Solved

CRM 4.0 Report Wizard Layout Confusion

Posted on 2009-05-13
5
2,450 Views
Last Modified: 2012-05-06
Hi All,
I am trying to use the CRM 4 report wizard to create a customised Quotation for customers.
I am falling on the first hurdle and for the life of me i cant seem to fix it.

I follow the wizard and get to add and sort columns.
I simply want to add a column named To: which displays the Potential customer business name, Address and postal code. On the same line but to right a new colum which shows Our company name, address and postal code. I would of thought this was pretty easy but as you will see from my screen shot i can not get it right.

What am i doing wrong?
Thanks
new-report.JPG
0
Comment
Question by:vitsolutions
  • 3
  • 2
5 Comments
 
LVL 10

Expert Comment

by:crm_info
ID: 24381067
You're not doing anything wrong - you've just run into the limitations of the report designer.  The designer really can't lay out reports in the manner that you would like.  The report wizard really just lays out columnar reports that you can group, subtotal and chart.  But you do have some options:

(1) You can create a mail-merge in Word that can handle what you're trying to accomplish:http://blogs.msdn.com/crm/archive/2008/01/15/mscrm-4-0-mail-merge-basics.aspx  (scroll to the comments on the bottom of the blog entry and search for the term "Quote")

(2) Update the standard Quote report to handle your needs.  For this, you'll need to edit the report, do More Actions, Download the Report - this will create a .rdl file on your machine.  You typically want to do this on the machine where SQL Reporting Services is located (often the same as you CRM Server if you are a small business).  You'll then need to open the .rdl file in SQL Server Business Intelligence Design Studio (basically, a version of Visual Studio with custom report design features).  From there, you'll edit the report in a visual interface, save it and then you can create a new report in CRM from an Existing file, browse to the .RDL file and upload it.

If you've ever used reporting services before (or if you're trying to gain competency with the essential tools of MS CRM) then I recommend approach #2.  If this is a single implementation for you and you'd prefer not to have to look at SQL queries, then I recommend option #1.

Hope that provides some useful info.
0
 

Author Comment

by:vitsolutions
ID: 24382933
Hi Crm_info,
thanks for your reply and links to help. I have read through the msdn blog regarding mail merge and followed the link to download the template quotations and had a little play around however i really think the reports are a better option for us because although it seems microsoft allow users to create a quotation for a customer, you can not do the same with invoices.

At present i am more than happy with the Microsoft default report layouts for Quotes, Orders and Invoices. All i want to achieve is to
- add a simple logo at the top of each page generated
- Remove the large "QUOTE - Customer Name" / "ORDER - Customer Name" etc from the top
- Remove the time and date stamp and Prepared by completely from the bottom
- Rename the word "Tax" with "VAT"
- Change the "From" information to Our company name and not the users name.

I have attempted to highlight these items in the attached screenshots.
I have followed your instructions and opened the rdl file in Visual Studio on the crm server and i notice i can amend the code. However my knowledge of what im guessing is SQL code is zero!
I notice things are laid out in a fairly straight forward way with tags etc however i would be more than grateful if i could get some assistance on what code changes i should make to achieve the above desired effect?

