Inside Report Prefiltering
Microsoft Dynamics CRM 3.0 includes a report prefiltering capability. In this post I will describe the process by which prefilters are sent to SQL Server Reporting Services for execution as part of the query. It gives examples of the two methods of enabling report prefiltering when you create a new Reporting Services report. Each of the methods has benefits and drawbacks, which become clear when the prefiltering process is examined.
Report prefiltering in Microsoft Dynamics CRM 3.0 makes it possible to narrow the scope of a report, to make the results more relevant, or to return results more quickly. In contrast to just creating a report parameter and referring to it in the SQL query, report prefiltering leverages the Advanced Find query interface. This allows the crafting of very specific filters, while remaining within the familiar CRM environment.
One of the major benefits of enabling report prefiltering when you create a new Reporting Services report is that reports are now integrated into CRM. They can appear not only in the Reports area, but also in other lists and forms. From a list, reports can run on a predefined view, making generic reports more versatile, and empowering the end-user to tailor that report for his/her own needs. Since prefilters are based on Advanced Find, this can be done on any view that prepopulates in Advanced Find. From a form, reports can be run on one specific record, making reports useful for critical formatting (e.g. a prefilled form to printout to the customer) as well as data analysis.
To reap these benefits, the report must be written to take advantage of prefiltering, be associated with the correct entities, and marked to appear in the proper areas of CRM. This process is described in the Report Writer’s Guide section of the CRM SDK (http://msdn2.microsoft.com/en-us/library/aa645137.aspx) , step-by-step in an earlier posting on this blog (http://blogs.msdn.com/crm/archive/2006/05/03/589243.aspx), and as a three-part narrated screencast on the CRM Sandbox (http://www.gotdotnet.com/codegallery/news/newsitem.aspx?id=b6499eb0-0eae-4597-b38e-ee9cbc71a404&newsId=b96c8a40-b67e-4ffc-8828-ae5b82996bb2).
There are two types of prefilterable queries: automatic and explicit. Why are there two different ways of designating a query as prefilterable? Which is the best one? Why does this report not run properly after it has been enabled for prefiltering? To answer these questions, it is instructive to take a deeper look at how prefiltering works in CRM.
Automatic Prefiltering
Automatic queries allow you to take advantage of prefiltering without having to restructure the report in any way. They can be used for simple queries:
SELECT CRMAF_Account.columns
FROM FilteredAccount CRMAF_Account
WHERE query–conditions;
When you upload a report containing this query to CRM, it will recognize the magic token (“CRMAF_”) in the table alias CRMAF_Account, which indicates that there is a prefilter on the Account entity. But the report runs through SQL Server Reporting Services, which looks at the query and just sees another SQL query. To allow a prefilter to be passed in to Reporting Services, CRM will rewrite the query and add parameters to the report. The parameters allow a prefilter to be passed in, while the query is modified to accept that prefilter.
A report is just an XML file using the Report Definition Language (RDL) schema. So rather than loading up the report in Visual Studio and using the graphical designer, let’s cut to the chase and look at what’s happening in the RDL. Once the report has been uploaded to CRM, the query now looks like:
<CommandText>=”select CRMAF_Account.columns from (” & Parameters!P1.Value & “) as CRMAF” & ChrW(95) & “Account where query-conditions” </CommandText>
As you can see, your query has been wrapped in a Visual BASIC expression, with reference to a parameter which contains the prefilter. When you run the report from within CRM:
1. The CRM report viewer presents the user with an Advanced Find query interface. The prefilter is generated as a WHERE clause for a SELECT *.
2. SQL Server Reporting Services receives the prefilter as a parameter. It fires up the VB interpreter and sticks the parameter inside the <CommandText>=”expression.
3. Reporting Services passes the now-completed SQL query, with prefilter embedded, to the SQL Server query engine. It now looks like this:
SELECT CRMAF_Account.columns
FROM (SELECT * FROM FilteredAccount WHERE prefiltering–conditions) CRMAF_Account
WHERE query–conditions;
As you can see, the prefilter acts as a subquery.
Automatic prefiltering works well for simple queries. A SELECT, a JOIN or two. All you have to do is use a table alias – CRM takes care of the rest when the report is uploaded.
Explicit Prefiltering
But if you’re a SQL guru and have written a 20 KByte SQL query for your CRM report (this is a real number), you should take a look at explicit filtering. First, because of the length – the query will expand when wrapped as VB, and you may bump into the 32K SQL query length limit. Also, because SQL is a very complex language, some language constructs may not play well with the query rewrite done for automatic filtering. With a query this complicated, it’s best to take full control of the process.
CRM performs the same three steps upon running a report with explicit prefiltering. However, CRM does not rewrite the report query or parameters when such a report is uploaded. You have to make the necessary changes yourself when writing the repot. Once you’ve added the parameter to the report, you must reference the prefiltering parameter in the query. The prefilter is just a string passed in as a parameter. To execute it, either:
1. Write a VB expression to wrap the query, as CRM does for automatic prefiltering, or
2. Use dynamic SQL in the query, as many of the out-of-the-box CRM reports do.
We’ve already seen the first; now let’s look at the second. For example, look at the User Summary.rdl file on the CRM install CD. The User Summary report uses dynamic SQL to accept prefiltering on the User entity. To do this, it first creates a string parameter called CRM_FilteredSystemUser:
<ReportParameter Name=“CRM_FilteredSystemUser“>
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>select * from FilteredSystemUser</Value>
</Values>
</DefaultValue>
<Prompt>CRM_FilteredSystemUser</Prompt>
</ReportParameter>
CRM will recognize this parameter and mark the SystemUser entity for prefiltering. But the query itself does not use automatic prefiltering, so it will not get rewritten by CRM. Instead, the query must itself make use of the prefilter:
<Query>
<DataSourceName>CRM</DataSourceName>
<CommandText>
Declare @SQL Varchar(4000)
SET @SQL = ‘SELECT …
FROM (‘ + @CRM_FilteredSystemUser + ‘) AS systemuser
LEFT JOIN …
WHERE … and domainname <> ””
ORDER BY …’
EXEC(@SQL)
</CommandText>
<QueryParameters>
<QueryParameter Name=“@CRM_FilteredSystemUser“>
<Value>=Parameters!CRM_FilteredSystemUser.Value</Value>
</QueryParameter></QueryParameters>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
In order to use the prefilter inside the SQL query, the query is marked as a parameterized query in Reporting Services. Under the <QueryParameter> node, we create a SQL query parameter @CRM_FilteredSystemUser. Then in the <Value> node, we set the value of this parameter to be =Parameters!CRM_FilteredSystemUser.Value, i.e. the value of the Reporting Services parameter CRM_FilteredSystemUser. We have to use this parameterized query because Reporting Services runs on top of the SQL query engine, so that a report parameter is meaningless unless it is passed into SQL as a query parameter.
The SQL query itself is a dynamic query which declares a varchar string @SQL to store the query. When building the SQL query into the string, it references the SQL query parameter @CRM_FilteredSystemUser as a subquery. The entire query then gets executed with EXEC(@SQL). Notice the quote-escaping ””, which is necessary because the query is itself inside single-quotes. That’s four quotes in a row, which ends up being the empty string ” when the string is interpreted as a SQL query and executed.
Perhaps you’ve already written a very complicated query and don’t want to rewrite it in dynamic SQL or quote it as a VB expression. In particular, the quote-escaping makes it a bit difficult to work with the query in SQL Management Studio. The workaround then would be to use a temp table, and use dynamic SQL only where the prefilter gets inserted. Then, you can take this temp table and use it in the rest of the query, which remains stock SQL, without VB or dynamic SQL to clutter up the syntax highlighting.
Reports can be written by users of many skill levels, from the Visual Studio neophyte to the SQL and VB guru. The two ways of designating an entity as prefilterable for CRM lets you choose how much of the process to control. Picking the right method will allow you to harness the full power of SQL Reporting Services, the T-SQL query language, and the CRM Advanced Find query interface.
