CRM Report Pre-Filtering with Multiple Datasets

As you know there are two ways of doing pre-filtering with CRM Reports (SQL and not FetchXML). The first one is using the CRMAF_ prefix alias like this:



I soon found limitation using this method. You can't use the same CRMAF_Filtered multiple times because it only filters on the first instance.

Therefore if you have multiple datasets or UNION query that are using that filter more than once, it will not work. To avoid this issue, we can use the Explicit pre-filtering - the second method that CRM provides.

Let's say that you want to have a report that display single Account details and the associated related records information. However rather than building complex single query you want to break it down into multiple datasets.

The Steps:
  1. Create a parameter called @CRM_FilteredAccount. You can set the default value to just:

  2. Build your datasets using this parameter. Use the SQL Dynamic Query so that CRM will replace the parameter with the Advanced Find instance that you specify when running the report.

    These are two of the dataset examples:





Hope this helps,
Andreas

Comments

  1. Thanks for investing the time and effort to create this useful article, it really helped me.

    ReplyDelete
  2. Hi, how can I use this on creating reports through BIDS? thank you.

    ReplyDelete
  3. Hey,

    You can just use this the same way as you would create a CRM report. Set the connection and put the queries in the dataset.

    Regards,
    Andreas

    ReplyDelete

Post a Comment

Popular posts from this blog

SharePoint 2013 anonymous access add attachments to list item

CRM Plugin - Parent and Child Pipeline

Sitecore custom publish agent from specific node and at a specific time