I have attached the code snippet of the default "Quote" template also.
<?xml version="1.0"?><Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"><DataSources><DataSource Name="CRM"><ConnectionProperties><IntegratedSecurity>true</IntegratedSecurity><ConnectString>data source=localhost;initial catalog=Adventure_Works_Cycle_MSCRM</ConnectString><DataProvider>SQL</DataProvider></ConnectionProperties><rd:DataSourceID>bd1301de-68ad-4451-8159-73edf1a55fea</rd:DataSourceID></DataSource></DataSources><BottomMargin>0.5in</BottomMargin><RightMargin>0.5in</RightMargin><ReportParameters><ReportParameter Name="CRM_FilterText"><DataType>String</DataType><Nullable>true</Nullable><AllowBlank>true</AllowBlank><Prompt /></ReportParameter><ReportParameter Name="CRM_URL"><DataType>String</DataType><Nullable>true</Nullable><AllowBlank>true</AllowBlank><Prompt /></ReportParameter><ReportParameter Name="CRM_FullName"><DataType>String</DataType><DefaultValue><DataSetReference><DataSetName>UserInfo</DataSetName><ValueField>fullname</ValueField></DataSetReference></DefaultValue><AllowBlank>true</AllowBlank><Prompt /></ReportParameter><ReportParameter Name="cbDisplayChoices"><DataType>Integer</DataType><DefaultValue><Values><Value>=split("1,3,4",",")</Value></Values></DefaultValue><AllowBlank>true</AllowBlank><Prompt>Select information that needs to be displayed in the Item Details area:</Prompt><ValidValues><ParameterValues><ParameterValue><Value>1</Value><Label>Product ID</Label></ParameterValue><ParameterValue><Value>2</Value><Label>Unit of Measure</Label></ParameterValue><ParameterValue><Value>3</Value><Label>Quantity</Label></ParameterValue><ParameterValue><Value>4</Value><Label>Price per unit</Label></ParameterValue><ParameterValue><Value>5</Value><Label>TAX</Label></ParameterValue><ParameterValue><Value>6</Value><Label>Discount</Label></ParameterValue></ParameterValues></ValidValues><MultiValue>true</MultiValue></ReportParameter><ReportParameter Name="CRM_FormatDate"><DataType>String</DataType><Nullable>true</Nullable><DefaultValue><DataSetReference><DataSetName>DSNumAndCurrency</DataSetName><ValueField>DateFormat</ValueField></DataSetReference></DefaultValue><AllowBlank>true</AllowBlank><Prompt></Prompt></ReportParameter><ReportParameter Name="CRM_FormatTime"><DataType>String</DataType><Nullable>true</Nullable><DefaultValue><DataSetReference><DataSetName>DSNumAndCurrency</DataSetName><ValueField>TimeFormat</ValueField></DataSetReference></DefaultValue><AllowBlank>true</AllowBlank><Prompt></Prompt></ReportParameter><ReportParameter Name="CRM_NumberLanguageCode"><DataType>String</DataType><DefaultValue><DataSetReference><DataSetName>DSNumAndCurrency</DataSetName><ValueField>NumberLanguageCode</ValueField></DataSetReference></DefaultValue><AllowBlank>true</AllowBlank><Prompt></Prompt></ReportParameter><ReportParameter Name="CRM_CalendarType"><DataType>String</DataType><DefaultValue><DataSetReference><DataSetName>DSNumAndCurrency</DataSetName><ValueField>CalendarType</ValueField></DataSetReference></DefaultValue><Prompt></Prompt></ReportParameter></ReportParameters><rd:DrawGrid>true</rd:DrawGrid><InteractiveWidth>8.5in</InteractiveWidth><rd:GridSpacing>0.03125in</rd:GridSpacing><rd:SnapToGrid>true</rd:SnapToGrid><Body><ReportItems><List Name="list1"><ReportItems><Subreport Name="QuoteSubReport"><Parameters><Parameter Name="cbDisplayChoices"><Value>=Split(Join(Parameters!cbDisplayChoices.Value,","),",")</Value></Parameter><Parameter Name="CRM_QuoteId"><Value>=Fields!quoteid.Value.ToString()</Value></Parameter><Parameter Name="CRM_URL"><Value>=Parameters!CRM_URL.Value</Value></Parameter></Parameters><ReportName>Quote Sub-Report</ReportName></Subreport></ReportItems><DataSetName>DataSet1</DataSetName><Grouping Name="list1_Details_Group"><PageBreakAtEnd>true</PageBreakAtEnd><GroupExpressions><GroupExpression>=Fields!quoteid.Value.ToString()</GroupExpression></GroupExpressions></Grouping></List></ReportItems><Height>2.6875in</Height></Body><rd:ReportID>175a60d8-7f95-49ba-b4f6-a498c1c36244</rd:ReportID><LeftMargin>0.5in</LeftMargin><DataSets><DataSet Name="DataSet1"><Query><CommandText>SELECT     TOP (20) quoteid

