Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

T-SQL Tuesday #005 – Don’t Send a Blank Report from a Report Subscription

Aaron Nelson (@SqlVariant) is hosting this month’s T-SQL Tuesday (#tsql2sday) and his topic is reporting.

One of the frustrations I have had and seen with Reporting Services subscriptions, especially when using non-enterprise edition, is that there is no built-in way to specify that a report should not be emailed if there is no data.  I came up with a work around, albeit not a perfect solution.

The first step is to note what jobs you have already created on your reporting SQL Server so you will know what job applies to the new subscription.  This is because SSRS subscriptions are driven by SQL Agent Jobs.  I’m sure there are other ways to find out which job ties to your new report subscription (querying sysjobs for most recently created or directly querying the ReportServer database), but in my case knowing what you already have and visually finding the new job was the easiest.

Next, create your scheduled subscription and under “Select Schedule” set the dates to begin and end the subscription to the past.  This is an important step as you don’t want the job to run on it’s own, but when you determine it should run.  Then click “OK” to save the subscription.

Now, you should have new job in SQL Agent with a GUID as a job name.  Now you need to create a new SQL Agent job with one step.  In that step you will, unfortunately, need to duplicate the logic in the report.  For example, Previous Day’s Sales, you would do something like this:

IF EXISTS ( SELECT
1
FROM
Sales.SalesOrderHeader AS SOH
WHERE
-- Start of Yesterday
SOH.OrderDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0) AND
-- beginning of today
SOH.OrderDate < DATEADD(DAY, DATEDIFF(DAY, 9, GETDATE()), GETDATE()) )
BEGIN
EXEC
msdb.dbo.sp_start_job @job_name = N'B05BEC48-721A-4BE4-B2E8-9B13F6143025'
END


As you can see, this code checks to see if any Orders were placed the previous day, and, if there were any orders, the job created by our SSRS Subscription is run, if no orders were placed then the SSRS subscription does not run.  You would then schedule this job to run on a daily basis so the subscribers to the report will get an email whenever there have been orders made the previous day.



The problem with this work around is obvious, when you have data you run similar queries twice, thus increasing the load on your SQL Server.  If you have many subscriptions, you may not want to use this work around for them all.  I came up with this because we had an exception report and the business users only wanted to receive an email when there was an exception.

Comments

Posted by Steve Jones on 13 April 2010

Great idea, and an easy one to overlook. I hate getting blank reports.

On the other hand, it's possible this is something you want to notify someone about if you expect orders every day.

Posted by Jason Brimhall on 13 April 2010

Nice solution Jack.  I like the idea myself.  I wouldn't want to receive a blank report.

Posted by Jack Corbett on 14 April 2010

Steve,

Yes, in this case it would probably be more important to get a report when there are no orders, but in the case where I used this the report was blank on most days, so it made more sense to not send it.

Jason,

This is definitely one of the failings, in my opinion, of SSRS Subscriptions.  Just like when you are able to set a No Rows message for a data region, you should be able to set a No Rows action to an subscription.  A little tougher to do if you have multiple datasets, but it should be possible.

Leave a Comment

Please register or log in to leave a comment.