Microsoft Dynamics CRM Team Blog: Microsoft Dynamics CRM Pre-Filtering Tips

[Blog bot]

Источник: http://blogs.msdn.com/crm/archive/20...ring-tips.aspx
==============

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 = '') 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 ('', '', '') as CRMAF_Account

Explicit Filtering

The CRMAF_ method works in most cases but for complex queries such as queries using UNION's, you may need to use the 2nd option referred to as Explicit Filtering. The CRM SDK contains information about how to use both methods under the Report Writers Guide section:

http://www.microsoft.com/downloads/d...DisplayLang=en

Under the Writing Reporting Services Reports section, you can reference the Using Filters in a Report section.  Another posting to the CRM Team Blog contained a great explanation of how CRM Pre-Filtering is enabled: Inside Report Prefiltering.

A potential problem is that when you use the CRMAF_ method, CRM needs to add the parameter for you as opposed to you creating the parameter yourself when using Explicit Filtering. With a more complex query such as a query using UNION statements, this can lead to unexpected results as CRM may only add the parameter to the first query.

For example, suppose you had a query such as the following:

SELECT name, accountnumber
FROM FilteredAccount as CRMAF_Account where address1_stateorprovince = 'FL'
UNION
SELECT name, accountnumber
FROM filteredAccount as CRMAF_Account where address1_stateorprovince = 'CA'

When you upload the report, CRM may just filter the first query using the parameter which would cause the CRM filtering to not be applied to the second query causing unexpected results:

SELECT name, accountnumber
FROM (@P1) as CRMAF_Account where address1_stateorprovince = 'FL'
UNION
SELECT name, accountnumber
FROM FilteredAccount as CRMAF_Account where address1_stateorprovince = 'CA'

For example:

In the scenario above, running the report from the Reports area and choosing to filter where Annual Revenue > 1,000,000, the resulting query would be something like:

SELECT name, accountnumber
FROM (select FilteredAccount.* from FilteredAccount where AnnualRevenue > 1000000) as CRMAF_FilteredAccount where address1_stateorprovince = 'FL'
UNION
SELECT name, accountnumber
FROM FilteredAccount as CRMAF_Account where address1_stateorprovince = 'CA'

This would mean it would return any Accounts in Florida with a Annual Revenue of  $1,000,000 or any Account in California (not just ones over $1,000,000). If you download the report from CRM and open it in Visual Studio, you will see the original version of the report that you uploaded into CRM.  If you download the report from SRS directly, you would see that CRM had modified the query but did not place the parameter where you wanted it to exist:

SELECT name, accountnumber
FROM (@P1) as CRMAF_FilteredAccount where address1_stateorprovince = 'FL'
UNION
SELECT name, accountnumber
FROM (@P1) as CRMAF_FilteredAccount where address1_stateorprovince = 'CA'

To resolve this issue you can use Explicit filtering. This requires you to create the parameter yourself but you are able to place it anywhere in your query you want. You need to use dynamic SQL to do this as shown in the CRM SDK. Instead of a query such as the following:

select name, accountnumber from FilteredAccount as CRMAF_Account
You would create a parameter called CRM_FilteredAccount and the query would be:
DECLARE @SQL nvarchar(4000)
SET @SQL = '
SELECT name, accountnumber FROM ('+@CRM_FilteredAccount+') AS FA'
EXEC (@SQL)

If you are looking for more examples of Explicit Filtering, you can look at any of the standard CRM reports.  All of the standard CRM reports use the Explicit Filtering method. 



Common Issues Related to CRM Pre-Filtering

Reports displaying less data than expected

Potential Cause 1

   - A default filter is enabled (ex. Modified in the Last 30 Days).  You can view the Default Filter by selecting a report in the Reports area and clicking Edit Default Filter from the More Actions menu.  Try clearing the default filter to see if the issue no longer occurs.  When an entity is enabled for CRM Pre-Filtering, a default filter is enabled to only display records for that entity that have a Modified Date within the last 30 days.

Potential Cause 2

   - A default filter is enabled on an entity that is not available for Advanced Find.  Do not enable CRM Pre-Filtering on an entity that is not available for Advanced Find as a default filter will be enabled (Modified in the Last 30 Days) but it will not appear in the user interface.  For Example:  If you are creating a report that queries Opportunities and Opportunity Products, do not enable Pre-Filtering on the Opportunity Product entity as it is not available for Advanced Find.  This can also occur if you created a report using the Report Wizard without the Update Rollup 2 being applied to the CRM server.  A hotfix was created to address that issue:

           -Only records that were changed in the past 30 days are displayed for the related record type when you create a report by using the Report Wizard in Microsoft Dynamics CRM 4.0. Read more at http://support.microsoft.com/kb/956079.

Reports displaying more data than expected

The CRMAF_ method is being used but Explicit Filtering is necessary to correctly enable CRM Pre-Filtering (see Explicit Filtering under the How is it Enabled section under the CRM Pre-Filtering topic).  Try the Explicit Filtering option.

Thanks,

Dana Martens



Источник: http://blogs.msdn.com/crm/archive/20...ring-tips.aspx