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

  • Article

 

Applies To: Dynamics CRM 2015

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 a report (RDL) file

Add elements by using the Report Designer

Test the report

Work with complex SQL queries

While creating or modifying a SQL-based report by using the Report Wizard in SQL Server Data Tools, some complex SQL queries must be typed 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. In a SQL query, 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 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 a report (RDL) file

  1. Download a report that you want to change. To do this, start the Microsoft Dynamics CRM web application, go to Sales > Reports and then select the report that you want. Choose 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 choose 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 choose 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 a report (RDL) file.

  2. Right-click the RDL file, and then choose 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 will add 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 will be reported in SQL Server Data Tools.

When the report is ready, Publish reports.

See Also

Report writing with CRM 2015 for online and on-premises
Use SQL and filtered views to retrieve data for reports
Create a new report using SQL Server Data Tools

© 2016 Microsoft Corporation. All rights reserved. Copyright