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’.
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 mr.SoldierName, @firstDayOfMonth as FirstDayOfMonth, @lastDayOfMonth as LastDayOfMonth, mr.SoldierName + '@x.com' as Email from xc.MissionReport mr where mr.MissionDate between @firstDayOfMonth and @lastDayOfMonth;
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’.
On the next screen we will simply paste our query into the box.
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.
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).
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.