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.
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.