Using CRM auto filtering in custom reports with complex SQL

Report prefiltering in Microsoft CRM 4.0 is a very cool feature - when it works.  The normal ways of setting up this auto filtering feature in your custom CRM reports are 1) special aliases, and 2) SQL strings.

Special aliases

The alias approach is the simplest one to use.  If you have a report that needs prefiltering, change your SQL from this:

select firstname, lastname 
from FilteredContact

to this:

select firstname, lastname 
from FilteredContact AS CRMAF_FilteredContact

The trick is the alias.  All you need to do is add an alias with CRMAF\_ in front of the view name, and CRM will rip out your SQL at report runtime and replace it with a custom SQL statement.  I think the acronym must stand for CRM Auto Filter.  By using a special alias CRM recognizes, the actual SQL run on your server will be similar to this, where contacts are prefiltered for city = 'Denver'.

select firstname, lastname 
from (select contact0.* 
      from FilteredContact as contact0 
      where address1_city  = 'Denver') AS CRMAF_FilteredContact

SQL strings

The other route you can go is mash together SQL strings and get a parameter from CRM that is the prefilter SQL statement.  You've probably done this kind of this kind of thing before:

declare @sqlstring varchar(max)
set @sqlstring = 'select firstname, lastname 
from (' + @CRM_FilteredContact + ') AS MyFilteredContacts '
exec (@sqlstring)

This really isn't bad when you have a small SQL statement, but you probably wouldn't need to create a custom CRM report if it was a simple SQL statement, right?  The CRM users could create their own simple report inside CRM for that.

When the SQL statement gets complex, you have quote and string bugs.  When the SQL gets lengthy, you can't have over a certain number of characters in the SQL string or you have to split it and you end up with:

exec (@sqlstring1 + @sqlstring2 + @sqlstring3)

You can get it to work, but it's pretty yucky.

The problem

I much prefer the alias approach.  It's much easier to read, and the aliases are not too much trouble to add in and get prefiltering in CRM for free.

The problem is, the alias technique breaks down when the queries get more complex or you need multiple datasets in your report using the same prefiltering.  Here are the cases I've found and the workarounds.

Dataset based on a union query

If you have a query with a union statement, like:

select firstname, lastname 
from FilteredContact AS CRMAF_FilteredContact
where address1_city = 'Denver'
union
select firstname, lastname 
from FilteredContact AS CRMAF_FilteredContact
where address1_city = 'Highlands Ranch'

CRM will do the alias filtering trick, but only for the first select.  The second select in this query will not be filtered at all.

The fix I've come up with uses SQL temp tables.  Maybe not the best approach, but I had a lot of reports that were not auto filtering as expected, and this has worked so far:

select * 
into #FilteredContact
from FilteredContact AS CRMAF_FilteredContact

select firstname, lastname 
from #FilteredContact
where address1_city = 'Denver'
union
select firstname, lastname 
from #FilteredContact
where address1_city = 'Highlands Ranch'

drop table #FilteredContact

This let's CRM do what it wants to, and swaps out your SQL where it finds the CRMAF\_FilteredContact alias the first time.  It also let's you leave the rest of your SQL pretty much intact.

Dataset based on multiple queries selecting into a temp table

I had another query that was suffering the same problem, but this query was a little different.  I needed the data displayed in columns, so I created a temp table and populated it with selects.  There were multiple selects in the query filling up the columns in this temp table.  Everything ran great without auto filtering.

But with auto filtering, the same thing happened.  The first select statement got filtered.  The ones after that were untouched.  The fix I used was the same.  I created a temp table, populated it with the auto filtered data, then selected off that temp table throughout.

To be a good TempDB citizen, I dropped the temp table at the end of the query.  SQL Server does the temp table drop for you when the table is out of scope, but it helps to have the drop statement in there when testing the query in SQL Server Management Studio and running it multiple times in the same query window.

Reports with multiple datasets

This technique works if you have a complex query that needs auto filtering.  But what if you have multiple datasets in your report, and they all need to be auto filtered?

In one report, the queries were very similar.  I had two datasets that were identical expect one used report parameter A and one used report parameter B.  This was to compare two funnels for two different time periods.  Auto filtering worked fine on the first dataset and funnel, but didn't do anything on the second dataset or funnel.

The fix in this case was to create one dataset that didn't filter on either report parameter A or parameter B.  Then in the report, have the funnel controls themselves do the filtering based on the report parameters.  Most SQL Server Reporting Services data controls have a filter property.  I've never really used them before now, since it almost never makes sense to pull all the data from the database and then filter inside the report.  I'd rather filter on the database first.  However, in this case, the control filters helped and I got the result I wanted because there was one dataset with one CRMAF\_ alias.

On another report, there were three charts built using three different datasets.  The datasets were the same except for the group by clause.  I was able to use the same approach and condense these to one dataset with no group by clause, then do the grouping in the chart controls.  Again, it made me feel a little bad that the data wasn't already summed and grouped when it got to the chart control, but at least it was working.