Microsoft Dynamics CRM 2011 for Developers | Creating Custom Reports Using Microsoft SQL Server 2008 Reporting Services

Contents

1. Introduction
2. Setup the Development Environment
3. Report Development Process
4. Create Custom Reports Using Business Intelligence Development Studio
   1. Create a Custom SQL-Based Report
   2. Create a Custom Fetch-Based Report
5. Import Custom Reports into Dynamics CRM

Introduction

Microsoft Dynamics CRM provides many out-of-box reports for viewing business data. You can view a list of reports you have access to by navigating to Workplace > My Work > Reports. You can also see a list of all reports available by navigating to Settings > Customization > Customizations > Customize the System > Report [left navigation]. In both lists, you can select a report and click Run Report from the ribbon to preview the report in running mode.

You can create custom reports using one of the out-of-box reports as templates, or create a custom report from scratch. There are two types of reports in Microsoft Dynamics CRM:

• SQL-Based Reports Use SQL queries to retrieve data from filtered views defined by the system. The default out-of-box reports are SQL-based reports. You can’t deploy SQL-based reports for Dynamics CRM online.
• Fetch-Based Reports Use FetchXML queries to retrieve data. They are introduced in Dynamics CRM 2011 and can be deployed both on on-premise and online. All reports created using the Report Wizard in the web interface, are fetch-based reports.

Setup the Development Environment

To write custom reports for Microsoft Dynamics CRM you need the following:

• Microsoft SQL Server 2008 Reporting Services (or 2008 R2) in native mode installation.
• Business Intelligence Development Studio. This is the report authoring environment in Visual Studio that hosts the Report Designer (available on the Microsoft SQL Server setup CD).
• Microsoft Dynamics CRM Report Authoring Extension. You will need this when creating a custom fetch-based reports. You can install it from http://www.microsoft.com/download/en/details.aspx?id=27823 or from the BIDSExtension folder in the Microsoft Dynamics CRM setup DVD.

Report Development Process

The following lists the steps for developing custom Microsoft Dynamics CRM reports. You may have to repeat some steps while you develop a report:

1. Develop a report concept or specification based on what business information is to be displayed.
2. Decide on the type of report you want to create: SQL-based or Fetch-based. Microsoft Dynamics CRM Online users can only create custom Fetch-based reports.
3. Download an existing Microsoft Dynamics CRM report definition (.rdl), and modify it or create a new report by using Business Intelligence Development Studio.
4. Create basic report parameters.
5. Specify datasets and filtering criteria for retrieving data:
   • For SQL-based reports, create datasets that contain Microsoft Dynamics CRM data obtained from the filtered views.
   • Enable pre-filtering on the primary entities.
6. Define the basic layout of the report, including headers and footers.
7. Add report items as required based on the report specification.
8. Preview the report in Microsoft Visual Studio, and resolve any errors.
9. Deploy the report to the reporting server by using Microsoft Dynamics CRM.
10. Run the deployed report to verify.

Create Custom Reports Using Business Intelligence Development Studio

Create a Custom SQL-Based Report

To create a custom SQL-based report using Business Intelligence Development Studio:

1- Open Business Intelligence Development Studio, and create a report server project.

2- In Solution Explorer, right-click the Reports folder, and then click Add New Report.

3- Click Next on the Report Wizard welcome screen.

4- On the Select the Data Source page, click New Data Source, and specify the following details:

• Name: Type a name for the data source.
• Type: Select Microsoft SQL Server.

• Connection String: Specify the connection string to connect to the instance of the Microsoft SQL Server database. To build the connection string, click Edit. To supply credentials, click Credentials. Click Next.

3- On the Design the Query page, type the SQL query to use in the report. You could also click Query Builder button to open the Query Builder window. Right click in the empty area at the top of the dialog and choose Add Table

then click on the Views tab, hold the CTRL key and and select the views that you will use in your report and click Add

select the columns you want, test your query results [We just retrieved the account name, city, country]

when you satisfied with your query results, click Ok to use the query in the report

click Next.

4- On the Select the Report Type page, select a Tabular report or a Matrix report, and click Next. [we used Tabular, which is the most common report format]

5- Specify the fields that will be included in the report. You can add fields in three different sections [Page, Group, Details]. Page can be used for granular grouping like Country in our case, it will displayed on the page header only. Group can be used to more specific grouping within the page, like City in our case. Details is the place where individual records will be rendered, like our accounts names. Select each column name from the list on the left and click the designated section button to add the column to it.

