Reports that return large data sets can be difficult to use and can cause performance problems. To limit the data that is presented in a report, use data filters.

In addition to data filtering supported by Reporting Services, Microsoft Dynamics CRM supports data pre–filtering. You can use data pre-filtering to:

• Make reports context-sensitive by narrowing the scope of a report to return more relevant data.
• Retrieve and display a result set faster because only more relevant data is returned.
• Allow the report to be filtered using the Advanced Find feature.

Enabling Data Pre-Filtering on Reports

There are two ways that you can enable data pre-filtering on Microsoft Dynamics CRM reports: automatic and explicit. The following sections explain these options.

Automatic Pre-Filtering

To enable automatic data pre-filtering on a report, you can alias entity tables in queries by using an alias name that starts with "CRMAF_".

Automatic data pre-filtering is suited for simple queries. For example, the following table shows a simple query modified to enable pre-filtering on the Account entity.

When you enable automatic data pre-filtering functionality using the "CRMAF_" prefix, Microsoft Dynamics CRM modifies the query to include a parameter (for example, P1) when it is uploaded to Microsoft Dynamics CRM, as shown in the following table.

Microsoft Dynamics CRM will pass a query to the P1 parameter depending on how the report is being filtered. In other words, automatic data pre-filtering acts as a sub-query within the existing query.

The following examples illustrate how Microsoft Dynamics CRM passes queries to the parameter (P1) as per different filtering requirements. In these examples, it is assumed that you are running the report from the Reports area in Microsoft Dynamics CRM, and are using the data filtering option.

Example 1: If you want to view only active accounts, the resulting query would be as follows:

SELECT <column1>, <column2>, <columnN>
FROM (SELECT FilteredAccount.* FROM FilteredAccount WHERE statecode = 0)
AS CRMAF_FilteredAccount

Example 2: If you are within a specific account and run the report, the resulting query would be as follows:

SELECT <column1>, <column2>, <columnN>
FROM (SELECT FilteredAccount.* FROM FilteredAccount WHERE AccountId = '<CurrentAccountId>')
AS CRMAF_FilteredAccount

Example 3: If you are looking at a list of three selected accounts and you choose the option to run the report against the selected records, the resulting query would be as follows:

SELECT <column1>, <column2>, <columnN>
FROM (SELECT FilteredAccount.* FROM FilteredAccount WHERE AccountId in ('<1stAccountId>', '<2ndAccountId>', '<3rdAccountId>') 
AS CRMAF_FilteredAccount

When any entity table names are aliased, the Advanced Find user interface is automatically included in the deployed report when it is run from Microsoft Dynamics CRM.

To alias an entity table name in Query Builder, right-click each table in your report, click Properties in the shortcut menu, and enter the alias value in the form CRMAF_FilteredEntity, for example, CRMAF_FilteredAccount.

Limitation of Automatic Pre-filtering

When you use the "CRMAF_" prefix to enable automatic pre-filtering, Microsoft Dynamics CRM has to add a parameter in the query. With a more complex query such as a query using UNION statements, this can lead to unexpected results because Microsoft Dynamics CRM might only add the parameter to the first query.

For example, consider the following query containing UNION statements:

SELECT <column1>, <column2>, <columnN>
FROM FilteredAccount AS CRMAF_FilteredAccount
WHERE address1_stateorprovince = 'FL'
UNION
SELECT <column1>, <column2>, <columnN>
FROM FilteredAccount AS CRMAF_FilteredAccount
WHERE address1_stateorprovince = 'CA'

When you upload the report, Microsoft Dynamics CRM might filter only the first query using the parameter. This leads to the filtering not being applied to the second query:

SELECT <column1>, <column2>, <columnN>
FROM (@P1) AS CRMAF_FilteredAccount WHERE address1_stateorprovince = 'FL'
UNION
SELECT <column1>, <column2>, <columnN>
FROM FilteredAccount AS CRMAF_FilteredAccount
WHERE address1_stateorprovince = 'CA'

In the above example, while running the report from the Reports area in Microsoft Dynamics CRM and choosing the filter as annual revenue greater than 1,000,000, Microsoft Dynamics CRM will pass a query to the P1 parameter as follows:

SELECT <column1>, <column2>, <columnN>
FROM (SELECT FilteredAccount.* from FilteredAccount where AnnualRevenue > 1000000) AS CRMAF_FilteredAccount
WHERE address1_stateorprovince = 'FL'
UNION
SELECT <column1>, <column2>, <columnN>
FROM FilteredAccount AS CRMAF_FilteredAccount
WHERE address1_stateorprovince = 'CA'

This implies that the query would return only those accounts in Florida with an annual revenue greater than $1,000,000 and all the accounts in California, which is not what you intended. You wanted to view all the accounts in Florida and California with annual revenue greater than $1,000,000.

If you download the report from Microsoft Dynamics CRM and open it in Visual Studio, you will see the original version of the report that you uploaded into Microsoft Dynamics CRM. If you download the report directly from SQL Server Reporting Services, you will notice that Microsoft Dynamics CRM had modified the query but did not place the parameter where you wanted it to be.

For complex queries like this, you must use explicit pre-filtering.

Explicit Pre-Filtering

For complex queries such as queries using UNION statements, you might need to use explicit pre-filtering for your data. Unlike automatic pre-filtering, Microsoft Dynamics CRM does not rewrite the report query by passing values to the parameters during explicit pre-filtering when such a report is uploaded to Microsoft Dynamics CRM. You have to explicitly make the required changes to the report by adding the pre-filtering parameter to the report, and then referencing the parameter in the query. You can then execute the query using dynamic SQL.

When you use dynamic SQL, filtering through Advanced Find is enabled by creating a hidden parameter named CRM_FilteredEntity, for example, CRM_FilteredAccount, and by using this parameter in a dynamic SQL query expression. This parameter enables filtering on the table data obtained from the specified filtered view.

Using the same example as discussed earlier to highlight the limitation of automatic pre-filtering, the following table shows a query with automatic pre-filtering modified to use explicit pre-filtering using dynamic SQL. It is also assumed that while running the report from the Reports area in Microsoft Dynamics CRM, the filter has been applied as annual revenue greater than 1,000,000.

Note   Most of the standard Microsoft Dynamics CRM reports use the explicit pre-filtering option.

Passing of Filters in Filter Summary

A filter summary displays the value of the filter that is used when a report is run. In standard Microsoft Dynamics CRM reports, it is displayed as a Textbox report item in the report header that contains the filter text value. When the user runs the report, the Report Viewer displays an Edit Filter button. When the button is clicked, it enables the user to define a data filter. An example of a filter summary can be found in the User Summary report that is included with Microsoft Dynamics CRM.

To add a filter summary to a report, follow these steps:

1. Create a hidden string parameter called CRM_FilterText.
2. Add a Textbox report item to the report and set its Value property as follows: =Parameters!CRM_FilterText.Value.

When the report is run, the value of the CRM_FilterText parameter will be set by the system to the text of the current filter.

Default Filters

When you publish a report, you can set a default filter. For all the reports created using the report wizard, if you do not set a default filter, it is automatically set to all records of the entity modified within the last 30 days. For the procedure to define a default report filter, see Publishing a Report.

See Also

Concepts

• Using Parameters
• Publishing a Report

Other Resources

• Writing Reporting Services Reports