Tag Archives: SSRS

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”.

Custom Reports for SSMS 2012

I presented on this topic at the TriadSQL User’s Group meeting on 3/26/13.  I promised I would post my code/solutions, so here it is.  For those that were not able to attend the meeting, here is a brief description of what I am posting.

I am lucky enough to be using SQL Server 2012 in a production environment.  Not only do I get to use the database engine, but I also get to use Integration Services (SSIS),  Analysis Services (SSAS) and Reporting Services (SSRS).

This post is mostly about SSIS 2012 and the aches and pains it relieves compared to previous versions.

One of the great things about SSIS 2012, is the new project deployment model.  Gone is the day when you had to search file servers high and low to figure out where all your SSIS packages lived (if you used file deployment, which I did).   Now all your packages are located in one place.

Another great thing about SSIS 2012 is that it has logging built in and the entries all go to one location, so gone is the day when a rogue developer would send his logging to some obscure table in some obscure database on some obscure server never to be seen by anybody ever again.

Where is this magical place you ask?  The Integration Services Catalog (ISC), that’s where.  The ISC stores your packages along with the metrics for running those packages and sits atop a nice little database called SSISDB.

The ICS also comes with some canned reports for looking at those metrics.  The stepping off point is a report called All Executions.  It can be viewed by right clicking on your project then selecting Reports | Standard Reports | All Executions.  This report is very cool, it provides basic execution information, e.g., how many times it has succeeded, failed, etc.  It also provides start times, end times and duration for each package in the project.  As I mentioned, it is the stepping off point, it has a section with links to three other reports, Overview, All Messages & Execution Performance.

The Overview report is nice; it shows all the parameter values that were used when the package was executed and some detailed execution information.  Definitely comes in handy when troubleshooting.

The All Messages report, is just as it sounds, it shows all the messages that were logged during the execution of the package.  Again, good information for troubleshooting.

My favorite is the Execution Performance report.  Prior to my first glimpse of this report, I had visions of graphs and KPIs that would rival dancing sugar-plums.  Needless to say, I was a little disappointed.  Don’t get me wrong, it has a graph on it, but only the last 10 executions are included.  There is also a listing of the last 10 execution durations.  The thing that gets me is that right above that listing is a three month average and standard deviation.  Really, they couldn’t have included those months of data in the graph?  But I digress.  So I said to myself, “I need more data.”  That’s when the light bulb went off and I decided to get more data all on my own.

The SSISDB is easily accessible and easy enough of follow, especially when using the catalog schema and its views.  I created some SSRS reports in SQL Server Data Tools and was happy as a clam, until I discovered that the SSMS custom reports don’t support images, drill-down or basically any other kind of interactive-ness.  That’s okay, I published the cool reports for the managers to see via the SSRS site and I created a stripped down copy of the reports that I can use within SSMS.

I end up maintaining two code bases, which is kind of a headache, but I work in a small shop where I am the only SQL database related employee, so it’s not that big of a headache.  If you work in a larger shop, you may want to figure out some cool PowerShell script that will sync them up.

I hope you find these reports useful and possibly a jumping off point for you to start your own collection of custom reports.  I am only providing some of the very basic reports that I created, I wanted to leave some of the creativity to you  😉