Stop empty reports from being sent in SSRS 2008 R2 Data-Driven Subscription

Question

I am scheduling some reports by using data driven subscription in SSRS 2008 R2.

Regardless of whether the generated report is empty or not, reports are still sent to recipients. This has been a common problem for long time, and honestly, the suggestions I have seen on the forums did not quite work out.

One of the suggestions, I tried, was that I have created a hidden parameter and set it to a field on the main dataset in default values tab. It throws error if the report is empty and report is generated if there is some data to show in report designer. However, when I schedule this report, it asks me to provide default value and I cannot finish scheduler wizard without providing it. If I provide any default value, the empty reports are still sent. But unlike this, an error is supposed to be thrown for empty reports and they should not be sent.

has anyone used this method before? if so, could you tell me what I am missing here please?

I was just wondering whether you guys have an ideal solution for this issue.

Many thanks for your help.

Regards

Answer 1

Here is my workaround: Retrieve your data via a stored procedure and put the following code in the end

IF @@ROWCOUNT = 0 RAISERROR('No data', 16, 1)

Check Russell Christopher's article and comments for more details. What surprise me is it has been 6 years and MS just can't come out a solution for this :3

Answer 2

Something I have found to work that is very easy to implement is to simply include an extra field in your query that divides by the row count. If the count is zero then you get a divide by zero error and the email is not sent; if the count is at least one then the report runs fine and the email goes out.

SELECT 
  [table].[id]
  ,(1/COUNT(id))
FROM [table]
WHERE [table].[id] > @Parameter

One note about this technique is that it does add some extra overhead so it may not be appropriate if you expect the possibility of very large data sets.