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 or Management Studio. 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 can be stopped or deleted through the Manage Jobs page of Report Manager or Management Studio. If you want to temporarily prevent a subscription from processing, you can modify 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.

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. Click the Subscriptions tab of the report, and then click the New Data-Driven Subscription button to open the Create Data-Driven Subscription pages.

To modify an existing data-driven subscription, click the My Subscriptions link on the global toolbar. Alternatively, open the report and click the Subscriptions tab. Select the subscription you want to modify. The following icon indicates a data-driven subscription: Data-driven subscription icon


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.
    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 in Reporting Services. 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.

See Also


How to: Subscribe to a Report (Report Manager)
How to: Delete or Modify a Subscription (Report Manager)
How to: Subscribe to a Report (Management Studio)


Choosing Report Presentation Formats in a Subscription
Setting Parameters in a Subscription
Setting Parameter Properties for a Published Report
Creating, Modifying, and Deleting Standard Subscriptions
Subscription Processing
Report Manager

Other Resources

Delivering Reports Through Subscriptions
Managing Subscriptions
Subscriptions Page (Report Manager)
My Subscriptions Page (Report Manager)

Help and Information

Getting SQL Server 2005 Assistance