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)