How-to series: Create a custom report (part 2)

This is the second part of this “how-to” series regarding the creation of a custom report in Microsoft CRM 4.0. For those who want to read the first part, you can go here: How-to series: Create a custom report (part 1)

Well, in the first post, we looked at how to create a new report using the “Report Wizard” provided by Microsoft CRM and, honestly, this report looked awful!

In this part, I will show you the internal stuff behind the scene and how you can add your own TSQL query to return the data you really want to see.

First, you have to download your new & freshly deployed report:

1. In Microsoft CRM, browse to the “Reports” section, select your new report (in my case, the new report is named ‘How-to series – custom report creation’) and click on the “Edit Report” button
2. A new screen opens up to allow for some modification of the deployed report, such as:
   • Change the report name or description
   • Choose a parent report (when modifying a sub-report)
   • Select one or more categories where the report should be displayed
   • Select one or more entities related to the report
   • etc.

   

3. At this point, we want to use a reporting tool to modify our report so we can add whichever information we need: the tool I’ll be using is Microsoft SQL Server 2008 Report Builder 2.0 because I’m using SQL Server 2008 behind my CRM (see link below) so we have to download the report source (which is in RDL format) to work on: 
   • Click on “Actions” and select the “Download Report” option
   • Save the RDL file somewhere (of course)
4. Launch Report Builder and open your RDL file; you should get a screen similar to this one

   

5. Report Builder 2.0 is basically a simple Visual Studio, focused on reporting only:
   • On the left you have: variables available with the Reporting Services engine (such as the report execution time, the number of pages, etc.), the report parameters (more on that later), the embedded images, the data sources (CRM) and one or more datasets
   • On the right you have: the report properties (such as width, height, background color, etc.) or the field properties (tooltip, font color & size, the field value [when linked to a data source], etc.)
   • On top you have: the famous ribbon which allows you to insert new textboxes, new tables, new charts, etc. or to add a header and/or a footer to your report; you can also preview the output of your report with the “Run” button under the “File” tab:

   

   • On the bottom you have: the various groupings (column and/or row) used when dealing with a table or a matrix table
6. First thing to do to be able to preview your report: change the embedded data source and make it point to your development SQL Server (you should always work on a dev server if you can, with a set of fresh and current production data if possible)
   • Right-click on the data source called “CRM” and select “Data Source Properties” to open the properties screen
   • Leave the name and the connection type as it is, just change the connection string to suit your need
7. Now let’s deal with the table that has been created by the report wizard and let’s add a new column to display the “Created by” field
   • First, you need to change the query that has been created by the wizard to add the new column to the resultset: on the left pane, right-click on the “DSMain” dataset and select ‘Query…’ to enter the query editor

   

   • In the query editor, replace the code with the following TSQL code, if the query is correct the fields should be refreshed and available for further use:

   select ticketnumber,
           title,
           subjectidname,
           subjectid,
           createdon,
           createdbyname,
           incidentid
   from dbo.FilteredIncident as CRMAF_FilteredIncident

   • Still in the left pane, go to the “Parameters” and delete the ‘@CRM_FilteredIncident’ parameter since we are using the special ‘CRMAF_FilteredIncident’ alias in our TSQL query (see the SDK)
   • Now it’s time to add the new field to our table, by right-clicking on the table and selecting ‘Insert Column’ (either to the left or the right): 
   • Give the new column a title by using an expression (="Created By") and select the field to bind to on the next row, which is ‘createdbyname’:

   

   • Run the report and you should be happy with the result

   

8. Now it’s time to deploy your report on your test server
   • Go to the reports section and edit your report by clicking on the “Edit Report” button
   • In the report type field, select the “Existing File” option and browse to your modified RDL file
   • You must save the report for the system to upload the new RDL file
   • If everything is OK with your RDL file, you can now test your modified report!

Happy!

Useful links:

Report Builder 2.0: http://www.microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en

Microsoft CRM 4.0 SDK: http://www.microsoft.com/downloads/details.aspx?FamilyID=82e632a7-faf9-41e0-8ec1-a2662aae9dfb&displaylang=en