Modify an existing SQL-based report using SQL Server Data Tools

  • Article

 

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

This topic provides information about modifying your existing Report Definition Language (RDL) file using SQL Server Data Tools. However, make sure that your modified RDL file conforms to the RDL schema and specifications. More information: MSDN: Report Definition XML Elements

In this topic

Work with complex SQL queries

Modify an RDL file

Add elements by using the Report Designer

Test the report

Work with complex SQL queries

When you create or modify a SQL-based report by using the Report Wizard in SQL Server Data Tools, you have to type some complex SQL queries into the Generic Query Designer because of SQL query limitations in Query Builder. Use Query Builder to generate an initial simple SQL query, and then switch to Generic Query Designer to add more complex query logic.

Note

New or existing SQL queries are limited to 260 table joins. The table join limitation includes your own table joins plus any table joins that are executed within the filtered views that are referred to.

When you add many string concatenations to an SQL query by using Query Designer or Query Builder, SQL Server Data Tools takes more time to refresh report items bound to the query's dataset. This results in reduced user productivity when you edit a report. For improved report writing productivity, you can bypass the report item refresh by manually editing the code for the SQL query in the Report Definition Language (RDL) file.

Modify an RDL file

  1. In Microsoft Dynamics 365, go to Sales > Reports and then select the report that you want. Click Edit on the command bar, and on the Actions menu, select Download Report.

  2. Open SQL Server Data Tools, and create a report server project.

  3. In Solution Explorer, right-click the Reports folder, select Add, and then click Existing Item. In the file dialog box, select the RDL file you downloaded in the previous step.

  4. To view the XML code of the RDL file, in the Solution Explorer pane, right-click the RDL file, and then click View Code. Make the required changes, and save the file.

Add elements by using the Report Designer

  1. Perform steps 1 through 3 as specified in Modify an RDL file.

  2. Right-click the RDL file, and then click View Designer. The report element is displayed on the Design tab.

  3. Use the Report Data pane to add datasets, select table fields, define queries, and add parameters to a report.

  4. Save the changes. This adds the required XML code for these report elements in the RDL file.

Test the report

After you finish editing the RDL file, save the changes, and switch back to the report Preview tab in SQL Server Data Tools to test the report. Any XML schema errors or SQL errors are reported in SQL Server Data Tools.

When the report is ready, Publish reports.

See Also

Report & Analytics with Dynamics 365
Use SQL and filtered views to retrieve data for reports
Create a new report using SQL Server Data Tools

© 2016 Microsoft. All rights reserved. Copyright