SSRS Data Driven Subscriptions - Part 2

In previous post, we have created tables with data, and designed and deployed the report to report server. This post will show the steps to configure data driven subscription.

First, data driven subscription is only supported in either SQL Server Business Intelligence or Enterprise edition. Open the Reporting Services Configuration Manager and connect to the instance.



Once the required edition is verified, copy the Report Manager URL and paste it on to your internet browser.



Navigate to the ProductReport, and click the drop down button and select Manage.



Specify the delivery method for the subscription. Select Email.



Configure the data source contains recipient information



Configure the query and click Validate button to validate the query.



Next, specify and configure the data driven subscription delivery extension setting. Here is where the mapping of a dataset column with certain subscription property such as email recipient, render format, subject and etc. Change To property to Get the value from the database with Email in the drop down list. (Optional) Change the Comment to Get the value from the database with Name in the drop down list. This put the name of the manager in the email body.



The following step map the report parameter (DepartmentID) with DepartmentID dataset column. This is to filter the product listing report with only the product from the manager's department.



Specify the subscription schedule. Click the On a Schedule created for this subscription and configure the schedule in the schedule page to run daily and click Finish.

Once the subscription is run, Manager A and Manager B should receive the email with the product listing report. Here is the example of the email body received by Manager A. Notice that there are only 3 products which are from the Manager A's department.



This post shows the steps how to use data driven subscription to distribute email notification to a dynamic subscription retrieved from an external data source. As mentioned earlier, this feature is only available on SQL Server Business Intelligence and Enterprise edition.

If you are using other edition or require more customization, you may need to create your own method. The next post utilizes SQL Server Integration Service (SSIS) to render SSRS reports and send out email notification based on an external data source.