Using Data Pre-Filtering in Microsoft Dynamics CRM Reports
Have you been looking for a way to retrieve and view query results faster? Pre-filter the data!
Data pre-filtering in Microsoft Dynamics CRM reports lets you retrieve and display result sets quickly, makes reports context-sensitive, and allows reports to be filtered using the Advanced Find feature. Data pre-filtering complements data filtering supported by Microsoft SQL Server Reporting Services, which limits the data returned by reports. There are two ways that you can enable data pre-filtering:
- Automatic Pre-Filtering: Automatic data pre-filtering, which is suited for simple queries, can be enabled by aliasing entity tables in the queries. The alias name should begin with “CRMAF_”. When you enable automatic data pre-filtering using the “CRMAF_” prefix, Microsoft Dynamics CRM modifies the query to include a parameter (for example, P1), and then passes 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 an existing query.
- Explicit Pre-Filtering: For complex queries such as queries using UNION statements, you might need to use explicit pre-filtering. Unlike in automatic pre-filtering, Microsoft Dynamics CRM does not rewrite the report query by passing values to the parameters during explicit pre-filtering when a report is uploaded to Microsoft Dynamics CRM. You have to explicitly make the required changes by adding the pre-filtering parameter to the report, and then reference the parameter in the query. You can then execute the query using dynamic SQL.
