SQL Server Reporting Services (SSRS) is a server based scalable and extensible platform for authoring, deploying, executing and managing reports based on a variety of data sources. SSRS allows us to create interactive, tabular, graphical (using data visualization controls) or free from reports from relational, multidimensional (using MDX or DMX) or XML data sources. Furthermore it allows you to view/export your reports in a variety of formats. You can enable report caching, which improves the performance for subsequent requests if the parameters remain same.
You can set the subscription for the report in which case report server processes the subscription, generates the reports and delivers it to the intended audience via email delivery or file share delivery method on the defined schedule. In my last article I talked about standard subscription in which you need to specify whatever parameters the report expects at the time of subscription creation and cannot be changed at runtime. In this article, I am going to talk about Data-Driven Subscription in detail.
Understanding Data-Driven Subscription
"In the subscription model, a subscriber subscribes/registers himself with publisher to get the subscription (data, reports, updates etc.) delivered on a scheduled/reoccurring basis."
A report subscription is a report snapshot generated on some defined scheduled time and delivered to the intended audience by the report server. SSRS allow you to create two types of subscriptions, i.e. standard subscription and data-driven subscription.
In my last article I talked about standard subscription in which we need to specify whatever parameters the report expects at the time of subscription creation and which cannot be changed at runtime; in this article we are going to discuss and use data-driven subscription.
In data-driven subscription the data or parameter values required during execution of the report can come from a query from a database including recipient list, delivery method and parameter values needed for each recipient's report execution, which makes it a dynamic subscription that gets required data during run time from the external data source.
There are two main usages of data-driven subscription:
- You can set up to generate and distribute dynamic reports (with different sets of parameter values) to a dynamic list of recipients (the list of recipients can come from the database) with varying rendering formats. If the parameter values or list of recipients change, you just have to change it in database; no report change is required as in the case of standard subscription.
- Apart from having two standard delivery modes (email delivery or windows file share delivery), data-driven subscription has one more specialized delivery mode extension (called NULL delivery extension) that instead of sending reports to file share or email, it preloads a parameterized reports cache. NULL delivery extensions can be set up only in data-driven subscription by the report server administrator to preload the cache or generate the report snapshots beforehand or during peak hours and thus improving the performance of report server by reducing the time it takes to present the reports to users.
Getting Started with Report Subscription
To setup data-driven subscription for a report, go to Report Manager, navigate to the folder that contains the report, point to the report for which you want to set up a subscription, click on the down arrow and click on the Manage menu item as shown below:
Figure 1 - Manage Report
On the manage report page, click on the Subscription option and then click on the New Data-driven Subscription button as shown below:
Figure 2 - Click on the New Data-driven Subscription button
Clicking on the New Data-driven Subscription as above will launch a wizard to create the data-driven subscription. On the first step of the wizard, you need to provide a description for the subscription, delivery mode and the data source to use to get information about the subscriber and its parameter values:
Figure 3 - Create a data-driven subscription
On the second step of the wizard, you need to specify the data source details. You can either use a predefined share data source or create a new one:
Figure 4 -Specify the data source details
On the third step of the wizard, you need to specify the query to fetch the list of recipients, delivery settings and parameter values, if any, from the data source chosen in the last step. You can click on the Validate button to get your query validated by data source:
Figure 5 - Click on the Validate button to get your query validated by data source
On the fourth step of the wizard, you need to specify delivery extension settings for the delivery mode you chose on the second step. You can specify different values for report execution, which will either come from the database or be static values; for example in the case of file share more, you can specify path, file name, extension, write mode and user name/password (either to come from database or to specify the static value) to be used by report server to put the file at the specified location:
Figure 6 - Specify delivery extension settings for Report Server FileShare
On the fifth step of the wizard, you need to specify report parameter values. You can specify different values for report parameters, which will either come from the database or use default of the parameter or use static values; for example in screenshot below, you can see I am taking the value for Report Month and Report Year from the database and specifying a static value for Employee Id:
Figure 7 - Take value from the database and specify a static value
On the sixth step of the wizard, you need to specify when the subscription will be processed. You can choose any of these three options as shown below:
Figure 8 - Specify whe the subscription is processed
If you chose "On a schedule created for this subscription" option on the last step, then on the step 7 you need to define the schedule for subscription processing.
Figure 9 - Define the schedule for subscription processing
As you can see in the image above, I have specified to process the subscription every two minutes (for demo purpose I have kept it 2 minutes but in real you might have a bigger gap for subscription processing) and hence you can see the result below.
Figure 10 - Subscription processing result
There are a couples of points that need to be taken into consideration when creating data-driven subscription for the reports.
1. As we all know, data sources can be categorized in two categories; data sources from the first category do not have credentials and are used for connecting to Excel or XML, etc. Data sources from the second category need credentials and are used for connecting to SQL Server or other DBMS. If your report uses the first category of data sources (which does not need credentials) then there is no problem but if it uses the data sources from the second category (which need credentials) then all these data sources must have credentials saved with them.
2. If the delivery mode is fileshare, the user that you specify or map from the database while creating a subscription must have the appropriate permissions (write access) on the fileshare location as this is the account report server uses to connect to fileshare, and put the file there.
3. If the delivery mode is Email, report server will not throw an exception if the report cannot be accepted by the email server because of the huge size of the report. In this case you need to have an exception set up on the email server for your account or you can use a report link instead of an attachment or use the fileshare delivery mode.
4. By default, Subscription and delivery extensions (for email you need to do email/SMTP configuration in Reporting Services Configuration Manager) are enabled; if you want to disable it you can set ScheduleEventsAndReportDeliveryEnabled property to False in the Surface Area Configuration for Reporting Services facet of SQL Server Policy-Based Management.
5. When creating a data-driven subscription, you need to either specify to use a default value for the parameter or a static value or specify a value to come from the database (dynamic in this case) to use each time the subscription is processed.
6. Depending on the number of rows being returned while querying the recipients, that many reports will be generated; one report for each recipient.
7. All the associated subscriptions will be deleted if you delete a report.
8. A report subscription can become inactive if the report (or report snapshot on which subscription is based) is modified; to activate a subscription you can open and then save it.
9. SQL Server Agent must be running for creating and running report subscriptions.
In this article I talked about Data-Driven Subscription in which case data or parameter values required during execution of the report can come from a query from a database including recipient list, delivery method and parameter values needed for each recipient's report execution.