Stop Blank Reports from Sending to SSRS 2008 R2 Data Subscription

Question

I am planning some reports using a data-based subscription in SSRS 2008 R2.

Regardless of whether the generated report is empty or not, reports are still sent to the recipients. This has been a common problem for a long time, and to be honest, the suggestions I saw on the forums didn’t quite work.

One of the suggestions I tried was that I created a hidden parameter and set it in a field on the main data set on the default values ​​tab. It throws an error if the report is empty and a report is created if there is data in the report designer. However, when I plan this report, it asks me to provide a default value, and I cannot finish the scheduler wizard without providing it. If I specify any default value, empty reports will still be sent. But in contrast, it is assumed that the error will be sent for empty reports and should not be sent.

Has anyone used this method before? If so, could you tell me what I am missing here?

I was just wondering if you have the perfect solution for this problem.

Many thanks for your help.

Hello

Answer 1

Here is my workaround: extract your data through a stored procedure and put the following code at the end

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

For more information, check out Russell Christopher's article and comments . What surprises me is for 6 years now, and MS simply cannot find a solution for this: 3

Answer 2

Many ways to achieve this. Based on the query results, it’s easy to change the behavior of the subscription. Just query your database to find out the situation (for example, in the process log - or just return the status from the procedure that you should run anyway) and use the if statement.

eg:.

 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' 

Answer 3

Please use as below script to stop empty 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 

Answer 4

Something that I found to work, which is very easy to implement, is simply to add an additional field to the request that divides by the number of lines. If the counter is zero, you get a division by zero error, and the letter is not sent; if there is at least one counter, the report works fine and the email goes out.

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

One note about this method is that it adds extra additional overhead, so it may be unacceptable if you expect the possibility of very large data sets.