Then click Next.

6- Select a stepped layout and click Next.

7- Select a style to apply to the report, and then click Next.

8- Verify the fields that will be included in the report, and name the report. Click Finish.

This will generate an .rdl file with the specified report name. You can use the .rdl file to publish your custom report in Microsoft Dynamics CRM.

Create a Custom Fetch-Based Report

Creating a custom Fetch-based report is similar to creating a custom SQL-based report except for the data source name and the report query specified while creating the report definition.

Since you have the capability to create SQL-based reports for on-premise, most people will not use Fetch-based reports for on-premise. Let’s learn how to get the data source name for our CRM On-Line organization. It consists of:

• Organization URL something like https://xrmdemo5.crm.dynamics.com
• Organization unique name you can get it by navigating to Settings > Customization > Customizations > Developer Resources > it will be on this page

Put the two parts separated by semi-colon will form your connection string, like https://xrmdemo5.crm.dynamics.com;4c69b8c565264033b4707c804cba7aa2 put that connection string in a text file for later use.

Now, lets learn how to create a query for our Fetch-based report. There are two ways to do that:

1. Manually type the FetchXML query (which I don’t personally prefer)
2. Obtain the FetchXML queries from the CRM web client and then modify it.

To obtain FetchXML queries you can navigate to any entity listing in CRM web client, like Accounts. Then click Advanced Find on the ribbon

This will open the Advanced Find dialog

Then click New to start creating a new query.

1- You can select the Entity (Table) from the Look For combo-box.

2- You can create your query where conditions. Click Select link to select the desired column

You can then click Equals link to specify the operator used in the where condition. You can then click Enter Text link to specify the value used in the condition

when you have multiple conditions, you can select them and use the Group AND and Group OR on the ribbon to build your query logic.

3- You can specify the columns you want retrieve. Click Edit Columns in the ribbon, this will open the Edit Columns dialog

The area on the left shows the query columns and the sequence they appear. You can click Add Columns from the left to add more columns from this entity or any of entities related to it.

Select Address 1: City and Address 1: Country and click Ok.

4- You can specify the query sorting. Click Configure Sorting on the left to open the Configure Sort Order dialog

You can specify two columns only for the sorting criteria.

5- You can remove any column by click its name on the right and click Remove from the left.

When you done with editing the query columns click OK to return to the Advanced Find window. Now, click Download Fetch XML on the ribbon to download the Fetch query in a .xml file format to use inside SQL Business Intelligence Development Studio.

Now to add a custom Fetch-based report to our Report project, Right click Reports folder and click Add New Report. Click Next on the Report Wizard welcome screen.

1- On the Select the Data Source page, click New Data Source, and specify the following details:

• Name: Type a name for the data source.
• Type: Select Microsoft Dynamics CRM Fetch.
• Connection String: copy the connection string we prepared earlier and past it here.
• Click on Credentials and enter your live account and password you used in CRM Online and click Ok. Then click Next

2- On the Design the Query page, click on Query Builder. On the Query Designer dialog, click Import and browse to the xml file you downloaded earlier.

 

You can click on Run to test the query. Click Ok to close the Query Designer dialog, and Click Next.

Complete the Wizard steps just like the SQL-based report, it is the same.

As you can create queries using Business Intelligence Development Studio, you can also use it to modify complex queries.

Import Custom Reports into Dynamics CRM

To complete the cycle and let the report used by your organization, you need to import it into CRM. Follow the following steps to import our report:

1- Navigate to My Work > Reports > click New on the ribbon. The New Report window will open.

Click Browse and select your .rdl report file. Enter the report name. Near the bottom of the window there is a Display In field which decides areas where your reports will be displayed. By default new reports will be displayed in Reports area,  You can click the eclipse button if you want to make it displayed in forms or lists of the related entities, then select desired values and move them to Selected Values list.

In the New Report window, if you want to make the report accessible by the whole organization click the Administration tab and change the Viewable By field from Individual to Organization.

Click Save and Close. Now our report will be available in the Reports area of configured accessibility level [Organization or Individual].

To copy a report between organizations or deployments, include the report and any custom entities the report uses in a solution. This ensures that the entity types are mapped automatically by the system

In this post we used the Business Intelligence Development Studio to create both SQL-based and Fetch-based custom reports for on-premise and on-line CRM deployments. We also learned how to upload our reports to Dynamics CRM and make them accessible to the whole organization and through many access points.