Create custom reports using Business Intelligence Development Studio
Applies To: Dynamics CRM 2013
Business Intelligence Development Studio is the primary tool that is used to create or modify reports. Business Intelligence Development Studio is a report authoring environment in Microsoft Visual Studio 2008 that lets you create Microsoft SQL Server Reporting Services reports. The end result is an .rdl file containing the report definition that you can publish in Microsoft Dynamics CRM to view reports.
Alternatively, you can author reports by using a common text editor. To reduce the effort to create a custom report, use an existing report definition file (.rdl) that provides most of the desired functionality and modify it. For more information about the format of the XML elements in an .rdl file, see Report Definition Language Reference. The modified report XML can be verified by using the specified XML schema. Reporting Services will also verify the report definition and reject a report if the definition is invalid when you try to upload the report in Microsoft Dynamics CRM.
Note
If the .rdl file contains a FetchXML query, the FetchXML query in the RDL is validated by Microsoft Dynamics CRM Report Authoring Extension, which internally validates it against the FetchXML schema. For more information, see the ”Fetch XML Schema” topic in the .
In This Topic
Create a custom SQL-based report
Create a custom Fetch-based report
Differences between the RDL File of SQL-based and Fetch-based reports
Create a custom SQL-based report
To create a custom SQL-based report using Business Intelligence Development Studio:
Open Business Intelligence Development Studio, and create a report server project.
In Solution Explorer, right-click the Reports folder, and then click Add New Report.
Click Next.
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.
On the Design the Query page, type the SQL query to use for the report, and click Next.
On the Select the Report Type page, select a Tabular report or a Matrix report, and click Next.
Specify the fields that will be included in the report, and click Next.
Select a style to apply to the report, and then click Next.
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. More information: Publish reports.
For more information about how to create a report by using the Report Wizard in Business Intelligence Development Studio, see How to: Create a Report (Reporting Services).
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. To create a custom Fetch-based report:
Perform steps 1 through 3 as mentioned earlier in the Create a custom SQL-based report section.
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: Specify the connection string. The connection string must be specified in the following format:
ServerURL;OrganizationName;HomeRealmURL
In this connection string, only ServerURL is mandatory. If OrganizationName is not specified, the first organization that the user running this query belongs to is used. If HomeRealmURL is not specified, the value is retrieved from the registry value "HKLM\SOFTWARE\Microsoft\MSCRMBidsExtensions\HomeRealmUrl".
Click Credentials to specify the credentials to connect to Microsoft Dynamics CRM or Microsoft Dynamics CRM Online, and click Next.
On the Design the Query page, type the FetchXML query to retrieve data for the report. You can do one of the following:
Manually type the FetchXML query. More information: Build queries with FetchXML in the Microsoft Dynamics CRM SDK.
To get the FetchXML query for a record, open Microsoft Dynamics CRM, click Advanced Find, and then click Download Fetch XML.
Click Next.
The rest of the steps are the same as mentioned earlier in steps 6 through 9 in the Create a custom SQL-based report section.
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 Online using the Report Wizard. More information:Publish reports.
Differences between the RDL File of SQL-based and Fetch-based reports
The following table lists the differences between .rdl files of SQL-based and Fetch-based reports in Microsoft Dynamics CRM.
Area |
SQL-based Report |
Fetch-based report |
|---|---|---|
Data Provider |
The <DataProvider> element value in the .rdl file is set to SQL. For example: |
The <DataProvider> element value in the .rdl file is set to MSCRMFETCH. For example: |
Report query |
The query specified for retrieving data is in the <CommandText> sub-element under the <Query> element in the report definition (.rdl file) is a SQL query. For example, the query for retrieving all account names for a SQL-based report will be: |
The query specified for retrieving data is in the <CommandText> sub-element under the <Query> element in the report definition (.rdl file) is a FetchXML query. For example, the query for retrieving all account names for a Fetch-based report will be: |
See Also
Report Writers Guide for Microsoft Dynamics CRM 2013
Modify reports using Business Intelligence Development Studio
Blog: Getting Started With Custom Reports In The Cloud
© 2016 Microsoft Corporation. All rights reserved. Copyright