FROM         FilteredQuote AS CRMAF_FilteredQuote</CommandText><DataSourceName>CRM</DataSourceName></Query><Fields><Field Name="quoteid"><rd:TypeName>System.Guid</rd:TypeName><DataField>quoteid</DataField></Field></Fields></DataSet><DataSet Name="DSNumAndCurrency"><Query><CommandText>

          SELECT     DateFormat, TimeFormat, NumberLanguageCode, CalendarType, NumberFormat_0_Precision, NumberFormat_1_Precision, NumberFormat_2_Precision,

          NumberFormat_3_Precision, NumberFormat_4_Precision, NumberFormat_5_Precision, CurrencyFormat_0_Precision, CurrencyFormat_1_Precision,

          CurrencyFormat_2_Precision, CurrencyFormat_3_Precision, CurrencyFormat_4_Precision, CurrencyFormat_5_Precision

          FROM         dbo.fn_GetFormatStrings() AS fn_GetFormatStrings_1</CommandText><DataSourceName>CRM</DataSourceName></Query><Fields><Field Name="DateFormat"><rd:TypeName>System.String</rd:TypeName><DataField>DateFormat</DataField></Field><Field Name="TimeFormat"><rd:TypeName>System.String</rd:TypeName><DataField>TimeFormat</DataField></Field><Field Name="NumberLanguageCode"><rd:TypeName>System.String</rd:TypeName><DataField>NumberLanguageCode</DataField></Field><Field Name="CalendarType"><rd:TypeName>System.String</rd:TypeName><DataField>CalendarType</DataField></Field><Field Name="NumberFormat_0_Precision"><rd:TypeName>System.String</rd:TypeName><DataField>NumberFormat_0_Precision</DataField></Field><Field Name="NumberFormat_1_Precision"><rd:TypeName>System.String</rd:TypeName><DataField>NumberFormat_1_Precision</DataField></Field><Field Name="NumberFormat_2_Precision"><rd:TypeName>System.String</rd:TypeName><DataField>NumberFormat_2_Precision</DataField></Field><Field Name="NumberFormat_3_Precision"><rd:TypeName>System.String</rd:TypeName><DataField>NumberFormat_3_Precision</DataField></Field><Field Name="NumberFormat_4_Precision"><rd:TypeName>System.String</rd:TypeName><DataField>NumberFormat_4_Precision</DataField></Field><Field Name="NumberFormat_5_Precision"><rd:TypeName>System.String</rd:TypeName><DataField>NumberFormat_5_Precision</DataField></Field><Field Name="CurrencyFormat_0_Precision"><rd:TypeName>System.String</rd:TypeName><DataField>CurrencyFormat_0_Precision</DataField></Field><Field Name="CurrencyFormat_1_Precision"><rd:TypeName>System.String</rd:TypeName><DataField>CurrencyFormat_1_Precision</DataField></Field><Field Name="CurrencyFormat_2_Precision"><rd:TypeName>System.String</rd:TypeName><DataField>CurrencyFormat_2_Precision</DataField></Field><Field Name="CurrencyFormat_3_Precision"><rd:TypeName>System.String</rd:TypeName><DataField>CurrencyFormat_3_Precision</DataField></Field><Field Name="CurrencyFormat_4_Precision"><rd:TypeName>System.String</rd:TypeName><DataField>CurrencyFormat_4_Precision</DataField></Field><Field Name="CurrencyFormat_5_Precision"><rd:TypeName>System.String</rd:TypeName><DataField>CurrencyFormat_5_Precision</DataField></Field></Fields></DataSet><DataSet Name="UserInfo"><Query><CommandText>SELECT     fullname

FROM         FilteredSystemUser

