SSRS Subscriptions Setups for Email

Setting up Email Subscriptions in SSRS

One of many things that I liked very much for SSRS over Crystal Reports is “Report Subscription”. At work we use Crystal Reports as our primary reporting system. And to provide scheduled report deliveries via email we have designed our own little .NET application that allows users to create schedule and manage automated report deliveries via email. But if it was SSRS that whole thing can be just removed and we will not need to worry about that application anymore (and this was one of my WIIFM presentation when I was showing why we should at least consider working with SSRS) in addition to better management of reports. I think both Crystal Reports and SSRS have pros and cons, but I am not going to discuss them here because I use both and I am OK with both of them.

This post is about Configuring SSRS to setup email subscription for Reports. This will allow uses (or DBAs) to schedule automated report delivery via email. Before anyone ask, let me say that for this demo I've used SQL Server 2012 dev edition but this feature is not changed from previous versions.

First step to configure SSRS to use SMTP server. And just like almost all MSFT products you cant use basic authentication for SMTP. Which simply means you can’t use external SMTP servers (say Gmail or Yahoo). But fear not … you can use IIS and create Virtual SMTP server for the purpose.

So once you have SMTP server address, all you need is to feed it into “Email Settings” section of “Reporting Services Configuration Manager”. In my case I had a VM as SMTP Server called “SMTPServer”, alternatively you can feed IP address as well.

Next step is to setup report subscription using Report Manager for SSRS. For those who are not familiar with it, Report Manager is an ASP.NET application for managing and administering SSRS. To create subscription for any report just select any report. For this post, I choose a report I had previously created. Once “subscribe” option is selected it will open up a page showing “Report Deliver Options”.

In this page if you don't see “Email” option in delivery method then it means that you have not setup configured SMTP in Reporting Service Configuration Manager. Once “Email” option is selected it will show more options for adding usual items like To, CC, BCC, Subject, Body etc. In Subject you can select in which format you want report to be delivered (if Include Report checkbox is selected) say in PDF, DOC, TIFF etc. You can also choose to include link for report as well.

Next option is to set up schedule. If you have setup any schedule task in SQL Server Agent then you will see that this schedule setup is more like ASP.NET avatar of same scheduler. For this test I set it up to run at every morning 7A.M.

Once that is done you should see that, report gets delivered at 7AM in the morning. But what if you want to manually run that report at some different time ? (what ? you thought that I waited till 7 to make sure that my report get delivered at time ?) There is no direct way to run those reports “on demand” from SSRS administration interface, but you can actually run them from SQL Server Management Studio !! Actually when we create a report delivery schedule it creates a scheduled task in SQL Server Agent and yes, as you can guess timely execution of report is handled by SQL Agent (and one of the reason why we don’t have this feature in SQL Server Express, because it doesn't have SQL Agent). Anyways, if you log to SQL Server instance which holds report server database, and expand SQL Server Agent jobs you will see a job (or jobs) with cryptic GUID names, these are the ones created by report manager for report subscription. Unfortunately there is no visual indication of which job is for which subscription so you will have kind of go through each job and try to guess that job based on its schedule. In my case it was just a one job so it was easy to find Smile. Then, right click on that job and click on “start job at step” to run the job and you should see that report delivered to your mailbox.