SSRS – CRMAF auto filter on multiple datasets

Not sure if this method is well known or not, but I discovered it out myself and would like to share.

As we know the CRMAF_ type autofilters do not support filtering on multiple datasets. Meaning that if you create

1. DataSet1 – select * from FilteredAccount As CRMAF_FilteredAccount
2. DataSet2 – select * from FilteredAccount As CRMAF_FilteredAccount

then create a report and publish it into CRM, the filters on row selection, executing reports from single forms etc. will not work.

Fortunately there is a simple way around this limitation. The trick is to create a hidden parameter, fill it in with ID’s from a filtered set and then use that in an IN filter inside the other queries.

Example:

1. Launch Business Intelligence Development Studio and create a new report.
2. Add a dummy filter dataset, and query the ID parameter of the entity you like to filter on.

   

3. Create a filter parameter, to be used in the other datasets. Remember to make it hidden and allow multiple values

4. Specify the “Available Values” to be taken from the filter dataset. Do the same with “Default Values”
   

5. Create the datasets depending on the filter. Probably at least 2, else this method doesn’t make sense. Inside the query filter using the parameter created in point 3.

   
   Notice you shouldn’t use the CRMAF_ filter inside this query.
   
   You can see how I created the datasets in BIDS:
   
   
   Remember to set the default values of the @AccountFilter parameter, else the environment will not be able to parse the query.
   
   Repeat this with all needed datasets. In this example I created a second dataset from the second query.

6. Use the dataset in the report
   
   Here I have simply used them in two separate tables.

7. Deploy report to CRM & enjoy fully working CRM auto filtering on multiple datasets

Cheers