remiWare

Report Scheduler for Crystal Reports, SSRS Reports and more.

  • Features
  • Download
  • Pricing
    • Renew Maintenance
  • Version History
  • Contact Us/Support
  • Become a Partner
  • Blog

Prevent Blank SSRS Subscription Reports From Being Sent

October 8, 2017 by remiware

One of the most common challenge that SSRS subscription users face is that their reports are sent out on schedule regardless of whether or not they contain data. As you are aware, SSRS reports are typically based on one or more datasets and sometimes these datasets can be empty. When using normal or data-driven subscriptions, the reports are executed and delivered even when the report is blank. This is undesirable as time is wasted by end-users who opens up these reports only to find that there is no data present.

How do you then make sure that the only reports coming out of your report distribution system to your recipients are the ones that contain data?

There are ways to achieve this in SSRS but they are fairly convoluted and likely to break if any changes are made to the original subscription.

There has to be a better way.

In remiCrystal, we have recently added the ability for the application to check if the report is blank before it is sent to its intended recipients.

And it couldn’t be easier.

To prevent an empty SSRS report from being sent, you simply check the box as shown below, specify the same database connection as that used by the report and then input an SQL query that mirrors the logic of the report. If no records are returned by the query, remiCrystal will know that the report will also be blank and should therefore not be run at all.

prevent SSRS reports from being sent

For example, say your report showed a list of daily orders and their respective invoice details. Your report’s main data set could have a query/stored procedure using the query like the one below:

SELECT * FROM [orders] INNER JOIN [invoices] ON [orders].[orderid] = [invoices].[orderid] WHERE [orders].[entrydate] = GetDate()

In remiCrystal, you can use the same query (or logic) to check if the report will have data. For efficiency and speed, remiCrystal only checks the first column of the first row to determine if the recordset has any data.

remiCrystal also allows the use of its numerous system variables as part of the query and therefore this is a perfect solution when using a data-driven task. For example, using the above query, we can extend it to run the check for different regions. The query would look like this:

SELECT * FROM [orders] INNER JOIN [invoices] ON [orders].[orderid] = [invoices].[orderid] WHERE [orders].[entrydate] = GetDate() AND [orders].[region] = ‘{rctx.GetExternalData(region)}’ 

This is one feature that has been requested by a lot of our SSRS users and we are very glad to be able to provide such an elegant solution. Please download the trial and let us know how it has helped your business.

To get started with a free 14-day trial, click here to download remiCrystal.

SaveSave

Filed Under: database, SSRS Tagged With: business intelligence, data-driven subscription, data-driven subscriptions, detect blank ssrs report, ignore blank ssrs report, only send ssrs reports with data, report subscription, reports, schedule ssrs report, schedule ssrs reports, SQL Server, sql server reporting services, ssrs, ssrs subscription

 remiCrystal is Reliable

remiCrystal utilizes the Windows Task Scheduling system for all its automation. This means that your reports will be delivered as long as Windows is running. No ifs or buts.

 remiCrystal is Easy To Use

With remiCrystal, you do not have to spend money on training your staff on how to use the software. We believe that if you can use MS Word, then you can use remiCrystal.

 remiCrystal is Cost-Effective

When you purchase remiCrystal, you will begin to realize the return on investment immediately. You will save time money and human resources. Automating your processes will reduce errors in your reporting immensely.

COPYRIGHT ©REMIWARE 2018