Creating, Modifying, and Deleting Data-Driven Subscriptions
A data-driven subscription is a query-based subscription that gets the data values used for processing the subscription at run time. When the subscription is triggered, a query is processed to get up-to-date information about recipients, report delivery options, rendering formats, and parameter settings. The query results are combined with the subscription definition to create a dynamic subscription that uses data you already maintain in an employee database, a customer database, or any other database that contains information that can be used as subscriber data.
To create a new data-driven subscription or modify an existing subscription, use the Create Data-Driven Subscription pages in Report Manager. These pages walk you through each step of creating or modifying a subscription. To access a subscription after it is created, use the My Subscriptions page and the Subscriptions list of a report. To learn how to create a data-driven subscription, see Tutorial: Creating a Data-Driven Subscription.
Managing and Deleting a Data-Driven Subscription
A data-driven subscription that is in progress cannot be stopped or deleted through the Manage Jobs page of Report Manager. For this reason, it is advantageous to use a shared schedule to trigger data-driven subscription. That way, if you want to temporarily prevent a subscription from processing, you can pause the schedule that triggers the subscription. For more information, see Managing Subscriptions.
To delete a data-driven subscription, select it from the My Subscriptions page or the Subscriptions page of a report and then click Delete.
For instructions on how to cancel a data-driven subscription, see Managing a Running Process.
Creating and Modifying a Data-Driven Subscription
To create a data-driven subscription, from Report Manager select a report that uses stored credentials or no credentials.
To create a data-driven subscription
In Report Manager navigate to the folder containing the report, hover over the report, open the options menu and Click the Manage.
Click the Subscriptions tab.
Click the New Data-Driven Subscription button.
To modify an existing data-driven subscription
In report Manager, In Report Manager navigate to the folder containing the report, hover over the report, open the options menu and Click the Manage.
Click the Subscriptions tab. Alternatively click the My Subscriptions link on at the tope of report manager
Select the subscription you want to modify. The following icon indicates a data-driven subscription:
You can modify any value that is already specified. All values are presented as they were first created, except for the password that is used to access the subscriber data store. You must retype the password every time you modify values on the second page or any subsequent page.
Before you can create a data-driven subscription, ensure that you satisfy the following requirements:
Report requirements. The report must use stored credentials or no credentials to retrieve data at run time. You cannot subscribe to a report that uses impersonated or delegated credentials to connect to an external data source; the credentials of the user who creates or owns the subscription will not be available when the subscription is processed. The stored credentials can be a Windows account or a database user account. For more information, see Specifying Credential and Connection Information for Report Data Sources (SSRS).
You cannot subscribe to a Report Builder report that uses a model as a data source and the model contains model item security settings. Only reports that use model item security are included in this restriction.
You cannot create a data-driven subscription on a report that contains the User!UserID expression.
Data requirements. You must have an accessible external data source that contains subscriber data.
User requirements. The author of the subscription must have permission to "Manage reports" and "Manage all subscriptions." For more information about item-level task permissions, see Tasks and Permissions. The author must also have the necessary credentials to access the external data source that contains subscriber data.
Defining a Query That Retrieves Subscription Information
A data-driven subscription must specify a query or command that retrieves subscriber data. The query should produce one row for each subscriber. If you are using the e-mail delivery extension, the query should return a valid e-mail alias for each subscriber. The number of deliveries that are made is based on the number of rows returned by the query. If the row set consists of 10,000 rows, the subscription delivers 10,000 reports.
If executing the query is time-consuming, you can increase the time-out value to accommodate additional processing.
For this step, the query must be validated before you continue. Validation does not process the query, but it does return a list of all columns that are in the row set so that you can reference the columns in subsequent selections. If the query fails to validate, you cannot continue. A query fails to validate if the query syntax is incorrect or if the connection to the data source is not valid. Use the Back button to make corrections to the data source.
Running the Subscription
You must specify conditions for processing the subscription. You can specify a schedule, or you can trigger the subscription to coincide with updates to a report execution snapshot. Processing for data-driven subscriptions is the same as processing for standard subscriptions. For more information, see Subscription Processing.