SSRS- How to create Email subscription for a Report

1.     Background

The purpose of this article is to provide a way of implementing Email subscription for a report using SQL server reporting services. Email Subscription of reports provides a way of delivering the reports in Email to individual users or groups in the required format and at a specific point of time.

2.     What is Subscription?

A subscription is a standing request to deliver a report at a specific time or in response to an event, and then to have that report presented in a way that you define. Subscriptions provide an alternative to running a report on demand. Subscriptions can be used to schedule and automate the delivery of a report.

3.     Delivery Extensions provided by SSRS

Delivery extensions are used to determine how to deliver a report and in which format. SQL Server Reporting services support two types of delivery extensions.

·         File Share Delivery : To deliver report to a folder

·         Email Delivery: To deliver a report to email recipient

4.     Pre-requisite for Email Subscription    

·         SQL Server Agent service should run.

·         Email address that is going to use as Sender address should have send mail permission on the SMTP server. Go to Reporting services configuration Manager è Email Settingsè Check whether correct Email Address and SMTP Server are mentioned or not:

·         In Reporting service configuration Manager, go to Execution Account tab and check whether unattended account mentioned is your domain account that is used for Report Data source connection, Report processing and sending emails:

·         Credential required connecting to Data source should store securely in the report server.

Below is the screenshot from report server showing that we are storing credential in the report server itself:

·         Run the report from Report Manager to ensure Report is working fine. Below is the screenshot of report preview showing report is working fine:

5.     Step by Step procedure to implement Email subscription:

                    I. Go to Report Manager URL e.g http://servername/reports  è go to folder which is having reports.

                  II. Below is the screenshot from my Report manager URL showing that there is a report named “ProductSalesReport” under MyReports folder.

                III.   Before creating subscription, run the report to ensure that Report is working fine.

                IV.   Go to “ProductSalesReport” report and click on down arrow and select Subscribe

It will open the Report subscription form. Below is the screenshot of Report Subscription form:

                  V. Fill the required details for Email Subscription and Click Ok.

                VI.  We are going for email subscription with below details:

              VII. Click Ok to create the subscription
            VIII.  Go to Report Manager home page and click on “My Subscription”. It will show the list
              of  all the subscription created by you.

 

                IX. For Demo purpose, I have schedule the subscription per minutes. We can set the subscription as per the business requirement.

                  X. Click on “My Subscription” to check the status of the subscription

 

                XI. The above screenshot is showing Report is delivered to vishaljharwade@sqlcircuit.com

              XII. Open the mail and check whether report is delivered to mail or not.

6.     Conclusion

By using above steps, we can create email subscription for a report and we can deliver the report to an individual as well as group at a specific point of time in a required format.

----------------------------------------------------End of Document---------------------------------------------------