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.