How to prevent empty reports from being sent as part of a SQL Reporting Services subscription


It’s pretty common to set up report subscriptions which are based on queries that can filter data based on sliding date ranges. So, at times you may end up with a report which does not contain any data. Do you want to send an essentially empty report out to your users? Maybe, but often the answer is no.


There are two ways to deal with this scenario, and one is a pretty big kludge. First, the hack (good for standard subscriptions):


– Create the subscription, and base it on a schedule which has expired (therefore the subscription will not execute on its own).


– Write custom code which fires the same query in your report (to see if any results are returned). If there are results, have your custom code execute the FireEvent() method of the RS web service to execute the schedule your subscription depends on. You set this code up to called from a SQLAgent job or make it into a service with its own “scheduling” capability. This technique doesn’t leverage the built-in goodness around schedules that RS automatically gives you, which is a bummer.


The other way to approach this situation is much cleaner, and works for data driven subscriptions. When configuring a data driven subscription, you must provide a query which returns subscriber data: Most of the time this query simply returns rows from a table which lists your data driven subscription users and their preferences around delivery and parameter values for the report in question. Each row of data returned equals one report we’ll deliver as part of the subscription. Just modify this query so that it ALSO filters the result based on whether or not the report itself will return rows. For example:


SELECT * from DataDrivenSubscriptionInfoTable
WHERE EXISTS(SELECT SomeField FROM TheDataSourceTable WHERE DateField Between DateAdd(dd,-3,GetDate()) and GetDate())


If you provide this query to the wizard, it will only return subscribers when there is data you wish to report on (records in the TheDataSourceTable table that have a date within the last 3 days)


 

