CRM Pre-filtering is a unique feature provided by Microsoft Dynamics CRM to enable reports to be context sensitive and present the report with the Advanced Find functionality. While creating the report this capability will not become apparent until posted as a report through the report manager.

Using the steps below we will walk through the steps Follow these steps to complete: Create a new report, select new table wizard Create the data source For the Data Set paste in this query SELECTcustomeridname, name, owneridname, estimatedclosedate, estimatedvalue, opportunityratingcodename, closeprobability, statecodename FROMFiltered Opportunity Note: we are not using the CRMAF_ filter yet It it not necessary at this time to enhance or make costeic change to the report, we are merely exploring this function. Modify the query and append the alias: AS CRMAF_Filtered Opportunity Your query should now look like this: SELECTcustomeridname, name, owneridname, estimatedclosedate, estimatedvalue, opportunityratingcodename, closeprobability, statecodename FROMFiltered Opportunity AS CRMAF_Filtered Opportunity Save the report with a ‘Save As’ and name it: ‘Opps By Owner_with Filter’Post as a new report in CRMRun the report.

The difference drops as the number of Total Options decreases such that when there are around 100 Options the difference becomes fairly trivial.

So in short, it is the number of Options on the entity that are the major cause of query degradation and that can be attributed to the additional joins required to obtain the friendly name (as described above).

In fact, it is very likely that the reasons for going with a whole bunch of custom Option Sets vs.Select the report and click ‘Edit Default Filter’Set as desired.In our example we will pre-set to ‘Open’Click to ‘Save Default Filter’Run the report to test.From what I've read, most attribute this degradation to the fact that the Filtered Views (unlike other views) perform additional security checks against the CRM security model.And while this is indeed the case, my experience has indicated that this particular aspect does not really impact adversely.The correct way of thinking is paramount when confronting a business challenge and this is what I hope to bring to the table.As is well known it is best practice to use Filtered Views when performing queries on the CRM database most notably for report writing.This is because doing so adheres to the SDK standard and also yields many benefits such as: There is one downside - there can be a performance degradation when running queries against Filtered Views (vs. Of course this can be very significant as if your report takes a long time to run it more than negates all the benefits cited above.However, the query performance of Filtered Views needs some additional analysis.Third, the number of Option Sets shown in the analysis has to be slightly unusual.In most cases, I would imagine that there would be far fewer of these and therefore using the Filtered View for your queries will simply not have any noticeable impact on performance (although it's easy to run a quick comparison query to determine if this statement is correct or not).The intention of this blog is to focus on the business application of Microsoft CRM and its surrounding ecosystem.In doing so, whenever discussing a topic I will endeavor to avoid presenting dry facts but rather to relate it to the practical application and/or impact it might have on the business, the pros, cons, best practices etc.In fact, this additional check is fairly trivial (it might be that in cases where there is a heavily nested security hierarchy that this impacts more - so not to be completely disregarded). For example, "account" has 118 Option Sets and 169 Two Options for a total of 287 Options.As you can see, a simple query against the Filtered Account vs. That drops dramatically for "event" where there are only 191 Options - dramatically but still not sufficient as the query difference remains at 6 seconds.