Create a data-driven subscription

A data-driven subscription is a type of subscription that gets data values used for report distribution at run time. A data-driven subscription is useful for distributing a report to a large number of recipients or to a recipient pool whose membership changes over time. An example might be sales reports for customer orders over the previous month.

Data-driven subscriptions can be configured to use dynamic values. When you create the subscription, you build a query that retrieves subscriber data from an existing database. Depending on the data available in the database, you can retrieve up-to-date values for recipient e-mail addresses, names, report delivery options, target rendering formats, or report parameter settings. The query results that are returned provide the values that drive the subscription.

What do you want to do?

Learn about requirements

Add a Subscription

Manage Subscription Processing

Learn about requirements

Requirements for creating a data-driven subscription include the following:

  • Predefined subscriber data that you can query from an employee database, a customer database, or any other database that contains values that can be used in a subscription. You can only specify a single database so be sure to select one that has all of the data you require.

  • A single report for which you are creating the subscription. The report you select must use stored credentials or no credentials when retrieving 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.

  • You cannot subscribe to a Report Builder report that uses a model as a data source if 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.

  • You must have Full Control level of permission to create and manage a data-driven subscription.

  • SQL Server Agent must be running on the computer that hosts the report server database.

Top of Page

Add a Subscription

  1. Open the library that contains the report. Check the data source properties to verify the report meets all of the requirements for subscription delivery.

  2. Point to the report.

  3. Click the down arrow next to the report and select Manage Subscriptions.

  4. Click Add Data-Driven Subscription.

Step 1: Specify Data Source and Query

  1. In Description, enter a short name that will uniquely identify this subscription on the Manage Subscriptions page.

  2. In Connection Type, select Custom Data Source if you want to specify all connection properties in the subscription or Shared Data Source if you have an existing shared data source that already has the connection properties you want to use.

  3. If you selected Custom Data Source, specify the following additional properties:

    1. In Data Source Type, select the data processing extension that the report server will use to retrieve data from the subscriber data source.

    2. In Connection String, enter connection properties sufficent to connect to the data source. The actual syntax varies depending on the data source you are using. If you are connecting to a SQL Server relational data source, an example connection string might be: "Data Source=ComputerName; Initial Catalog=AdventureWorks".

    3. In Credentials, enter a username and password that has permission to connect to and query the subscriber data source.

  4. If you selected Shared Data Source, specify the following additional properties:

    1. In Data Source Link, select the shared data source (.rsds) file that contains the connection properties you want to use.

  5. In Query, enter a query that returns all of the subscriber data you require. For e-mail delivery, it should return a list of e-mail adresses. For file share delivery, it should return sufficient information to create a unique file name in a shared folder.

  6. Click Validate to check query syntax.

  7. In Query Timeout, enter a value after which data processing will stop on the report server. Be sure to enter a value that is sufficent to connect to the data source, wait for the query results, and process the subscription on the report server.

  8. Click Next..

Step 2: Set Parameters

If the report uses one or more parameters to accept input values at run time, you must ensure that parameter values are available to the report when the subscription is processed.

  1. Choose whether to use the default value already defined for the report, override the default value with a different value from the same column, or override the default value by mapping the parameter to a query results field returned from the subscriber database. If the query results do not contain valid values for a parameter, you must use a default or static value. To set a default value, you can set parameter properties in the report.

  2. Click Next..

Step 3: Specify Delivery Options

Delivery options determine which delivery extension on the report server is used to distribute the report.

  1. For Windows File Share delivery, specify properties used to deliver a report to a shared folder that is accessible on the network. The report server exports the report to an application file and saves it to a shared folder. After the report is delivered, you can use a desktop application to view the file.

    1. In File Name, specify how the file will be named. In most cases, you will want to choose a value from the database to generate a report delivery that is unique to a specific user. If you specify a static name, a single report name will be used for each delivery to the same shared folder, overwriting the previous version. You can set Write Mode to Autoincrement to create unique names based on a number appended to each file.

    2. In Path, specify the network path of a shared folder in Universal Naming Convention (UNC) format: \\computername\foldername.

    3. In Render Format, specify the output format for each delivery.

    4. In Write Mode, specify whether subsequent deliveries overwrite an existing file of the same name or are placed alongside previous deliveries. Specify Autoincrement to keep older and newer versions of a file delivery in the same folder.

    5. In File Extension, specify whether a file extension is added to each file that is delivered. The render format determines the extension. For example, if you specified Excel output format, the file extension is .xls.

    6. In User Name, enter a Windows user account that has write permissions on the shared folder.

    7. In Password, enter the password for the user account.

  2. For SharePoint Document Library delivery, specify properties used to deliver a report to a library in the same site.

    1. In File Name, specify how the file will be named. In most cases, you will want to choose a value from the database to generate a report delivery that is unique to a specific user. If you specify a static name, a single report name will be used for each delivery to the same shared folder, overwriting the previous version. You can set Write Mode to Autoincrement to create unique names based on a number appended to each file.

    2. In Path, specify a library in the same site.

    3. In Render Format, specify the output format for each delivery.

    4. In Write Mode, specify whether subsequent deliveries overwrite an existing file of the same name or are placed alongside previous deliveries. Specify Autoincrement to keep older and newer versions of a file delivery in the same library.

    5. In File Extension, specify whether a file extension is added to each file that is delivered. The render format determines the extension. For example, if you specified Excel output format, the file extension is .xls.

    6. In Title, specify the title of the item as it appears in the library.

    7. In Auto copy, if you are using the autocopy feature to automatically copy the latest version of a file to multiple locations, the file will be copied if Auto copy is set to True.

  3. Select Null Delivery Provider to preload the report cache on the report server with generated reports that vary for each user. The Null delivery provider is useful if you have a parameterized report for which you want to provide dynamic values at run time, where the dynamic values are read from the subscriber database. Users who access a cached report will still need to wait for the report to render, but all the data is retrieved in advance. If the report does not have parameters, the Null delivery provider is less useful. Consider configuring the report for scheduled data processing if your objective is to retrieve static data before the report is viewed.

  4. Click Next..

Step 4: Schedule Subscription Processing and Save the Subscription

  1. In Delivery Event, specify how the subscription will be triggered. You can use a shared or custom schedule to start subscription processing. Or, if the report is already associated with a schedule that drives data porcessing, you can trigger subscription processed based on when data is refreshed for the report.

  2. Verify that an instance of SQL Server Agent is running on the computer that hosts the report server database. You cannot save the subscription unless SQL Server Agent is started.

  3. Click Finish..

Top of Page

Manage Subscription Processing

After you define the subscription, you can view subscription status on the Manage Subscriptions page to determine when it last ran or if errors occurred.

You cannot pause subscription processing or temporarily disable it. However, if the report uses a shared data source, you can disable the data source to prevent report and subscription processing.

Data-driven subscriptions can produce a large number of files very quickly. If you must cancel the subscription because it is producing more deliveries than you expected, do the following:

  1. On the report server computer, open the RSReportServer.config file in a text editor.

  2. Find IsNotificationService.

  3. Set it to False.

  4. Save the file.

  5. In Manage Subscriptions page, delete the data-driven subscription.

  6. After you delete the subscription, in the RSReportServer.config file, find IsNotificationService and set it to True.

  7. Save the file.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×