Comments (38)

  1. Anri says:

    There is another way to stop empty report from being sent out. Create a hidden parameter and set up its default value to First(Fields!SomeFieldFromMainDataset.Value). This will artificially error out the report if there are no rows in “MainDataset”. In case of error, subscription executes with “Parameter Validation Failed … “ LastStatus and email is not sent.

    Although it’s not the most elegant approach in the world, “This technique” DOES “leverage the built-in goodness around schedules that RS automatically gives you” and does not require EE. 
    
  2. VBPro says:

    I tried this technique and received an error when I tried to preview my report.  

    Created new report parameter: SuppressEmptyReportEmail.

    Set default value to: =First(Fields!ProducerName.Value, "tsp_AIFACE_ProducerTransactionsAged")

    Saved the reportparameters.

    then clicked preview report and

    received the following errors.

    [rsFieldInReportParameterExpression] A Value expression used for the report parameter ‘SuppressEmptyReportEmail’ refers to a field.  Fields cannot be used in report parameter expressions.

    [rsAggregateInReportParameterExpression] A Value expression used for the report parameter ‘SuppressEmptyReportEmail’ includes an aggregate function.  Aggregate functions cannot be used in report parameter expressions.

    [rsFieldInReportParameterExpression] A Value expression used for the report parameter ‘SuppressEmptyReportEmail’ refers to a field.  Fields cannot be used in report parameter expressions.

    Build complete — 3 errors, 0 warnings

    I have the same problem and I do not have data-driven subscriptions available since we are not using SQL Enterprise version.

  3. Sudhir Sharma says:

    I am also trying to do that through a hidden parameter. same error. Field value can not be used a parameter’s value.

    did you get any solution to this one?

    thanks in advance

  4. Andy Hoffman says:

    To avoid the error do this:

     <ReportParameters>

       <ReportParameter Name="SuppressEmptyReportEmail">

         <DataType>String</DataType>

         <Hidden>true</Hidden>

         <DefaultValue>

           <DataSetReference>

             <DataSetName>MainDataSet</DataSetName>

             <ValueField>SomeFieldFromMainDataset</ValueField>

             <LabelField>SomeFieldFromMainDataset</LabelField>

           </DataSetReference>

         </DefaultValue>

       </ReportParameter>

     </ReportParameters>

  5. Jason Josephson says:

    I found another way to prevent a report from going out if there is no data present.  I started off by creating a subscription that would run one time only.  Then, in Server Management Studio, if you look at the list of jobs, there will be one (or more, if you have a lot of SRS subscriptions setup) with a GUID.  The first step in the job is an EXEC command that will run the SRS subscription.

    I took this job and added a step ahead of the SRS step.  This step does a SELECT 1/(SELECT COUNT(*) FROM MyView).  If this step fails (because there is no data in the view) the job exits reporting success, and if it succeeds, the SRS subscription is run.

    So far, in testing, this appears to work OK.  Has anyone else tried this?  It’s kind of a kludge, but it seems far simpler than the other methods I’ve seen so far.

  6. brinda.shree says:

    I am also trying for the same. But when we add an user defined step ahead of the SRS step, incase we make any changes to the subscription via the Report Manager the Step 1 is lost and ultimately the report is still delivered as blank.

    Any other options!!

  7. brinda.shree says:

    I tried to set the query option inside the report as dataset to another table object, when it encounters an error while rendering then the mail is not delivered.

    As of now ok,need to think about some other workaround also.

  8. Simon says:

    the ‘job step divide by zero error’ -> fail next job > worked like a charm. Luckily I dont have that many reports that require it, but for a few reports only this is by far the best kludge I found. Thanks dude.

  9. Matthew Jackson says:

    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

  10. Dilip says:

    In my case, I have a main report incorporating 7 sub reports within. I detect error/Null in case of each sub reports and hide those tables and sub reports. But, how to stop sending the mail in case of all my sub report fails. Now the empty main report is being sent to the email. how to stop this. Any idea?  

  11. Peter says:

    I use a RAISEERROR statement in my SQL script or procedure.

    IF NOT EXISTS ( SELECT * FROM ….)

    RAISEERROR(‘no records found,’16,1

    ELSE

    SELECT * FROM ….

    Not pretty, but it works.

  12. Sheetal says:

    Raising the error from the procedure works but it throws an error "No Records Found" in the log but we want the same status in "LastStatus" column of the subscription table. Or can we update any such user defined messages to this LastStatus column? We basically want to use this column to show the status of all the subscriptions on a web page as a summary.

  13. rma says:

    I got this working:

    1- I used the "If Exists … raise Error" method, but didn’t want to have my query executed twice, so used Top 1.

    2- I got errors when trying to set up the subscription, so I also had to add Declare & Set parameter statements to force the report to have data first.

    3- So, with the report in a state where it was forced to have data, I set up my subscription.

    4- Once the subscription was set, I commented out the Declare & Set statements so the report would fail.

    5- Every time you need to change a subscription, you’ll have to make sure the report has data by deploying a version with parameters, then redeploying with them commented out.

    Here’s my query with the Declare & Set statements commented out.  I Delete/replace the dashes to uncomment/comment these lines as needed.

    –declare @MyDate as date

    –set @MyDate=’2010-1-22′

    if not exists

    (

    select top 1 * from

    [FSCC-ATM].dbo.FSCCTerminalAdjustemnts

    where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, FSCCTerminalAdjustemnts.ImportDateTime))) = @MyDate

    )

    RAISERROR (‘No Records Found’,16,1)

    else

    select * from

    [FSCC-ATM].dbo.FSCCTerminalAdjustemnts

    where CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, FSCCTerminalAdjustemnts.ImportDateTime))) = @MyDate

  14. rma says:

    One drawback- if a user tries to run the report from Report Manager on a day with no data, they won’t be able to run it for a prior date.  It will get errors.  So, I hide this version of the report.  I created another version without the RaisError statement that users can run on demand.

  15. Moinu says:

    Just a query about this

    "I took this job and added a step ahead of the SRS step.  This step does a SELECT 1/(SELECT COUNT(*) FROM MyView).  If this step fails (because there is no data in the view) the job exits reporting success, and if it succeeds, the SRS subscription is run"

    Could you please let me know what is MyView here.

    Is it a table in Report server db.. Please advise me. I am new to SSRS.

  16. Erich says:

    We addressed something similar to this by using a subscription based on a snapshot refresh. Basically the same concept of checking for data first then using the update or lack of an update to determine whether there should be a report distributed.

  17. Zafar Khan says:

    Reply to Moinu: MyView is the table or view which you are using to get the data

  18. Julio Valencia says:

    I have another issue, is the same report with two subscriptions:

    1. A subscription with a txt file attached to the email. (Always has info)

    2. A subscription with a excel file attached to the email. (this is the one with issues)

    Both subscriptions run normally but the second one sometimes the excel file is empty.

    any Ideas why is this happening.  Remember both subscriptions are from the same report and the first one arrives with information, the second is the one that sometimes is empty.

  19. Leon says:

    Thanks a lot for the suggestion.

    This is was very simple way to solve the problem

    Example inside the procedure:

    if @Count > 0

    select *

    from #SSRS_COI_DQ_Report_DETAILS

    order by [filename],source_code

    else

    RAISERROR('Because there was no data the report was not sent out.',16,1)

  20. no luck says:

    I did try the parameter approach.. but no luck, has any of you managed to achieve this on 2008 r2?

  21. I came up with a workaround while using SSRS 2008 R2:

    Create an additional column (calculated or otherwise) on the main dataset where the value returned is always the same (let say it is a calculated column where the formula is ="Yes").

    Then create an additional paramater that is mandatory.  This paramater will get its Avaialble values from the main dataset, using this new column as the data value.  The default value for this paramater is going to be the same as the value of this calculated column (i.e. the formula is ="Yes").

    Now you won't be able to select a value for this new mandatory parameter when the main dataset contains no rows!  The user experience isn't optimal when interacting with this report; but it works well for the subscription scenario.  Its mentioned previously, but I suggest that this report is not utilized for end-user interaction.

  22. Rob says:

    I created a Microsoft Connect request for this: connect.microsoft.com/…/768029

  23. Pathetic Microsoft says:

    Why didn't Microsoft just add a checkbox to the subscription to indicate whether or not to send the report if it contains no data?

  24. Kevin Fairchild says:

    They did add the option — it's called "upgrade your edition of SQL Server".

  25. Bradley Teague says:

    I'm using the "set Include Report to invalid value (e.g. 0) to effectively cause the subscription to throw an error if no data is available based on a query that counts the rows returned" method.

  26. Gerhard Bosma says:

    @Kevin Fairchild: could you please indicate how to do this in the new (I presume 2012) SQL Server?

  27. hangeo says:

    OMG, you guys are doing things way too complicated.

    here is my approuch

    1. data -driven subscription

    2. value of send out email is a case statement based on same query of the report. Do count of whatever row in the report, if count > 0, return all values of send out emails, if count = 0, return NOTHING

    3. use value from step 2 set as email value for data driven subscription

    The idea is, if rowcount > 0, send all value email with report, if rowcount =0, send email to nowhere with report. DONE. GO HOME!

  28. hangeo says:

    For those whose SSRS subscription will error out on empty email and subscription never fire up again, just put fake email address instead of empty string to fool SSRS. Cool?

  29. dnaq says:

    @hangeo  Thanks that worked for me and it isn't that difficult to setup.  I did run into an issue if I had multiple rows returned I would get multiple emails.  I just did a top 1 and that fixed my issue.

  30. @dnaq ,@hangeo:Guys i am a naive user of SSRS..But got stuck with similar thing..Dont want to send empty report..I found u already resolved can u eloborate how u did? where to write code and how? You can send me mail at "kalpdus123@gmail.com" ..Thanks in advance

  31. hangeo says:

    Below is the code Kalpesh D.

    Select case when count(*) >= 1 then 'youremail@yahoo.com' when count(*) < 1  then 'dummy@yahoo.com' end as email

    from yourtable

  32. Barbaros Saglamtimur says:

    Anri's solution works for me but you have to apply Andy Hoffman's tip as-well. Otherwise  you get " Fields cannot be used in report parameter expressions.". Other methods may work too, but my subscribers listed on a SQL server different then my data source. Actually my data source is an XML data source which I can not execute any sql query for count / if exists approaches.

  33. Vural Tuysuz says:

    Chaps,

    There is also another simple method. (thnx to Jason Josephson for the idea)

    You could create two datasets for the same report, and first dataset has the below logic:

    Dataset1

    SELECT 1/count(VT.YourSQLQuery.Field1) FROM

    (YourSQLQuery

    )  

    VT

    Dataset2

    YourSQLQuery

    Then make sure the Field1 from dataset1 is in the report (somewhere hidden)

    So when you run the report if datset failed the whole report would fail therefore your email will not be sent to the users.

    The benefit of this method is you do not have to worry about creating extra steps or either procedures since all config is in the same report.

    Hope this helps,

    Cheers,

    VT

  34. Keith Berling says:

    This very simple addition to my proc raised an error if no data was returned:

    IF @@ROWCOUNT = 0

     RAISERROR ('no data', 16, 1)

  35. Jamie Nelson says:

    Vural's Solution worked well for me.  I placed the field in the header of the report.  You need to make it an aggregate such as =MAX(Fields!ID.Value, "dataset2").

    Thank you

  36. BL139 says:

    Christopher Scolt is a beast. Solution worked perfectly without messing up a user report. I named my custom parameter 'ReportIsBlankForTheseInputs' so that when a user does try to run a report that will turn up blank, the parameter name will give a clue as to what happened.

  37. LeaLubi says:

    Thanks Christopher Scolt 🙂 you helped me big time! cheers

  38. Alan Handel says:

    Christopher's method works great.

    Added column in proc returning fixed value.  Set parameter in report with avail value by query to the dataset.  Default value set to same column.

    If no records found when subscription runs, errors as follows:

    "Failure sending mail: Default value or value provided for the report parameter 'rpSendEmail' is not a valid value.Mail will not be resent."