Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


CRM 4.0 Report Wizard Layout Confusion

Posted on 2009-05-13
Medium Priority
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?
Question by:vitsolutions
  • 3
  • 2
LVL 10

Expert Comment

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.

Author Comment

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

LVL 10

Expert Comment

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.

Author Comment

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
LVL 10

Accepted Solution

crm_info earned 2000 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.

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Desired Skill Set for Microsoft Dynamics CRM Technical Resources – Part II
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

564 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