The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

Inside Report Prefiltering

Inside Report Prefiltering

  • Comments 9

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 (" &amp; Parameters!P1.Value &amp; ") as CRMAF" &amp; ChrW(95) &amp; "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 &lt;&gt; ''''
                        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.

Tao Yue

  • Tao, Thanks for the article. We have some very complex reports queries. For those you suggest we "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." A simple example of how to do this would be greatly appreciated. Thank you.

    krog

  • Tao, this is a great article. I've created a lot of reports using such approach. However I've encountered with a problem that looks like a bug (or not) in Advanced Find filter control.

    When viewing report in Microsoft CRM Report Viewer, it extracts the filter info from the RDL file (stored in Custom tag). When user saves the default filter it's also stored in the RDL. The filter info is actually the Fetch XML which is build dynamically by the viewer control.

    I've created a report and in the RDL file specified the Fetch XML with linked entities and attributes for each linked entity. This

    way when report is executed, the Fetch XML is converted to the SQL statement which is used as report data (when executed). Report viewer displays the filter correctly BUT when it builds the Fetch XML again, it does not include the attributes for the linked entities. Because if this the SQL statement is incorrect - the columns from the join table

    (link entity) are not included.

    Have you ever encountered with such behaviour ?

    Any help will be appreciated.

    Thanks is advance,

    Kobi

  • Writing a report for Microsoft CRM can seem a bit daunting at first if you’re not familiar with Microsoft

  • I have uploaded a canned CRM report to VS2005. Because the code is Dynamic SQL (which I do not use), I converted all of the main query to sql. I have also put an additional report parameter in the where clause. However, I do not know where to reference the prefilter (CRM_FilteredTask and its prefiltered conditions)in sql. Do I try and make it a subquery?. At present, unless I put the prefilter conditions in the where clause, the report returns all data, whereas the prefilter returns only a small portion. The user can not edit the default filter.

  • An exremely useful article, to get a report working on a single entity instance. There are a couple of tricks to getting it work in the real world. After making the changes to the .RDL (XML) file, the report still does not run in Visual Studio 2005.

    1. In the Data panel, run(!) the dataset and enter the (select * from FilteredSystemUser) as the parameter value. This gets it started.

    2. Now hit Refresh Fields and the build works and the dataset can be saved and previewed

  • hi,

    I'm working on customizing reports for CRM and I would be interested in knowing if there is any way of choosing which options will the user have in the advanced find prefilter. I mean, if I want a user to be able to filter by predefined label 'last 30 days' but not with 'last X days', how can I get to this?

    Thanks in advance

  • CRM Pre-Filtering is a very useful option that can be enabled on CRM reports to make them context sensitive

  • CRM MVP Joel Lindstrom, a Solutions Consultant for CustomerEffective , blogs about BI solutions. So you

  • All,

    About prefiltering, I try to implement something but can't get it to work:

    I need to get the count of currently selected accounts, and when there is only one account selected, use it as the default value of a parameter; when there are more than one account, use no default value(selecting all account will make the report very slow).

    So I tried this query:

    DECLARE @count INT

    SElECT @count= count( *)  FROM FROM FilteredAccount CRMAF_Account

    If @count < 10

    Begin

       SElECT   accountid,  name from  FilteredAccount CRMAF_Account order by name asc

    End

    else

    Begin

       SElECT TOP 0 accountid, name from FilteredAccount CRMAF_Account order by name asc

    end

    For some reason the query "SElECT @count= count( *)  FROM FROM FilteredAccount CRMAF_Account

    " always gives me the count of all accounts (~3000), even when I try to run the report for one single account.

    Any idea about this?

Page 1 of 1 (9 items)
Leave a Comment
  • Please add 7 and 7 and type the answer here:
  • Post