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.

Where’d the Data Go?

The Reported Problem

A few weeks ago I was contacted by a former employer because an SSRS report I had written several years ago was no longer returning the data that was expected.  The report returned Manufacturing Instructions that were attached to an Order or Order Item and was used to ensure that everything was done as requested by the customer.  There are 2 groups on the report:

1. Runs – a Run consists of orders that contain the same product.
2. Orders

The detail section consists of the specific Manufacturing Instructions for each order in the run.  Well, the report was showing the Runs and the Orders but no Instructions.  Obviously this is bad since the whole point of the report is to show the instructions.

Troubleshooting the Problem

I vpn’ed into the network and opened up the report to find the SQL that was generating the report.  I knew I had authored the report, but could not remember what the data source was.  There were 2 possible data sources:  SQL Server or DB2 on the iSeries.  I was hoping it would be SQL Server.  It wasn’t.  I had a query similar to this (underlining added) in the report that was connecting to DB2 using the IBM DA400 OLE DB Driver:

SELECT
    S.run_no,
    O.order_no,
    OI.instruction AS order_instruction,
    S.start_date,
    S.end_date
FROM
    @orders O JOIN 
    @order_instructions OI
        ON O.order_no = OI.order_no JOIN
    @schedule S
        ON O.order_no = S.order_no
WHERE
    (S.start_date >= '12/17/2009' AND
     S.end_date < '12/21/2009') OR
    (S.start_date < '12/21/2009' AND
     S.end_date > '12/14/2009')
UNION ALL
SELECT
    S.run_no,
    O.order_no,
    OII.instruction AS order_item_instruction,
    S.start_date,
    S.end_date
FROM
    @orders O JOIN 
    @order_items OI
        ON O.order_no = OI.order_no JOIN 
    @order_item_instructions OII
        ON OI.order_item_no = OII.order_item_no JOIN
    @schedule S
        ON O.order_no = S.order_no
WHERE
    (S.start_date >= '12/17/2009' AND
     S.end_date < '12/21/2009') OR
    (S.start_date < '12/21/2009' AND
     S.end_date > '12/14/2009')
ORDER BY
    run_no,
    O.order_no 

I ran the query in the dataset designer in Visual Studio 2003 (yup, SSRS 2000) and got data back including instructions.  I couldn’t figure out why I wasn’t seeing instructions on the report, especially since this report had been running without problem.  Then I ran the query in a custom query tool and that’s when I noticed the instruction column was not being returned as instruction but as column 00003!  What! I don’t have that anywhere?  Well, look at the underlined text in query, notice how I aliased the instruction columns with different aliases.  Turns out THIS was the problem.  Apparently when an update (ptf) was applied to the iSeries the behavior of column aliases in a UNION changed.  If you run a query like that in SQL Server the ALIAS used in the FIRST query is returned as the column name.  This was also how it USED to work in DB2 on the iSeries, but apparently that changed and instead of getting EITHER of the ALIASES it returns the column number, in the case you’d get 00003 for the ALIASED column.

The Solution

Well, simple solution, I removed the ALIAS in the second part of the UNION query and, VIOLA!, the report now worked again.

Why did I have the separate ALIASes in the first place?  I think it was because when I was testing the queries for correct data I wanted to know which instructions were at the order level and which were at the order item level and I ran the queries separately.  Then when I was convinced the data was correct I UNION’ed the queries and stuck them in the report without removing the ALIASes. 

So in reality the query was returning the correct data, but the SSRS report was looking for a column named instructions and it didn’t exist, so it didn’t show it!

Next time, I’ll know better!