Microsoft Dynamics CRM Pre-Filtering

Just need to alias 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 = ‘’) 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