Anyone who has ever developed a report in SQL Server Reporting Services (SSRS) is probably familiar with building a basic subscription to automate executing the report and e-mailing it out to certain users. Many reports require only static parameters, such as a cost center or year, that can can be hard coded into the subscription. However, there are cases where you may not know the exact parameters that need to be passed to the report but you still want to automate the report to run on a certain schedule. This is where a data driven subscription becomes useful.
A data driven subscription is similar to a normal subscription with the exception of giving you the ability to declare dynamic parameters that can be passed to the report. I have found the most common data driven subscriptions tend to pass date parameters. The example outlined below illustrates creating a data driven subscription to pass a
enddate parameter to the report. In this example, which queries the
HR.Employees table from the
TSQL2012 sample database, I have configured the subscription to determine the first day of the current year as the
startdate and the current date as the
enddate. Since the parameters are used to filter by
hiredate, this allows the report to display all employees hired within the current year without having to modify the subscription each new calendar year.
Before stepping through the process of creating the data driven subscription, review the below script which I used to create the stored procedure for pulling the data. As described above, you will see the two
DATETIME parameters being declared and then being called in the
CREATE PROCEDURE [dbo].[Get_Employees_By_Hire_Date]
WHERE hiredate BETWEEN @startdate AND @enddate
The steps outlined below assume you already have a stored procedure and front-end report created and published. To learn more about creating SSRS reports you can reference my blog post, A Simple Guide To Building Reports Using SQL Server Reporting Services.
To get started on setting up a data driven subscription, connect to the SSRS front-end and locate the published report where you want to create the subscription. Once you have opened the report, navigate to the report setup screen (usually by clicking the report name at the top of the screen).
You will see a list of options on the left hand side. Click on Subscriptions and then click New Data-driven Subscription.
Step 1 is to provide a description and choose the desired delivery method of the report. The description can be anything that will help you better distinguish the new subscription from any existing ones. The most common delivery method tends to be E-Mail.
Depending on how your reports are set up, you may want to specify a shared data source. Though you can keep the default selection of Specify for this subscription only, best practice is to used one of the pre-configured shared data sources.
Once you have specified the delivery method and data source option click Next >
If you selected to specify a shared data source, Step 2 will allow you to select one of the pre-configured shared data sources. In the example below I have selected the TSQL2012 data source. If you do not already have a pre-configured data source set up for the desired connection, you can easily create one by following the instructions outlined in Creating A Shared Data Source In SQL Server Reporting Services.
If you choose to specify a data source for the subscription only, you will need to provide the connection string and credentials for running the report.
Once you have selected the desired data source click Next >
Step 3 is the core of the data driven subscription. This is where you will specify the dynamic data that will drive the report. In this example I am running a
SELECT statement to assign the first day of the current year to
startdate and the current date to
Once you have entered the desired script, click Validate to confirm that the script does not contain any syntax errors.
Once you have specified the command and validated the syntax, click Next >
Step 4 is where you will fine tune the delivery options. The only field that requires input here is the To field. This is where you will specify the recipients email address. This will let the subscription know who should receive the report when it is sent out. The remaining fields can be modified if needed but generally can be kept as their default values.
Once all changes have been made, click Next >
Step 5 involves assigning the report parameters to the query that was used in Step 3. By default the option to Specify a static value will be selected. Change this option to Get the value from the database for each parameter listed. Then, in the drop-down select the corresponding parameter. The value listed in the drop-down should match what was specified in the query from Step 3.
Once all parameters have been assigned click Next >
Step 6 is where you will configure when the subscription will run. The most common selection here tends to be On a schedule created for this subscription. Though shared schedules can be created for reports that generally run at the same time, I most often create a separate schedule for each report.
Once you have selected the option that works best for you, click Next > or Finish.
If you selected to run the report on a schedule created for this subscription, then the following screen will display the 7th and final step. Here you will configure the actual schedule. In the example below I have configured the report subscription to run Monday through Friday at 10:00AM. You can also set the report subscription to end on a specific date if desired.
Once the subscription schedule has been configured click Finish.
You should now see the newly created subscription listed on the Subscriptions page of the report in Reporting Services.
I usually add myself to all new reports initially so that I can confirm the report was executed and received. You can easily modify the report subscription by clicking Edit next to the subscription and clicking through the various configuration screens.
I hope this overview has provided some useful information on how to correctly create and configure data driven subscriptions in SQL Server Reporting Services.