Thursday, January 26, 2012

Building Queries for SSRS Data-Driven Subscriptions

Microsoft SQL Server Reporting Services (SSRS) 2008 R2 offers the ability to create report subscriptions based on live dynamic data.  I've found it especially useful for situations where many users wish to receive a copy of a report in their mailbox but groups of those users want their own specific report parameters.  Data-driven subscriptions are based on a SQL query which returns a list of users and a set of report parameters that are used to generate and send the report, and its all built into a single data-driven subscription.

However I discovered SSRS is not as efficient as I would like. While reading Microsoft's documentation (http://msdn.microsoft.com/en-us/library/ms156413.asp) I was getting the feeling that a report is generated for each recipient in a data driven subscription, even if several of those recipients use exactly the same parameters to generate their reports.  This might not be a problem for reports that run in seconds, but for reports that are resource intensive it can be quite an issue.  Imagine a report that normally takes 30 seconds or so to run: 30 seconds times maybe 20 users = 10 minutes of slamming the SQL server to generate the reports…. and if they want this report 3 times a day…. Not so good if you need that SQL server to perform well throughout the day.

So I did some testing to prove this out
and set out to find a solution.


Turns out, this is TRUE.  I created a data driven subscription for six recipients, but only using two unique sets of parameters for the report.  So I would think SSRS should only generate the report twice, one time for each set of unique report parameters and not for each recipient.  But it doesn’t.  It generates the report six times, once for each recipient, even though in this case four of those users are getting the exact same report.

The solution is to create queries for data driven subscriptions that return a more efficient result set based on unique sets of report parameters rather than a result for every single recipient.  To do this I needed to bundle the email addresses for all users who were expecting a report with the same parameters into a single field.  This turned out to be a lot more difficult than I would’ve thought.  After racking (melting) my brain, some googling, some pacing, and some more googling, I came up with a query based on a couple blog posts by Rob Farley: http://msmvps.com/blogs/robfarley/archive/2007/03/24/group-by-v-distinct-group-by-wins.aspx, which was linked from http://msmvps.com/blogs/robfarley/archive/2007/04/08/coalesce-is-not-the-answer-to-string-concatentation-in-t-sql.aspx. Using a query with the For XML Path() and Stuff() functions you can group sets of parameters (which are columns in a table) together and combine the recipient’s email addresses into a single column (user1@domain.com,user2@domain.com,user3@domain.com, etc)  rather than separate rows.   In my test sample it reduced six rows returned (and therefore six reports that would be created) down to two rows with the email addresses concatenated into a single "to" column. 



select distinct
    stuff((
        select '; ' + r2.EmailAddress
        from ReportTable r2
        where
        r2.Parameter1 = r.Parameter1 AND
        r2.Parameter2 = r.Parameter2 AND
        r2.Parameter3 = r.Parameter3 AND
        r2.Parameter4 = r.Parameter4 AND
        r2.Parameter5 = r.Parameter5
        order by r2.EmailAddress
        for xml path('')
    ),1,1,'') as 'toaddress',
    r.Parameter1,
    r.Parameter2,
    r.Parameter3,
    r.Parameter4,
    r.Parameter5
from ReportTable r

No comments:

Post a Comment