Create a Custom Quote, Order or Invoice Report for your Customers

Many organizations appreciate the ability to generate ‘branded’ Quotes, Orders, Invoices and other Customer related documents from CRM. This post will guide you through the steps required to create a report that will run on one CRM record and show you a few simple formatting tricks like how to add a logo, using page headers and footers, use sub-reports, etc. You will then be able to customize the report and generate a professional and branded document for use in your Organization or delivery to your customer base. The example in this post uses the Quote entity but any entity can be used.

We have 3 primary objectives for the report: create a document that displays data from two tables (FilteredQuote & FiltereQuoteDetail), ensure the report runs against only one Quote record at a time, and meet the organization’s design requirements. In this case, the only design requirement we have is to add a logo to the report. Let’s get started.

First, this post assumes that you already know how to create and upload a basic custom report, you are using CRM On-Premise or an installation where you have the ability to upload custom reports, and you have access to the tools and resources needed to generate the report format that you want.

Open Visual Studio, this example uses VS 2005 but the same principles apply to VS 2008. Create your first report document; we will end up with two reports so let’s name the first one Customer Quote. Use the following query in the Customer Quote report:

• select quoteid from filteredquote crmaf_filteredquote

We’ll keep it simple for now. You can build on it later once you confirm everything is working as expected. Save this report and let’s create the second report that will serve as a sub-report to the Customer Quote report. I’ll name the sub-report Customer Quote Subreport. For now, let’s use the following query in the sub-report. You can add additional fields later.

• select quoteid, productidname, quantity, extendedamount from filteredquotedetail

Now we’ll add a Parameter to the sub-report;

• Select Report from the top menu
• Select Report Parameters

• A new dialogue window will open
• Select the Add button from the bottom of the dialogue window to create the new parameter
• Ensure your parameter matches the following

• Select Ok and save your changes

Let’s add a simple table to the sub-report

• Go to the Layout View
• Select Table from the Toolbox and drop it onto your report layout

I added three fields to the table, gave them friendly names in the header columns, added a formula in the footer column that will display the Total Quote Amount and added some borders to the table to improve the display. The important part for testing is to add the three fields to the table. Everything else is cosmetic and can be adjusted to fit the Customer requirement. Here is a view of my sub-report table

You’ll notice a textbox in the above image. I have the visibility properties of the textbox set to not display and I copied the guid of a quote from CRM into the textbox. I plan to use the value later for testing.

Let’s go back to the Data view of our sub-report and add the parameter to the quoteid filter column in this format @QuoteId. If you are not sure how to take that action, you can just add it to the end of your query. You query should look like this:

• SELECT quoteid, productidname, quantity, extendedamount from FilteredQuoteDetail WHERE (quoteid = @QuoteID)

Now let’s test the query by selecting the Run button and supplying a valid Quoteid guid to the parameter. This is where the hidden guid in the textbox comes in handy. You can copy the quoteid guid into the Value field for the parameter and your return result should be a list of products from the quote associated with the guid.

Now that we know the sub-report is working as expected, let’s go back to our Customer Quote report and add a sub-report control to the report by selecting the Subreport control from the toolbox and placing it in the report layout.

After you’ve added the sub-report control, right-click it and select properties. Let’s add the following values:

• General Tab
  • Change the name or leave the default value, your choice
  • Select the Customer Quote Sub-report from the Subreport drop down list

• Parameters Tab
  • Select the QuoteID from the Parameter Name list
  • Select the quoteid field from the Parameter Value list and change the word Count to First
    • Since we are always going to run this report on one quote we will only have one quoteid as a value so this change should work for this report

• Select Ok and save your changes.

That completes the steps to add the sub-report.  Next, let’s create a Page Header. We’ll use the header area to display a logo and some other information.

• Select Report from the top menu and select Page Header

Now, let’s add a logo image to the page header. For this example I’ll embed the image in the report.

• Select the Image control from the report toolbox and place it on your page header area

• A new dialogue window will open
• Leave the default selection “Embedded” and select Next

• Select the New Image button and browse to the Logo file on your computer
• Select the logo file and select Open

• The logo will now appear in your Report header

I also added a few additional textbox controls, Page Footer, and a textbox in the footer area that will display the report execution date and time. I added these as examples. You can add additional fields to your query like the Account that is associated with the quote, the Sales Representative associated with the Quote and any other data you would like to display in the report. You can then display that data by adding an additional report control like a table, textbox, etc. and add the fields that you want to display to the controls.

• Save your work and we are ready to upload the reports to CRM.
• Open your CRM application in a Web browser
• Go to Workplace Reports
• Select the New button
  • A new dialogue window will open
• Select Existing File as the Report Type
• Browse to your Customer Quote Report file
• You can leave the default Name or change it to whatever you prefer
• Select Quote from the Related Record Type
• Select Forms for Related Records for the Display In
  • Do not select Reports Area as this report is designed to run on one record
• Select Save and Close
  • After testing, and when you are ready to make the report available to the Organization, you’ll need to edit the report, select the Administration tab and change the ‘Viewable By’ from Individual to Organization

Now let’s upload the sub-report

• Follow the steps above with a couple of changes
• Select the Customer Quote Subreport
  • When you select the sub-report , the Parent Report field should automatically fill in the with the Customer Quote name. If it doesn’t, then this is a sign that something is wrong.
• Ensure the following fields have no value as the Parent Report will run this report automatically
  • Categories
  • Related Record types
  • Display In
• Select Save and Close

To test the report, open a Quote in CRM and select the Report icon from the top menu. You should see the Customer Quote report under the ‘Run on Existing Record’ value. Select the Customer Quote and the report should automatically run.

Now for the fun part; go back to Visual Studio and add the design elements, additional fields and any other formatting changes you want to give the report a professional, branded look. You can customize both the Main and Sub-report depending on your needs. To upload and test design changes, just highlight the report from your report list in CRM and select the Edit button. Browse to the report “.rdl” file then select the Save and Close button. Be sure to select the ‘.rdl’ file and not the ‘.rdl.data’ file. Open a Quote and run the report to review your changes.

This is one example of a method for creating a customized Quote, Order or Invoice document that you can use to deliver professional documents to your client base. There are several other methods available for use as well. Below are links to some of the available resources that you can use to find other solutions.

• MS CRM Team Blog
• CRM MSDN Community Resources
• Report Writer Guide
• CRM Resource Center