Advanced SSRS Reporting Part 3

Continuing with the advanced reporting series let's take a closer look at context sensitivity. In the previous example the report was made context sensitive to just a single CRM entity, namely the marketing list. What happens if you we wanted to make this context sensitive to multiple different entities? For example, let's say we have the following 2 parameters defined:

If the main query can be constructed to join against the various tables such that the context sensitivity will restrict the query sufficiently no matter which entity the report is launched from then that should work. However that is probably not going to be the case in most cases. And if you don't take the both parameters into account (and what happens when one is not passed), then you'll likely find that the query will run very inefficiently when run from the entity which was not taken into proper consideration. That's the best case. The worst and more likely case is either that the report will time out or it will return incorrect data due to not restricting the query sufficiently.

This is because, when a report is not run from within the context sensitive form or grid, the "default filter" is used. So for example, if the default filter is set as follows:

And the report is run from the "contact" context then the variables will be populated as follows:

CRM_FilteredList:

select  [list0].* from FilteredList as "list0" 

where ( list0.modifiedonutc >= '20120520 04:00:00' and list0.modifiedonutc <= '20120619 15:39:16' )

CRM_FilteredContact:

select  [list0].*  from  FilteredContact as "list0" 

where  ("list0".contactid = N'{92F129FB-4099-E111-9EFF-006073502237}')

That is, the marketing list query will be quite unrestricted (there would be no restrictions at all were there no default filters acting on it at all) whereas the contact query is restricted to the contact that is currently being viewed. This is of course expected behaviour (and the situation would be reversed were the report being run from the marketing list context).

So other than creating 2 separate reports for each of the queries - how can the procedure accomodate context sensitivity from both contexts?

The answer lies in interrogating the above variables from within the procedure. As can be seen, when the report is being run from the contact context the CRM_FilteredContact variable will contain "contactid" as part of the string. Similarly when running from the marketing list context the CRM_FilteredList variable will contain "listid" as part of the string. When not in context, the "id" field will not be part of the string.

Armed with this knowledge we can make a simple modification to the report processing logic as follows:

if (@CRM_FilteredList like '%listid%')
 set @SQL = 'insert into #report(ord, contactid, fullname, processed) ' +
    'select (ROW_NUMBER() OVER(ORDER BY c.Fullname))*10, c.contactid, c.fullname, 0 ' +
    'from  (' + @CRM_FilteredList + ') AS list ' + 
    'inner join FilteredListMember m on m.listid = list.listid and EntityType = 2 ' +
    'inner join FilteredContact c on c.contactid = m.EntityId '
else 
 set @SQL = 'insert into #report(ord, contactid, fullname, processed) ' +
    'select 0, c.contactid, c.fullname, 0 ' +
    'from  (' + @CRM_FilteredContact + ') AS c ' 

And the report will execute different queries depending from which context sensitivity it is run.

And once again the sky is the limit.