Report writing environment using SQL Server Data Tools

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

This topic describes what’s needed to author Microsoft Dynamics 365 reports. For Dynamics 365 (on-premises), this topic assumes you already have a functioning deployment of Microsoft Dynamics 365 Server. For information about Dynamics 365 (on-premises) requirements for reporting, see Microsoft Dynamics CRM reporting requirements.

You must have the required development tools and appropriate privileges in Microsoft Dynamics 365 to write and publish a report. Also, you should be familiar with:

• Microsoft Visual Studio.

• Creating Microsoft SQL Server Reporting Services reports using SQL Server Data Tools (SSDT).

• To write Fetch-based reports, the Microsoft Dynamics 365 FetchXML language. More information:  MSDN: Build queries with FetchXML

• To write SQL-based reports, the Transact-SQL language for Microsoft SQL Server.

Required tools

The following are required to write a custom report for Microsoft Dynamics 365:

• Microsoft SQL Server Reporting Services. Microsoft Dynamics 365 uses Microsoft SQL Server Reporting Services as the report engine.

• Microsoft Visual Studio. For specific versions, see Microsoft Dynamics 365 Reporting Authoring Extension requirements.

• SQL Server Data Tools. This is a report authoring environment that is used as a plug-in Microsoft Visual Studio.

  • For and Microsoft Visual Studio 2013: You must download and install Download: Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013

  • For Microsoft Visual Studio 2012: You must download and install Download: Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2012.

  • For Microsoft Visual Studio 2010: You must select and install the SQL Server Data Tools (SSDT) feature that is included with Microsoft SQL Server 2012 or Microsoft SQL Server 2012 Express on the computer that is running Microsoft Visual Studio 2010. Download: Microsoft SQL Server 2012 Express

• Microsoft Dynamics 365 Report Authoring Extension. This is required if you are writing custom Fetch-based reports. Notice that Microsoft Dynamics 365 (online) only supports Fetch-based reports. Microsoft Dynamics 365 Report Authoring Extension must be installed on the computer where Microsoft Visual Studio and SQL Server Data Tools are installed. Download: CRM 2016 Report Authoring Extension. For installation instructions, see Install Microsoft Dynamics CRM Report Authoring Extension.

Required privileges

To deploy custom reports to Microsoft Dynamics 365, you must have a Microsoft Dynamics 365 account and a security role assigned to you that includes the PublishReport privilege. By default, the System Customizer and System Administrator security roles include these privileges.

Report development process

The following lists the steps for developing custom Microsoft Dynamics 365 reports. You may have to repeat some steps while you develop a report:

1. Develop a report concept or specification based on what business information is to be displayed.

2. Decide on the type of report you want to create: Fetch-based or SQL-based. Microsoft Dynamics 365 (online) users can only create custom Fetch-based reports. More information: Report & Analytics with Dynamics 365

3. Create a custom report or use an existing report to modify using SQL Server Data Tools in Microsoft Visual Studio.

   • Create a new (custom) report. More information: Create a new report using SQL Server Data Tools

   • Download an existing Microsoft Dynamics 365 report definition language (.rdl) file. You can do this in the Microsoft Dynamics 365 web application. More information: Modify an RDL file

     Alternatively, for Dynamics 365 (on-premises), reports are located in the C:\Program Files\Microsoft Dynamics 365 Reporting Extensions\LangPacks\<lcid>\Reports\MSCRM\ folder where Microsoft Dynamics 365 Reporting Extensions is installed. More information: Modify an existing SQL-based report using SQL Server Data Tools

4. Create basic report parameters. More information: Use parameters in reports

5. Specify datasets and filtering criteria for retrieving data:

   • For SQL-based reports, create datasets that contain Microsoft Dynamics 365 data obtained from the filtered views. More information: Filtered views in Microsoft Dynamics 365

   • Enable pre-filtering on the primary entities. More information: Improve report performance by using filters

6. Define the basic layout of the report, including headers and footers.

7. Add report items as required based on the report specification. More information: Add report navigation

8. Preview the report in Microsoft Visual Studio, and resolve any errors. More information: Test and troubleshoot reports

9. Deploy the report to the reporting server by using Microsoft Dynamics 365. More information: Publish reports

10. Run the deployed report to verify.

See Also

Use SQL and filtered views to retrieve data for reports
Report & Analytics with Dynamics 365
Create a new report using SQL Server Data Tools
Getting Started with Custom Reports in the Cloud
Business Intelligence Development Studio
Report Designer and Business Intelligence Development Studio

© 2016 Microsoft. All rights reserved. Copyright