Data Driven subscriptions in SQL Server Reporting Services (SSRS) is only available if you have the Enterprise or BI Editions for 2012 & 2014, Enterprise or Data Center Editions for 2008R2 or Enterprise for 2008. But what happens when the money is not in the budget for those versions? Can you still get Data Driven subscriptions? The answer is You Bet!
I have worked in large shops in the past where purchasing the Enterprise Edition of SQL Server was never an issue, in fact, it was the standard flavor of SQL Server. But when I switched to a smaller shop, where cost was an issue, I had to say good-bye to all those lovely Enterprise features that I have come to know and love. As the proverb goes, “Necessity is the mother of invention”. So when I was asked to essentially create a data driven subscription in Reporting Services, I paused ever so slightly, then said, “Yeah, I can do that”.
There are a few things you can do with a data driven subscription in SSRS
- Trigger the execution of a report based on data
- Provide parameters to filter the report data at run time
- Distribute a report to a fluctuating list of subscribers
- Vary the output format and delivery options.
In this post I will address point 1 only, hopefully at some point I will get around to creating a post about points 2, 3 & 4, but for now, it’s just 1.
Trigger the Execution of a report
Let’s say I have a sales report that needs to go out on a daily basis. This report contains sales for the previous day. But what happens when there are no sales? Our report shows up with no data on it. Now we, as data people, completely understand why this happens, but those in the C-Suite don’t always understand and they think the report is “broken”. This initiates a call to the help desk saying simply, “The Daily Sales Report is broken”. We freak out, thinking, “Great, who promoted what?” We instantly go into trouble shooting mode. But after running the report, seeing no data and running the underlying query, we now understand. The report is not “broken”, there were just no sales. I don’t know about anybody else, but I don’t like those in the C-Suite thinking that we in the dungeon are idiots. So instead of sending them a report with no data, we need to send them an email to let them know there were no sales the previous day. Problem solved, crisis averted, get back to work. Oh but wait, I don’t have the appropriate edition of SQL Server, how the heck can I do this? In three easy steps, that’s how:
- Create a subscription to your Daily Sales Report in Report Manager, schedule it for a one time execution at a time just a few minutes in the future, and remember the execution time. (This creates the SQL Agent job in SQL Server.)
- Take a look at your SQL Agent jobs in SQL Server. If you have never seen or noticed a job created by SSRS, then you will be wondering where your job is because SSRS does not use human readable names for its jobs, it uses those pesky GUIDs as names, ugh! If your server has lots of jobs you may need to sort your jobs by Category to get all the “Report Server” jobs together. Find the job that executed at the time you scheduled (this is why you needed to remember the time!), this will be the job you will need to reference later.
- Create a new SQL Agent job and add a step for executing Transact SQL script. In this newly created step write a query that checks for sales from the previous day, if sales exist, then execute the job that was created by SSRS, otherwise send an email stating that there were no sales. There are several ways you can do either one of these things, but here’s my T-SQL script:
DECLARE @SalesTotal numeric(18,2)
, @Yesterday date
, @BodyMessage nvarchar(max)
SET @Yesterday = DATEADD(DAY, -1, GETDATE())
SELECT @BodyMessage = N'There were no sales for ' + CAST(@Yesterday AS varchar(10))
SELECT @SalesTotal = SUM(InvoicedAmount)
FROM dbo.Sales
WHERE SaleDate = @Yesterday
IF @SalesTotal > 0
BEGIN
EXEC msdb.dbo.sp_start_job @job_name = '4D3D4A1F-F007-4045-B5F6-3C86445D153B'
END
ELSE
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients = 'recipient@servername.com',
@subject = 'Daily Sales Report',
@body = @BodyMessage
END
Now schedule this newly created SQL Agent job for the time you need your Daily Sales Report to be executed and Wa-La, you now have a data driven subscription for your SSRS report.
There is one really big assumption here, since it’s a small shop, the SSRS instance and the instance where your data live are one in the same. This is easily adaptable if they are not on the same instance by creating a linked server, yes I feel dirty even suggesting it, but like I said, “Necessity is the mother of invention”.
SQLSwimmer 