Create a new report using SQL Server Data Tools

Article
11/28/2016

Applies To: Dynamics 365 (online), Dynamics 365 (on-premises), Dynamics CRM 2016, Dynamics CRM Online

SQL Server Data Tools is a report authoring environment that lets you create or edit Microsoft SQL Server Reporting Services reports in Microsoft Visual Studio. The end result is a report definition .rdl file that contains the report definition that you can publish in Microsoft Dynamics 365 to view reports.

You can also author reports by using a common text editor. To reduce the effort to create a custom report, modify an existing .rdl file that provides most of the desired functionality. 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 365.

Note

If the .rdl file contains a FetchXML query, the query in the RDL is validated by Microsoft Dynamics 365 Report Authoring Extension, which internally validates it against the FetchXML schema. For more information, see MSDN: Fetch XML Schema.

In This Topic

Create a custom Fetch-based report (Dynamics 365 (online) and Dynamics 365 on-premises)

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

Create a custom Fetch-based report (Dynamics 365 (online) and Dynamics 365 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 365 Report Authoring Extension, and the necessary privileges. More information: Report writing environment using SQL Server Data Tools
Open Microsoft Visual Studio, and then 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 then specify the following details:
- Name: Type a name for the data source.
- Type: Select Microsoft Dynamics 365 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 isn't 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.
  
  Click Credentials to specify the credentials to connect to Microsoft Dynamics 365 or Microsoft Dynamics 365 (online), and then click Next.
On the Design the Query page, type the FetchXML query in the Query box. To get this query, you can do one of the following:
- Get the FetchXML from an Advanced Find query. To do this, open Microsoft Dynamics 365, click Advanced Find, create the query that you want, and then on the Advanced Find tab, click Download Fetch XML. Copy the FetchXML into the Query box of the Dataset Properties in Microsoft Visual Studio.
- Manually enter the FetchXML query. The following example shows how to create a report that displays all accounts with 5,000 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
Click Next.
Verify the fields that will be included in the report, and then 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 enter a name for the report, such as Accounts With More Than 5,000 Employees. Click Finish.
If you’d like to see how the report will appear when it’s run, click the Preview tab.

This generates an .rdl file with the specified report name. You can use this file to publish your custom report in Microsoft Dynamics 365 (online) using the Report Wizard. More information: Publish reports

Create a custom SQL-based report (Dynamics 365 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,SQL Server Data Tools, and the necessary privileges. More information: Report writing environment using SQL Server Data Tools
Open Microsoft Visual Studio, and then 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 then 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 click Edit to type the SQL Server name and organizationName_MSCRM database. To supply credentials, select Credentials. Click Next.
On the Design the Query page, type the SQL query to use for the report, and then click Next. For example, to create a report that displays all accounts with 5,000 or more employees, where OrgName_MSCRM is the name of the organization database, use this query.
```
Use OrgName_MSCRM Select Name, AccountNumber, NumberofEmployees from AccountBase where NumberofEmployees > 5000 order by NumberofEmployees desc
```
Or 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 then click Next.
Verify the fields that will be included in the report, and thenclick Next.
Select a style to apply to the report, and then click Next.
Verify the fields that will be included in the report and enter a name for the report, such as Accounts With More Than 5,000 Employees. Click Finish.
If you’d like to see how the report will appear when it’s run, click the Preview tab.

This generates an .rdl file with the specified report name. You can use the .rdl file to publish your custom report in Microsoft Dynamics 365. 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 (Dynamics 365 (online) and Dynamics 365 on-premises)

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

See Also

Additional resources