Previously I wrote an article on Subscriptions and how to configure them on a report to have it delivered unattended on a schedule. If you have not read that article make sure to review the prerequisites to running subscriptions:
· SQL Server Agent Must be running
· Enable sharing on folder that report is being delivered to or configure SMTP server
· Create a SQL Server Authenticated account that the report data sources can use
In this article I will be going beyond configuring regular Subscriptions and discuss how you can configure Data Driven Subscriptions. This kind of subscription is only available with either the Enterprise or Developer editions of SQL Server and gives you a way to dynamically deliver and process reports.
For example, if you have a report that uses a parameter to return different values you can use a Data Driven Subscription to create a report with the results of every possible parameter value. You could also use this kind of subscription to dynamically deliver your reports based on a query your write.
To configure a Data Driven Subscription first open the report that you want subscribe to in the Report Manager. Once the report is open select the Subscriptions tab.
Then select the New Data-driven Subscription option.
If you see a warning sign on top of the icon then it is likely that you have not correctly set your data sources for this report to use SQL Server authentication.
Step One: Select delivery method
After clicking on New Data-driven Subscription you can start the configuration. Here you can give the subscription a description, decide how to deliver the reports, and what data source will to query.
· You have three options for delivery methods:
· Windows File Share – places reports in a folder setup for sharing
· Email – uses an SMTP server to email reports
· Null Delivery Provider – Saves reports into cache to improve report performance
If you have SharePoint installed there’s also a third option to deliver to SharePoint library.
The data source selection will depend on if you have already deployed the data source that is used for subscription query. Remember the Data Driven Subscription will require you to write a query that will populate things like the name of the file created, the kind of file to deliver, and who to deliver it to. If you do not already have the data source to write this query this select specify for this subscription only but if the data source is already deployed select Specify a shared data source. Once you have decided what kind of data source you have click Next.
In this example, I will do a Windows File Share using a shared data source.
Step Two: Select/Create a data source for your query
Select the data source that you will use to write a query on.
I will be showing AdventureWorksDW2008 as my data source.
Step Three: Write query
Now you are ready to write the query that will handle the subscription. The query you write can populate any parameter in the report as well as these Subscription properties:
Each of these settings will be talked about in the next step. Once you write your query you can manipulate the command time-out and also validate the query. Once you are satisfied with your query select Next.
Here I am writing a query to populate the report parameter and the Subscription property FILENAME.
Step Four: Delivery Configuration
Now you must specify the settings for delivery of the report. Each one of these properties could have been manipulated with the query written on the previous step. You can either type in a static value or get the value from the database, which is just the query you wrote.
· File name – the name of the file(s) to be created
· Path – the file path where the file will be sent, which must be full UNC path. (Ex.\\ServerName\Folder)
· Render Format – what type of file will be created (Ex. Excel, PDF, Word…Just to name a few)
· Write mode – whether the file that is created can overwrite other files with the same name or to auto-increment (Ex. ColorReport_1, ColorReport_2…)
· User name – user that has permissions to save files to the shared folder in the Path property
Step four will look different depending on the delivery method you choose. The configuration for Email delivery obviously has different properties. Once your settings are complete hit Next.
My example I have mostly static values except for File name that is bring populated from the query. My path is covered in the screen shot mine looks like this (\\Devin-PC\Reports).
Step Five: Fill report parameter values
All report parameters can be set to dynamically change when using Data Driven Subscriptions. Based on the query you wrote you can make it so each report that is delivered has a different value populated for the parameter.
I used my query to fill the value of parameter. Each report that is generated will show the results with a different parameter.
Step Six: Select scheduling options
Scheduling the subscription gives you the ability to send reports unattended at any point and time.
Your options are:
· When the report data is updated on the report server – when changes have been made to the report
· On a schedule created from this subscription – setup a schedule just for this subscription
· On a shared schedule – a predefined schedule
Choose when you want to subscription to run this hit Next.
I will show how to setup a schedule just for this subscription.
Step Seven: Create a schedule
Now you will create a schedule for when the report should run. The report can be set to run hourly, daily, weekly, monthly or just once. You can also set a date range for how long this schedule should run. When this is done select Finish.