Working with Complex Queries
[Applies to: Microsoft Dynamics CRM 4.0]
Some complex 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 queries are limited to 260 table joins. In an 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, Visual Studio 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. The general procedure for editing a report's RDL file is described next.
Edit a Query in a Report's RDL File
The following describes how to edit a report definition in an RDL file by using Visual Studio.
The Report Definition Language XML schema can be found in the Visual Studio online documentation under the Reporting Services Books Online main topic and the Report Definition Language subtopic at http://msdn2.microsoft.com/en-us/library/ms155062.aspx. The specific documentation on XML schema can be found at http://msdn2.microsoft.com/en-us/library/ms154044.aspx.
Add to the Report in Report Designer
Before you edit the XML code, use Report Designer to add datasets, select table fields, define queries, and add parameters to a report. This will add XML code for these report elements in the RDL file.
- In the Query Builder's Data tab, create any required datasets.
- For each dataset, select required table fields and construct the query that fills the dataset with data.
View the XML Code
- Open Visual Studio. In Solution Explorer, right-click the report's RDL file and select View Code from the shortcut menu. An editing window opens displaying the XML code for the report.
- To turn on word wrap for improved readability, in the Tools menu, click Options. The Options dialog box appears.
- In the Options pane, select Text Editor, and then select Plain Text.
- Select the Word Wrap check box and then click OK.
To add report parameters to an SQL query when you edit the report's RDL file, you have to add <QueryParameter> XML tags for those additional query parameters.
The following example XML code is taken from the Account Summary report's RDL file. The code demonstrates <QueryParameter> tags to define parameters for use in a query.
<Query> <DataSourceName>CRM</DataSourceName> <CommandText> declare @sql as nvarchar(4000) set @sql = ' SELECT top 10 CAST(accountid as nvarchar(100)) as AccountID, name, '''' as None FROM (' + @CRM_FilteredAccount + ') as fa' exec(@sql) </CommandText> <QueryParameters> <QueryParameter Name="@CRM_FilteredAccount"> <Value>=Parameters!CRM_FilteredAccount.Value</Value> </QueryParameter> </QueryParameters> </Query>
Modify the Query
- Modify the query, adding any needed references to query parameters and additional string concatenations.
- With the report's RDL file selected in Solution Explorer, navigate to the File menu and select Save Selected Items to save your changes.
Test the Report
When you have finished editing the SQL query in the RDL file, switch back to the report Preview tab in Query Builder and test the report. Any XML schema errors or SQL errors will be reported in Visual Studio.
© 2010 Microsoft Corporation. All rights reserved.