Create Data-driven Subscription Page (Report Manager)

  • Article

Use the Create Data-driven Subscription pages to build or modify a subscription that uses query results to determine the recipients of the subscription, delivery settings, and report parameter values. At run time, the report server runs a query to get values used for subscription settings. You can use these pages to define the query and assign query values to subscription settings. The values and options that you specify for a data-driven subscription are divided among several pages, similar to a wizard. There are seven pages in all.

To create a data-driven subscription, you must know how to write a query or command that gets the data for the subscription. You must also have a data store that contains the source data (that is, the names of subscribers and the delivery settings associated with each one) to use for the subscription.

This page is available to users with advanced permissions. If you are using default security, data-driven subscriptions cannot be used for reports located in a My Reports folder.

To open this page, select a report, click the Subscriptions tab at the top of the page, and then click New Data-driven Subscription. The report must use stored credentials in order for this button to be enabled.

Note

This feature is not supported in SQL Server 2005 Express Edition with Advanced Services. For more information about feature availability, see Reporting Services in SQL Server 2005 Express Edition with Advanced Services.

Start a Subscription (Page 1)

  • Description
    Provide a description for the subscription. The description appears in subscription lists in My Subscriptions and in the Subscriptions tab of the report. It is also the subscription node name in Management Studio.

  • Specify how recipients are notified
    Select the delivery extension to use to distribute the report. Only one delivery extension can be used for each subscription. The following options are available:

    • Use Report Server File Share to deliver reports to a file share. The report will be saved as a static file. For more information, see File Share Delivery in Reporting Services.
    • Use Report Server E-Mail to deliver reports to an e-mail inbox. For more information, see E-Mail Delivery in Reporting Services.
    • Use Null Delivery Provider to deliver reports to the report server database. This option creates report snapshots. Choose this option when you want to preload the report server with user-specific or parameterized report snapshots on a specific schedule. For more information, see Report Caching in Reporting Services.

  • Specify a data source that contains recipient information
    Specify how the data source connection is defined. You can choose a shared data source if you have one that contains the connection information you need. You can also specify connection information directly in this subscription.

    The data source provides subscriber data. This data might consist of employee names, employee IDs, e-mail addresses, and preferences for export formats (such as HTML or PDF). If you are using the report server e-mail delivery extension, the data source should contain e-mail addresses.

Specify a Connection (Page 2)

If you specified a shared data source, use this page to select the shared data source item. You can use the tree control to navigate to and select the item. If you are defining a connection for this subscription, use this page to specify the following options.

  • Connection Type
    Select which data processing extension to use with the data source.
  • Connection String
    Type a connection string to use to connect to the data source.

  • Connect Using
    Type the credentials to use when connecting to the data source. The credentials are stored as encrypted values in the report server database.

    If the data source uses Windows Authentication, select Use as Windows credentials when connecting to the data source.

    Note

    You can also use no credentials, however, this option is not recommended.

Specify a Query (Page 3)

Specify a SQL query or a command that retrieves a result set that contains one row for each recipient of the subscription. On subsequent pages, the result set is used to populate data-driven extension settings. Specifically, columns in the result set that contain useful values, such as e-mail addresses, can be used to provide values for the subscription.

  • Timeout
    Specify a query time-out value. This value must be large enough to complete query validation.
  • Validate
    Click Validate to verify the query. The query must produce valid results before you can continue. If you do not click Validate, the query is validated when you click Next.

Set Delivery Options (Page 4)

On the fourth page, you specify delivery extension options. The options that appear on the page are derived from the delivery extension. How you specify those options can vary considerably based on how the delivery extension presents them. If the extension has no settings, no options appear on this page.

Select this To do this

Specify a static value

Use a constant value for the delivery setting.

Some delivery extensions provide static values that you can choose from. For example, report server e-mail delivery provides values for IncludeReport, RenderFormat, Priority, and Include Link.

Use caution when specifying a static value for a recipient field.

Get the value from the database

Use a value from the result set. The columns of the result set can be selected as a source of a data value to use with each subscription instance.

No value

Omit the setting from the subscription.

Important

For e-mail delivery, assigning static values for the To field, the Cc field, the Bcc field, and the Reply-To fields for e-mail delivery can produce unintended results. The recipients specified in these fields will receive one delivery for each row in the result set. If the result set is extensive, you run the risk of overwhelming the capacity of the recipient's mailbox. For example, if the result set contains 20 rows, each recipient receives 20 messages in his or her in-box.

Set Parameters (Page 5)

If a report includes parameters, you must specify which parameter values to use with the report. Parameter values can be obtained from the subscriber data source (for example, if you have a regional sales report that is parameterized based on a regional code, you can obtain region information for each employee if that information is stored in the employee database).

Select this To do this

Specify a static value

Use a constant value for the parameter if you want to use the same parameter for all subscribers. Some parameters provide a list of values that you can choose from. For example, a list of months, years, or product categories.

Get the value from the database

Use a value from the result set. The columns of the result set can be selected as a source of a data value to use with each subscription instance.

Specify a Trigger (Page 6)

Select a trigger that initiates the subscription.

Select this To do this

When the report data is updated on the report server

If the report is configured to run as a report execution snapshot, you can process the subscription when the snapshot is refreshed.

On a schedule created for this subscription

Run the subscription at a specific date and time.

On a shared schedule

Run the subscription using schedule information provided through a shared schedule.

Schedule a Subscription (Page 7)

If you schedule the subscription, you must specify the frequency with which the report is delivered. The first set of options specifies a category of frequency (hourly, daily, weekly, and so on). The second set of option that appears is based on your initial selection.

  • Hourly
    Define a schedule that runs at hourly intervals.
  • Daily
    Define a schedule that runs on the days you select at a specific hour and minute. You can specify days in the following ways: Every <day>, Every weekday, and Every <number> day. Choosing one approach voids the others, even if the other days appear to be selected.
  • Weekly
    Define a schedule that runs at weekly intervals at a specific hour and minute. The interval can be in complete weeks (for example, every two weeks) or days within a week.
  • Monthly
    Define a schedule that runs on a monthly basis. Within a month, you can choose a day based on a pattern (for example, the last Sunday of every month) or specific calendar dates (such as 1 and 15 to indicate the first and fifteenth day of every month). Using commas and hyphens, you can specify multiple days and ranges, for example, 1, 5, 7-12, 21.
  • Once
    Define a schedule that runs only once. Use the Start and end dates section to specify the day on which to run the schedule. This schedule expires as soon as it is processed.
  • Start and end dates
    Specify a start date that determines when the schedule takes effect and an end date that determines when the schedule expires. Schedules expire without notification. After the end date, a schedule no longer runs.

Saving the Subscription

The Finish button is enabled when there is enough information for the subscription. Click Finish to complete the subscription.

See Also

Reference

Report Manager F1 Help

Other Resources

How to: Start Report Manager (Report Manager)
Report Manager
Data-Driven Subscriptions
Tutorial: Creating a Data-Driven Subscription
Specifying Credential and Connection Information
Delivering Reports Through Subscriptions

Help and Information

Getting SQL Server 2005 Assistance