Create a new report using SQL Server Data Tools

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.

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.

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

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.