Export (0) Print
Expand All

Create Custom Reports Using Business Intelligence Development Studio

[Applies to: Microsoft Dynamics CRM 2011]

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.

noteNote
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, seeFetchXML Schema.

In This Topic

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.

  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.

  5. On the Design the Query page, type the SQL query to use for the report, and click Next.

  6. On the Select the Report Type page, select a Tabular report or a Matrix report, and click Next.

  7. Specify the fields that will be included in the report, and click Next.

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

  9. 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. For more information, see Publish Reports.

For more information about 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:

  1. Perform steps 1 through 3 as mentioned earlier in the Create a Custom SQL-based Report section.

  2. 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, and click Next.

  3. 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. For more information, see Build Queries with FetchXML.

    • Obtain the FetchXML queries for records by opening Microsoft Dynamics CRM, clicking Advanced Find, and then clicking Download Fetch XML.

    Click Next.

  4. 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. For more information, seePublish a Report Using the Microsoft Dynamics CRM Web Application.

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:

<DataProvider>SQL</DataProvider>

The <DataProvider> element value in the .rdl file is set to MSCRMFETCH. For example:

<DataProvider>MSCRMFETCH</DataProvider>

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:

<CommandText>SELECT name FROM FilteredAccount;</CommandText>

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:

<CommandText>&lt;fetch version="1.0" output-format="xml-platform" mapping="logical"&gt; 
    &lt;entity name="account"&gt;
        &lt;attribute name="name" /&gt;
    &lt;/entity&gt;
&lt;/fetch&gt;</CommandText>

See Also

Microsoft Dynamics CRM 2011
Send comments about this topic to Microsoft.
© 2013 Microsoft Corporation. All rights reserved.
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2014 Microsoft