Microsoft Dynamics CRM Pre-Filtering for reports

CRM Pre-Filtering is a very useful option that can be enabled on CRM reports to make them context sensitive and to allow the report to be filtered using the Advanced Find functionality.  Although this is a great feature, it is often an area that is not fully understood which can lead to someone encountering unexpected results.

How is it Enabled?

Automatic Prefiltering (CRMAF_)

There are 2 ways to enable the CRM Pre-Filtering functionality. The easiest option is the CRMAF_ method which simply requires aliasing the filtered views with a name that starts with “CRMAF_”.   A query such as “Select name from FilteredAccount” can simply be changed to “Select name from FilteredAccount as CRMAF_Account”.  Aliasing the Filtered View with a prefix of CRMAF_ will allow CRM to recognize that you would like to enable this entity for pre-filtering.

When you enable the CRM Pre-filtering functionality using the CRMAF_ method, CRM will take a query such as the following and modify it when it is uploaded into CRM:

SELECT name, accountnumber
FROM FilteredAccount as CRMAF_Account

Becomes:

SELECT name, accountnumber
FROM (@P1) as CRMAF_Account

Then CRM will pass a query to the P1 parameter depending on how the report is being filtered. For example: If you are running the report from the Reports area and use the Pre-filtering functionality to filter to only show Accounts that are Active, the resulting query would be something like:

SELECT name, accountnumber
FROM (select FilteredAccount.* from FilteredAccount where statecode = 0) as CRMAF_Account

If you are within a specific Account and run the report, the resulting query would be something like:

SELECT name, accountnumber
FROM (select FilteredAccount.* from FilteredAccount where AccountId = '<CurrentAccountId>') as CRMAF_Account

When you are looking at a list of Accounts with 3 selected and choose the option to run the report against the selected records, the resulting query would be something like:

SELECT name, accountnumber
FROM (select FilteredAccount.* from FilteredAccount where AccountId in ('<1stAccountId>', '<2ndAccountId>', '<3rdAccountId>') as CRMAF_Account