Create a new report using SQL Server Data Tools

Article
05/31/2016

Applies To: Dynamics CRM 2015

SQL Server Data Tools in Microsoft Visual Studio is the primary tool that is used to create or modify reports. SQL Server Data Tools is a report authoring environment that lets you create Microsoft SQL Server Reporting Services reports in Microsoft Visual Studio. 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 Microsoft Dynamics CRM SDK.

In This Topic

Create a custom Fetch-based report (CRM Online and CRM on-premises)

Create a custom SQL-based report (Microsoft Dynamics CRM on-premises only)

Create a custom Fetch-based report (CRM Online and CRM on-premises)

To create a custom Fetch-based report:

Make sure that you have a supported version of Microsoft Visual Studio, SQL Server Data Tools, Microsoft Dynamics CRM Report Authoring Extension and the necessary privileges. More information: Report writing environment using SQL Server Data Tools
Open Microsoft Visual Studio, and create a report server project.
In Solution Explorer, right-click the Reports folder, and then choose Add New Report.
Choose Next.
On the Select the Data Source page, choose 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. HomeRealmURL is the Home Realm URL of the Identity Provider used by your organization and is needed when your organization uses Federation for identity management. Contact your network administrator to determine the Home Realm URL.
  
  Choose Credentials to specify the credentials to connect to Microsoft Dynamics CRM or Microsoft Dynamics CRM Online, and choose Next.
On the Design the Query page, type the FetchXML query in the Query box. To get your FetchXML query, you can do one of the following:

Tip

The easiest method to create the FetchXML for a report is to create an Advanced Find query that provides the results that you want and then save the query as FetchXML as described here.
- Get the FetchXML from an Advanced Find query. To do this, open Microsoft Dynamics CRM, choose Advanced Find, create the query that you want, and then on the Advanced Find tab choose Download Fetch XML. Copy the FetchXML into the Query box of the Dataset Properties in Microsoft Visual Studio.
- Manually enter the FetchXML query, such as the following example, which creates a report that displays all accounts with 5000 or more employees.
```
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="name" />    
    <attribute name="numberofemployees" />
    <attribute name="accountnumber" />
    <order attribute="name" descending="false" />
    <filter type="and">
      <condition attribute="numberofemployees" operator="gt" value="5000" />
    </filter>
  </entity>
</fetch>
```
  More information: MSDN: Build queries with FetchXML in the Microsoft Dynamics CRM SDK.
Click Next.
Verify the fields that will be included in the report. Choose Next.
Select a style to apply to the report, and then choose Next.
Verify the fields that will be included in the report, and enter a name for the report, such as Accounts With More Than 5000 Employees. Choose Finish.

Tip

Choose the Preview tab to display how the report will appear when run.

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.

Create a custom SQL-based report (Microsoft Dynamics CRM on-premises only)

To create a custom SQL-based report using SQL Server Data Tools:

Make sure that you have a supported version of Microsoft Visual Studio and SQL Server Data Tools and the necessary privileges. More information: Report writing environment using SQL Server Data Tools
Open Microsoft Visual Studio, and create a report server project.
In Solution Explorer, right-click the Reports folder, and then choose Add New Report.
Choose Next.
On the Select the Data Source page, choose 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, and choose Edit to type the SQL Server name and organizationName_MSCRM database. To supply credentials, select Credentials. Choose Next.
On the Design the Query page, type the SQL query to use for the report, and choose Next. For example, to create a report that displays all accounts with 5000 or more employees, where OrgName_MSCRM is the name of the organization database, use this SQL query.
```
Use OrgName_MSCRM Select Name, AccountNumber, NumberofEmployees from AccountBase where NumberofEmployees > 5000 order by NumberofEmployees desc
```
Alternatively, you can design a query by choosing Query Builder.
On the Select the Report Type page, select a Tabular report or a Matrix report, and choose Next.
Verify the fields that will be included in the report. Choose Next.
Select a style to apply to the report, and then choose Next.
Verify the fields that will be included in the report, and enter a name for the report, such as Accounts With More Than 5000 Employees. Choose Finish.

Tip

Choose the Preview tab to display how the report will appear when run.

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 Designer, see Create a Basic Table Report (SSRS Tutorial).

Create a new report using SQL Server Data Tools

In This Topic

Create a custom Fetch-based report (CRM Online and CRM on-premises)

Create a custom SQL-based report (Microsoft Dynamics CRM on-premises only)

See Also

Additional resources