Lesson 3: Defining a Data-Driven Subscription


Updated: May 26, 2016

Applies To: SQL Server 2016

In this Reporting Services tutorial lesson, you use the Reporting Services web portals data-driven subscription pages to connect to a subscription data source, build a query that retrieves subscription data, and map the result set to report and delivery options.

System_CAPS_ICON_note.jpg Note

Before you start, verify that SQL Server Agent service is running. If it is not running, you cannot save the subscription. One method for verification is to open the SQL Server Configuraton Manger. This lesson assumes you completed Lesson 1 and Lesson 2 and that the report data source uses stored credentials. For more information, see Lesson 2: Modifying the Report Data Source Properties

  1. In Reporting Services web portal, click Home, and navigate to the folder containing the Sales Orders report.

  2. In the context menu ssrs_tutorial_datadriven_reportmenu of the report, click Manage, and then click Subscriptions in the left pane.

  3. Click + New Subscription. If you do not see this button, you do not have Content Manager permissions.

  1. Type Sales Order delivery in description.
  1. click Data-driven subscription..
  1. In the schedule section click Report-specific schedule.
  2. Click Edit schedule.
  3. In Schedule Details, click Once.
  4. Specify a start time that is a few minutes ahead of the current time.
  5. Click Apply.
  1. In the Destination section, Select Windows File Share for the method of delivery.
  1. click Edit Dataset.

  2. Select A custom data source.

  3. Select Microsoft SQL Server as the data source Connection type.

  4. In Connection string, type the following connection string. Subscribers is the database you created in lesson 1.

    data source=localhost; initial catalog=Subscribers
  1. Select Using the following credentials.
  2. Select Windows user name and password.
  3. In User Name and Password, type your domain user name and password. Include both the domain and user account when specifying User Name.
    System_CAPS_ICON_note.jpg Note

    Credentials used to connect to a subscriber data source are not passed back to Management Studio. If you modify the subscription later, you must retype the password used to connect to the data source.

  1. In the query box, type the following query:

    Select * from OrderInfo  
  2. Specify a time-out of 30 seconds.

  3. Click Validate query, and then click Apply.

Fill in the following values:

ParameterSource of valueValue/field
File nameGet value from datasetOrder
PathEnter valueIn the Value, type the name of a public file share for which you have write permissions (for example, \\mycomputer\public\myreports).
Render FormatGet value from datasetFormat
Write modeEnter ValueAutoincrement
File ExtensionEnter ValueTrue
User NameEnter ValueType your domain user account. Enter it in this format: <domain>\<account>. The user account needs to have permissions to the path you configured.
PasswordEnter ValueType your password
  1. In the OrderNumber field , select Get value from dataset. In Value, select Order.
  2. Click Create Subscription.

When the subscription runs, four report files will be delivered to the file share you specified, one for each order in the Subscribers data source. Each delivery should be unique in terms of data (the data should be order-specific), rendering format, and file format. You can open each report from the shared folder to verify that each version is customized based on the subscription options you defined.

List of files created by the subscription

The subscription page in the Web portal will contain the Last Run date and Status of the subscription. Note: Refresh the page after the subscription runs to see the updated information.

Subscription results in Report Manager

This step concludes the tutorial "Define a Data-Driven Subscription".

Subscriptions and Delivery (Reporting Services)
Data-Driven Subscriptions
Create, Modify, and Delete Data-Driven Subscriptions
Use an External Data Source for Subscriber Data (Data-Driven Subscription)

Community Additions