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

Hottentot

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

Reprise

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.

Acroterion

Answer 3

Many ways to accomplish this. It is easy to change the behavior of the subscription based on the results of a query. Just query your database to figure out the situation (e.g. in a process log -- or just return the status from a procedure you have to run anyway), and use an if statement.

E.g.:

DECLARE @SomeVariable INT = 0; -- 0 means good

EXEC @SomeVariable = reports.sp_some_proc; -- Returns 1 if something is bad

IF @NoData = 0 -- everything is fine send report

SELECT 
    mail_to = 'email1; email2; email3,... important people.....',
    mail_subject = 'Whatever subject',
    mail_comment = 'Whatever comment',
    include_report = 'True'

ELSE -- the world is a dark place, variable is probably 1 or something. bad...

SELECT 
    mail_to = 'someone you don''t like',
    mail_subject = 'something is bad' + LEFT(CONVERT(VARCHAR(25), GETDATE(), 20),16),
    mail_comment = 'like i said, something is bad',
    include_report = 'False'

Monied

Answer 4

Please use like below script to stop blank scheduler report in SSRS :

BEGIN


Select @PN=Count(ParameterName) from setup   where 

@SAPProcessedDate<>parametervalue   and parametername='CommunicatorLastRun'

IF @PN = 0

    Select 1/0

Else 

Select 'Communicator Not Running' AS ParameterName


END

Shrewd

Answer 5

Necromancing here, but Wayne's answer was so helpful in pointing me in the right direction that I had to add this tweak, which works to only run the report when the daily data has been refreshed (the data refresh process is not in my control but I can read its success/failure status).

DECLARE @processSuccess TINYINT = 0;
SET @processSuccess = 
(
    SELECT TOP 1 1
    FROM processesTrackerTable
    WHERE ID = 25
    AND State = 'Success'
    AND CAST(RunEndDtm AS DATE) = CAST(GETDATE() AS DATE)
    ORDER BY RunID DESC
 )

And then adding this line to the WHERE clause of the regular query:

AND 1 = 1/COALESCE(@processSuccess, 0)

This forces the report failure directly from the SSRS query so that the users don't get either stale or blank reports.

Yoder