Pre-Filtering Reports – Fun with CRMAF_

I have blogged before about the technique for pre-filtering reports using the CRMAF_ prefix, enabling your CRM reports to run contextually etc., however I had a need recently to develop a series of reports that might call for all sorts of outer joins and unions, and pulling in a lot of fields.

I had all sorts of issues trying to get it to work properly – It ran fine pulling in the single SalesOrder table, but we have another custom table that sits under the SalesOrder table and it just spat that out!.

I decided to look at it slightly differently.

Firstly create a dataset that pre-filters the record you want to run it from – In this case from a contact:

select
CRMAF_filteredcontact.contactid as contactid
from
filteredcontact as CRMAF_filteredContact

If you were to run this, you would just get a long list of your contacts GUIDS.

Secondly you need to use the results of this as a parameter, so create a parameter called something like ‘Contact’ and choose the dataset as it’s source (In Report Builder it looks something like this):

Once this has been setup you create a second dataset which is you main query.

Because you are already pre-filtering with this first dataset, you don’t need to prefix with CRMAF_ in the second dataset. This not only makes life easier, but also opens up the ability to use other techniques for retrieving your data that you couldn’t before with pre-filtering your whole query. For example, I needed to do some calculations and aggregates prior to pulling in the data, as it was more efficient to do it that way. With that in mind I created a temporary table using Declare @tablename, and grabbed my data – doing some calculations on the way.

The key thing is that once you have created your query, part of it must pull in the parameter, so in my case it was:

where
customerid =@contact

The result was a much easier to construct main query. A much quicker report, and a more robust way of calculating fields.