SQL Server Reporting Services 2005 - How to Handle Empty Reports

Question

I was wondering if it is possible to not attach Excel sheet if it is empty, and maybe write a different comment in the email if empty.

When I go to report delivery options, there's no such configuration.

Edit: I'm running SQL Server Reporting Services 2005.

Some possible workarounds as mentioned below:

MSDN: Reporting Services Extensions

NoRows and NoRowsMessage properties

I should look into these things.

Answer 1

I believe the answer is no, at least not out of the box. It shouldn't be difficult to write your own delivery extension given the printing delivery extension sample included in RS.

Answer 2

Yeah, I don't think that is possible. You could use the "NoRows" property of your table to display a message when no data is returned, but that wouldn't prevent the report from being attached. But at least when they opened the excel file it could print out your custom message instead of an empty document.

Answer 3

I have had success with using a Data-Driven Subscription and a table containing my subscribers, with the data-driven subscription query looking like this:

SELECT * FROM REPORT_SUBSCRIBERS WHERE EXISTS (SELECT QUERY_FROM_YOUR_REPORT)

In the delivery settings, the recipient is the data column containing my email addresses.
If the inner query returns no rows, then no emails will be sent.

---

For your purposes, you can take advantage of the "Include Report" and "Comment" delivery settings.
I imagine that a data-driven subscription query like this will work for you:

SELECT '[email protected]; [email protected]' AS RECIPIENTS,
CASE WHEN EXISTS (REPORT_QUERY) THEN 'TRUE' ELSE 'FALSE' END AS INCLUDE_REPORT,
CASE WHEN EXISTS (REPORT_QUERY) THEN 'The report is attached' ELSE 'There was no data in this report' END AS COMMENT

Then use those columns in the appropriate fields when configuring the delivery settings for the subscription.

Answer 4

Found this somewhere else...

I have a clean solution to this problem, the only down side is that a system administrator must create and maintain the schedule. Try these steps:

1. Create a subscription for the report with all the required recipients.

2. Set the subscription to run weekly on yesterday's day (ie if today is Tuesday, select Monday) with the schedule starting on today's date and stopping on today's date. Essentially, this schedule will never run.

3. Open the newly created job in SQL Management Studio, go to the steps and copy the line of SQL (it will look something like this: EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='1c2d9808-aa22-4597-6191-f152d7503fff')

4. Create your own job in SQL with the actual schedule and use something like:

IF EXISTS(SELECT your test criteria...)

BEGIN

EXEC ReportServer.dbo.AddEvent @EventType=... etc.

END