Discrete Distribution with SSRS Data-driven Subscriptions

Have you ever needed to send a data set to an individual when certain conditions were met? Have you ever needed to slice up a data set for different users and send their individual reports to them on a schedule? Have you ever needed to do either or both of these in multiple formats such as Excel, csv, pdf, or embedded in an email body?

SSRS has a very useful feature called ‘Data-driven Subscriptions’, and it’s meant for exactly these situations. It’s also very easy to set up!

Here is an example report. It has start and end date parameters, and a ‘Name’ parameter that defaults to ‘All’.

figure 1 - XCOM Report
Let’s say that we want this report to be sent automatically to each person with only that individual’s data. And also, it should show data only for the current month. This is where the data-driven subscriptions really shine.

Step one is to create a query that returns execution parameters for the desired set of reports. Each row will have the parameters needed for a single report; in this case a name, start and end dates, and an email address. SSRS will execute one report for each row in this set.

declare @firstDayOfMonth date = convert(date, getdate() - (datepart(day, getdate()) - 1)),
	@lastDayOfMonth date = convert(date, dateadd(month, 1, getdate() - (datepart(day, getdate()) - 1)) - 1);

select	distinct
	@firstDayOfMonth as FirstDayOfMonth,
	@lastDayOfMonth as LastDayOfMonth,
	mr.SoldierName + '@x.com' as Email
from	xc.MissionReport mr
where	mr.MissionDate between @firstDayOfMonth and @lastDayOfMonth;

figure 2.5 - XCOM Report Query
If an individual does not have a row, then they will not receive a report.

Now that we have our query, we can create a new data-driven subscription in SSRS. On the report’s management screen go to ‘Subscriptions’ and select ‘New Data-driven Subscription’.

figure 3 - Subscription screen
Choose your delivery method. Here I chose email:
figure 4 - Subscription screen step 1
Next we choose a data source. This is the source where the query that we created above will run.

On the next screen we will simply paste our query into the box.
figure 3.5 - Subscription screen
The next screen is where the magic starts. Here we can map columns from our query in step 3 to report execution parameters. You’ll do this by selecting ‘Get the value from the database’ for the options that you are using the query to determine. Notice the list gets populated with the columns from our query.
figure 6 - subscription screen step 4
The next screen lets us choose the report data parameters. Again, you will use the values determined by your query, or you can specify a static value, or use the default value (if your report allows a default).
figure 7 subscription screen step 5
Lastly you’ll set up the schedule. When the schedule executes, each individual will now get a report emailed to them with only their data for the current month.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s