Tag Archives: SQL Server Reporting Services

Data Driven Subscription On A Budget, Part 2

Data Driven Subscriptions On A Budget – Part 2

Yes, this is Part 2, you can find Part 1 here to get background information.

This blog will talk about Point 3 – Distribute a report to a fluctuating list of subscribers.

Distribute a report to a (fluctuating) list of subscribers

When using email as your method of delivery for a Data Driven Subscription, best practice is to use metadata to drive that process. Usually a simple table that contains the email address of the recipient and the report name does the trick. This part of the process is no different if you don’t have Data Driven subscriptions. I usually create a table similar to this:

CREATE TABLE dbo.SSRSEmailSubscribers
(
EmailAddress varchar(128) NOT NULL
,ReportName varchar(128) NOT NULL
)

Let’s say I have a sales report that needs to go out on a daily basis and the standard format for this report is Excel. Because we don’t have data driven subscriptions, we can’t just query the table and use the resulting list to email the report. Instead we need to create a File Share subscription that generates this report and saves it to a file share. From there we can “pick up” the newly generated Excel file and email it to the recipients.

  1. 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.)
  2. 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 in the next step.
  3. Create a new SQL Agent job and add a step for Transact SQL script. In this new step you need to execute the SQL Agent job that you created back in step 1:

exec msdb.dbo.sp_start_job N'B514C05F-07D5-4C0B-9600-666E9980C7C3'

    where B514C05F-07D5-4C0B-9600-666E9980C7C3 is the GUID from the job that SSRS created.
  1. Next you will need to add a new job step for PowerShell. In this newly created step write your PowerShell script to get the file that was generated (as a result of executing the previous step) and retrieve your list of email addresses. Once you have this information you can send the email with the report attached. There are several ways you can do this, but I chose to use PowerShell. Since PowerShell requires a double quoted semicolon (;) delimited list of email addresses when using SMTP, I wrote my SQL query to return a double quoted semicolon (;) delimited list of the email addresses. You could have just as easily used PowerShell command-lets to format your list. Here’s my PowerShell script:

cd c:

$FilePath = "c:\temp\"
$smtpServer = "10.0.0.4"
$smtpFrom = noreply@email.com

$AddressQuery = "DECLARE @List varchar(MAX);"
$AddressQuery = $AddressQuery + "SELECT @List = COALESCE(@List + '"";""', '') + EmailAddress "

$AddressQuery = $AddressQuery + "FROM dbo.SSRSEmailSubscribers "
$AddressQuery = $AddressQuery + "WHERE ReportName = 'Daily Sales Report'; "
$AddressQuery = $AddressQuery + "SELECT '""' + @List + '""';"
Invoke-Sqlcmd -Query $AddressQuery -ServerInstance "MyServer" -Database "MyDatabase" -Variable $smtpTo

$messageSubject = "Daily Sales Report was executed"
$latest = Get-ChildItem -Path $FilePath -Filter "*.xlsx" | Sort-Object CreationTime -Descending | Select-Object -First 1

$FullFileName = $FilePath + $latest
$body = "Attached is the Daily Sales Report"
send-mailmessage -from $smtpFrom -to $smtpTo -subject $messageSubject -body $body -smtpServer $smtpServer -Attachments $FullFileName

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 that distributes a report to a fluctuating list of email addresses.

Data Driven Subscriptions On A Budget

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

  1. Trigger the execution of a report based on data
  2. Provide parameters to filter the report data at run time
  3. Distribute a report to a fluctuating list of subscribers
  4. 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:

  1. 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.)
  2. 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.
  3. 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”.