tim laqua dot com Thoughts and Code from Tim Laqua

Pattern for Conditionally Sending SSRS Subscriptions

This is a pretty common request - send the report if there's an issue, don't send it if there's not an issue. This is a simple pattern for doing that using Data Driven Subscriptions. Here, we're not using the data driven subscription to dynamically populate recipient and parameter information, we're just using it to suppress the sending of the report if the report has no value.

To do this, we only need one thing - a query that returns one row if the report should be sent and zero rows if the report should not be sent. Here is an example - Let's say I want to send a report out if a JDE Address Book record with an AT1 of 'DC' has been updated after a certain julian date.

First, write a query to return rows meeting that criteria:

SELECT *
FROM LIB.F0101
WHERE ABAT1 = 'DC'
	AND ABUPMJ >= 111292

That's great, but it can return multiple rows... So just select the first record (because all we care about is if we have one or more than one - exactly how many is irrelevant). We also don't care what the column values are, so just select some arbitrary value.

SELECT TOP 1 1 AS DoStuff
FROM LIB.F0101
WHERE ABAT1 = 'DC'
	AND ABUPMJ >= 111292

So now we have what we need in SQL - let me add one final note on this for those DB2 users out there. DB2 doesn't understand "TOP" so we have to use their syntax, FETCH FIRST (and likewise for MySQL, use LIMIT).

SELECT 1 AS DoStuff
FROM LIB.F0101
WHERE ABAT1 = 'DC'
	AND ABUPMJ >= 111292
FETCH FIRST 1 ROWS ONLY

Now, you're ready to create your subscription.

Go in to your report Subscriptions and click on the New Data Driven Subscription button

Enter meaningful name and select Shared Data Source

* If you don't use shared data sources - you should

Select Shared Data Source

* I really hope you don't have a shared data source called "local" - this is just an example

Enter query we developed above

Enter the usual options - all static values

Enter parameters - usually just "Use Default"

* This is one drawback to the pattern, you have to type in your parameter(s) - no dropdowns here if you're not using the default

Select your schedule type

And finally the usual timed schedule options

The schedule you specify will be both the time when the above query is executed to see if the report should be sent as well as the time the report will be sent out (assuming it should be sent out). As far as who the report is sent to, 99% of the time this should be a distribution list that can be managed elsewhere. You don't want to be wading through SSRS to update the list of subscribed users (unless you're the only subscribed user... then go ahead).