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

In addition to data filtering supported by Reporting Services, Microsoft CRM supports data pre–filtering through the Advanced Find feature. In general, this feature is available for all entities in Microsoft CRM, although some entities can be set to not be searchable using Advanced Find.

Two methods to enable filtering in reports through Advanced Find are discussed next, followed by a discussion of the filter summary and setting a default filter.

Filtering Data using CRMAF_FilteredEntity

One method to enable a report for filtering is to alias entity tables using an alias name in the format CRMAF_FilteredEntity.

For example, to alias a table name through code in an SQL query, use the following SQL statement:

SELECT column1, column2, columnN
FROM FilteredAccount AS CRMAF_FilteredAccount

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

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

Explicit Filtering using CRM_FilteredEntity

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

DECLARE @SQL nvarchar(4000)
SET @SQL = '
SELECT * FROM ('+@CRM_FilteredAccount+') AS FA'
EXEC (@SQL) 

Passing of Filters in Filter Summary

A filter summary displays the value of the filter used when a report is run. In standard Microsoft 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 that, when clicked, enables the user to define a data filter. An example of a filter summary can be found in the User Summary report that ships with Microsoft 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 to: =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

A default filter can be set when publishing a report. If you do not set a default filter in a report, the default filter 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 to Microsoft CRM.

Related Topics

Using Parameters

Publishing to Microsoft CRM