WHERE     (systemuserid = dbo.fn_FindUserGuid())</CommandText><DataSourceName>CRM</DataSourceName></Query><Fields><Field Name="fullname"><rd:TypeName>System.String</rd:TypeName><DataField>fullname</DataField></Field></Fields></DataSet></DataSets><Author> MSFT</Author><Code /><Width>7.46875in</Width><CustomProperties><CustomProperty><Name>Custom</Name><Value>&lt;MSCRM xmlns="mscrm"&gt;&amp;lt;ReportFilter&amp;gt;&amp;lt;ReportEntity paramname="P1"&amp;gt;&amp;lt;fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"&amp;gt;&amp;lt;entity name="quote"&amp;gt;&amp;lt;all-attributes /&amp;gt;&amp;lt;filter type="and"&amp;gt;&amp;lt;condition attribute="modifiedon" operator="last-x-days" value="30" /&amp;gt;&amp;lt;/filter&amp;gt;&amp;lt;/entity&amp;gt;&amp;lt;/fetch&amp;gt;&amp;lt;/ReportEntity&amp;gt;&amp;lt;/ReportFilter&amp;gt;&lt;/MSCRM&gt;</Value></CustomProperty></CustomProperties><InteractiveHeight>11in</InteractiveHeight><Language>=Parameters!CRM_NumberLanguageCode.Value</Language><TopMargin>0.5in</TopMargin></Report>

Open in new window

crm1.JPG
crm25.JPG
0
 
LVL 10

Expert Comment

by:crm_info
ID: 24383591
Hi vitsolutions,

Actually, the better way to open the report is using SQL Server Business Intelligence Development Studio (this way, you'll be able to visually design your report without editing a lot of XML).  Also, based on your changes, above, you won't need to change any of the SQL code, just some of the design elements of the report.

Here's how to open it in SQL BIDS:

(1) On the server that has report services installed, make sure you have exported the .RDL file.

(2) Start | All Programs | SQL Server 2005 | SQL Server Business Intelligence Development Studio (this is an extension of Visual Studio)

(3) File | New | Project

(4) Name your report project and let Visual Studio create a directory for you

(5) Copy your .RDL file into the lowest-level subdirectory for the project

(6) Back in SQL BIDS, open the Solution Explorer and right-click on Reports, select Add | Existing Item

(7) Select the RDL file that you copied in #5, above

(8) Double-click the report

(9) Click the Data tab

(10) Click the ellipses (...) next to the dataset

(11) Again, click the ellipses (...) next to the dataset

(12) Click the Edit button

(13) You will need to enter and/or browse to the correct server and database (the defaults are for the sample database provided by MS).

(14) Click OK, OK, OK to exit the dialog boxes

(15) Click Layout

(16) Edit your report

(17) Save your report

(18) In CRM, select New Report (I advise against overwriting your old report until you are satisfied that your new report is working), select Existing File and browse to your revised RDL


NOTE: The Quote, Order and Invoice reports include a base report and a sub-report.  To make your changes, you will need to edit BOTH reports in each case.  When you load them back into CRM, you will need to correctly identify your parent report.
0
 

Author Comment

by:vitsolutions
ID: 24386243
Thanks again for your reply crm_info,
I finally got the hang of amending the reports. It was the sub report of the quotation that was vital. I have managed to add a logo and amend the other items i listed however for seem reason when printing the report it produces 8 pages, 7 of which are blank :(

Any ideas?
Thanks again
0
 
LVL 10

Accepted Solution

by:
crm_info earned 500 total points
ID: 24397458
A couple of possibilities come to mind:

(1) You may have changed the width of the report.  If you made it wider, then for each page you're printing, you may be printing a second blank page with information in the area that is outside of the standard margins.  If no data is in that area, then the page will be blank.

(2) If you headers/footers (including group headers / footers) you can set the report to start a new page for each new one.  You may have to turn that off.

(3) Take the extra 7 sheets of paper and stick them back into the paper bin on your printer.   :)

(4) Your logo or other information may be pushing the content down the page, creating additional pages.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This is a walkthrough guide I wrote whilst upgrading my on-premise MS Dynamics CRM 3.0 deployment to 4.0. This covers the actual installation of the product to a working level for my system, I ran into a lot of issues that the steps below fixed so h…
On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now