Below are the following steps to create a Data driven subscription in SSRS through SSRS report manager:
Step 1: Go to the Report Manager and do right click on report to go on manage option of the report and click on Subscriptions option under the properties:
Step 2: Click on New Data driven Subscription tab for creating a data driven subscription or if we want a standard subscription than click on New Subscription:
Click here for Differences between Data Driven and Standard Subscriptions
Step 3: In Data driven subscription specify the description of the subscription, email as recipient format if you want to deliver rendered file through the email or if you want to share rendered file through a shared location than you can use “Windows File Share” option:
There are two options available for implementing a Data Source (database connection):
· Shared Data Source
· Specify for this Subscription only
Shared Data Source is a Data source which we have created on report manager to connect to a particular database server so for creating a Shared data source click on below link:
Here we have existing Data sources available for reporting so we are going to use Tran1 data source under the Datasets folder:
Step 4: If we select second option “Specify for this subscription only” than we need to provide connection string and credentials as:
Step 5: After the selecting data source we need to go on next page where we need to provide a query and click on Next, suppose we want to generate email list from database table then we can use that SQL query to fetch email list here as I have added a SQL script to get year value for my parameter’s value:
On the next page, If we choose the delivery method to Windows File Share, the fields on below screenshot will change to let you specify the file name, share location, credential to use to copy the file at that location, overwrite options, etc.
Step 6: Provide the information about recipient email and rendered format of the report and Subject (select false option for include Link option) and click on Next:
Step 7: On this page provide parameter’s information and click on next as I have one parameter here named “Year” so I have use second option to populate value from SQL query and you can provide a static value also for this parameter:
Step 8: On the next page we need to schedule this subscription by following options:
- On a schedule created for this subscription: In this option we can schedule our subscription for Hourly, Weekly, Daily or Monthly as:
- On A Shared Schedule: in this option we need to provide a shared schedule which we can create to use in multiple subscriptions as in below screen shot I have selected Weekly Refresh for Monday morning shared schedule:To deep dive about Shared Schedule, please go through this link: Shared Schedule in SSRS report manager
Step 9: Finally click on next and your subscription is ready to trigger based on scheduled time.
If you have any thoughts or suggestion, feel free to post in the below comment section.Thanks!