Filtering in Reports Mscrm 2011

Enabling Data Pre-Filtering in SQL-based Reports

There are two ways that you can enable data pre-filtering on Microsoft Dynamics CRM SQL-based reports: automatic and explicit.

Automatic Pre-Filtering

Automatic data pre-filtering is suited for simple queries. 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_.
For example, the following table shows a simple query modified to enable pre-filtering on the Account entity.

 

Query without pre-filtering	Modified query with automatic pre-filtering enabled
SQL SELECT <column1>, <column2>, <columnN> FROM FilteredAccount;	SQL SELECT <column1>, <column2>, <columnN> FROM FilteredAccount AS CRMAF_FilteredAccount;

When you enable automatic data pre-filtering functionality by 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.

 

Query with automatic pre-filtering	Modified by Microsoft Dynamics CRM
SQL SELECT <column1>, <column2>, <columnN> FROM FilteredAccount AS CRMAF_FilteredAccount;	SQL SELECT <column1>, <column2>, <columnN> FROM (@P1) AS CRMAF_FilteredAccount;

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:

SQL

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:

SQL

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:

SQL

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, and then 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 adds a parameter in the query. With a more complex query, such as a query that uses 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:

SQL

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 may filter only the first query using the parameter. This leads to the filtering not being applied to the second query:

SQL

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:

SQL

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 Microsoft 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 Microsoft 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 exist.
For complex queries like this, you must use explicit pre-filtering.

Explicit Pre-Filtering

For complex queries such as queries using UNION statements, you may need to use explicit pre-filtering. 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 must 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 by 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.
Taking 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 by 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.

 

Query with automatic pre-filtering

Query modified to use explicit pre-filtering

SQL 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'

SQL DECLARE @SQL nvarchar(4000) DECLARE @CRM_FilteredAccount nvarchar(2000) Set @CRM_FilteredAccount = 'Select FilteredAccount.* FROM FilteredAccount where AnnualRevenue > 1000000' SET @SQL = 'SELECT <column1>, <column2>, <columnN> FROM ('+@CRM_FilteredAccount+') AS FA where address1_stateorprovince = ''FL'' UNION SELECT <column1>, <column2>, <columnN> FROM ('+@CRM_FilteredAccount+') as CA where address1_stateorprovince = ''CA'' ' EXEC (@SQL)

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

Enabling Data Pre-filtering in Fetch-based Reports

Fetch-based reports support only automatic data pre-filtering. A report can have multiple data sets and multiple FetchXML queries. One data set supports one FetchXML query. . To enable pre-filtering for the primary or linked entity in a Fetch-based report, you must set the value of the enableprefiltering parameter to “1”, and specify a parameter name in the prefilterparametername property. The parameter name should start with “CRM_” to specify it as a hidden parameter. As with the SQL-based report, this parameter specified in the FetchXML query acts as a sub query within the FetchXML query, and the sub query is constructed with the value specified by the user in the Advanced Find area while running a report.
The following example displays how to enable pre-filtering for the primary entity in the FetchXML query:

XML

<CommandText>&lt;fetch distinct="false" mapping="logical"&gt;
   &lt;entity name="account" enableprefiltering="1" prefilterparametername="CRM_FilteredAccount"&gt;
      &lt;attribute name="name" /&gt;
      &lt;attribute name="accountid" /&gt;
   &lt;/entity&gt;&lt;/fetch&gt;</CommandText>
<DataSourceName>CRM</DataSourceName>

Similarly, you can enable pre-filtering for the linked entity. You can also specify a different pre-filtering condition for the linked entity in the FetchXML query by specify a different and unique name for the parameter name in the prefilterparametername property.
If you are manually modifying a Fetch-based report definition without using the Report Wizard in the Microsoft Dynamics CRM Web application or Business Intelligence Development Studio to enable pre-filtering for primary and linked entities, make sure that you:

1. Set the value of the enableprefiltering parameter to 1 for the required entity or linked entity, and specify a unique parameter name for the prefilterparametername property.
   
   
   XML

   <fetch distinct="false" mapping="logical">
      <entity name="account" enableprefiltering="1" prefilterparametername="CRM_FilteredAccount">
   

2. Create a corresponding query parameter with the same name as specified for the prefilterparametername property. Make sure that the parameter name starts with CRM_ to specify it as a hidden parameter.
   
   
   XML

   <QueryParameters>
      <QueryParameter Name="CRM_FilteredAccount">
      <Value>=Parameters!CRM_FilteredAccount.Value</Value>
   </QueryParameter>
   

3. Create a corresponding report parameter with the same name.
   
   
   XML

   <ReportParameters>
       <ReportParameter Name="CRM_FilteredAccount">
         <DataType>String</DataType>
         <Prompt>CRM Filtered Account</Prompt>      
       </ReportParameter>
     </ReportParameters>
   
   

Passing Filters in the Filter Summary

A filter summary displays the value of the filter that is used when a report is run. In Microsoft Dynamics CRM reports, it is displayed as a text box 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:

• Create a hidden string parameter called CRM_FilterText.
• Add a text box 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.