Data Driven Subscription using SSRS 2012

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
This article needs basic knowledge of SSRS and how to create report using SSRS because, we are going to learn something new to send or create report automatically and send it to recipient via email with relevant data, or what is called a Data Driven Subscription.

You can create normal subscription in every version of SSRS but the data driven subscription is part of the Enterprise edition.

Let go step by step to create Subscription.

1. To create subscription first open Report Manage using url : http:://servername/Reports

You will be in the SSRS web site from where you can run reports, create new reports, load Report builder, create subscription etc.

2. We will move to the Report folder from where report is located and open report property by right clicking on that.
Report Manage3. In the below screen, you can see the report properties like Snapshot, Cache, Subscription etc.

All these three features are important to the advanced user. We are not moving to that but giving you some brief introduction of those.

3.1. Snapshot is the feature to generate reports from that report image. Snapshot means "make an image of the report and store in database". It is very useful for those reports in which data is not frequently changed.

By this feature you can render the report faster. To get the feature up you have to select the option "Render report from snapshot" in Processing options.

3.2. Cache is the same as Snapshot except the storage is in memory, not in the database. The same configuration is available in the Processing option for Caching as well.

3.3 Subscription is different. It will send or create report automatically. This means SSRS create Job and schedule Report creation process and send it via email to the recipient.

If Mode of subscription is File structure other than email then create report file on specified location and format.

Report Property
General Subscription
4. Click on Data driven subscription to generate subscription. see image

Types of Subscription
SSRS makes it very easy to create the reports to be distributed depending on the data. In the first step specify the recipient data source to whom you want to send the report.

The data driven subscription gives you the chance to specify the command or query needed to extract the parameters that will be used in conjunction with the data to create the recipient.

e.g. If Cellular company sending out the latest bill to its users, then the Bill contains the details related to that user only. So, when the bill is for Patel, then the report needs to be sent to Patel@email.address. We can use the (subscription) query to retrieve that email address which is passed to the Bill report as a parameter.

Data Driven Subscription
In the second step select the type of delivery (email, file system etc)

5. Select data source for the report from the tree view.

Select Main Source
6. Write SQL Query to get recipient details and validate the source by hitting validate button.

Set Subscription
7. Specify subscription related parameter i.e To address, email priority, Reply to, Report Render format etc.

Set mai lsetting or File System
8. Specify the filter to report to get only recipient specific data (per the report)

Set parameter value
This is for the filter the data related to that person only. So, he/she will not get data of other persons.

e.g. If Cellular company sending the bill to user Patel, then the Bill contains the calling details related to user Patel only.

9. Schedule the report. It has three options: on data change, on schedule and shared schedule.

Schedule report
Note: This feature is part of enterprise edition only
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Comments (2)

I understand the concept but I am having trouble figuring out where to store the data (the parameters)that the report will use.  i.e. we have a list of 3 customers and a date range;  I need to create a report for each customer and give the report a unique name.  How is the list of parameters stored and updated?  And how do you access the data for the subscription
Is it possible to embed the report inside the email using data driven subscription?

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.