Lesson 3: Defining a Data-Driven Subscription

In this lesson, you use the 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.

Before you start, verify that SQL Server Agent service is running. If it is not running, you cannot save the subscription.

Start the Data-Driven Subscription Wizard

1. In Management Studio, connect to a report server, expand Home, and navigate to the Employee Sales Summary report.

2. Expand the report.

3. Right-click Subscriptions, and select New Data Driven Subscription.

4. Type a description for the subscription.

5. For Delivery method, select Report Server File Share as the delivery method, and then click Next.

Specify a connection to the subscriber data source

1. Click A custom data source.

2. In Type, select Microsoft SQL Server as the data source type.

3. In Connection string, type the following connection string:

   data source=localhost; initial catalog=Subscribers
   

4. Click Credentials stored securely on the report server.

5. In Login name and Password, type your domain user name and password. Include both the domain and user account when specifying Login name.

1. Select Use as windows credentials when connecting to the data source, and then click Next.

Define a query to retrieve subscriber data

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

   Select * from UserInfo
   

2. Click Validate, and then click Next.

Set delivery options

1. For File name, in Setting Source, select Query Results Field. In Setting Value, select LastName.

2. For File Extension, in Setting Source, select Query Results Field. In Setting Value, select FileType.

3. For Path, in Setting Source, select Static Text. In Setting Value, type the name of a public file share for which you have write permissions (for example, \\mycomputer\public\myreports). If you not sure how to create a public folder, see How to: Create a Shared Folder for Report Server File Share Delivery.

4. For Render Format, in Setting Source, select Query Results Field. In Setting Value, select Format.

5. For User name, in Setting Source, select Static Text. In Setting Value, type your domain user account.

6. For Password, in Setting Source, select Static Text. In Setting Value, type your password. Be sure that you type the password carefully. The wizard does not validate the password.

7. For Write mode, in Setting Source, select Static Selection. In Setting Value, select AutoIncrement. Click Next.

Specify a parameter value to vary report output

1. Use the default parameter values for Month and Year.

2. For Employee, select Query Results Field. In Value, select EmployeeID. Click Next.

To schedule a subscription

1. Click On a custom schedule, and then click Set Schedule.

2. In Recurrence Pattern, select Once.

3. Specify a start time that is a few minutes ahead of the current time.

4. Click Finish.

Next Steps

When the subscription runs, three report files will be delivered to the file share you specified, one for each subscriber in the Subscribers data source. Each delivery should be unique in terms of data (the data should be employee-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.

This step concludes the tutorial "Defining a Data-Driven Subscription". To learn more about other Reporting Services tutorials, see Reporting Services Tutorials.

See Also

Tasks

Tutorial: Creating a Data-Driven Subscription

Other Resources

Delivering Reports Through Subscriptions
Data-Driven Subscriptions
Creating, Modifying, and Deleting Data-Driven Subscriptions
Subscription and Delivery Scenarios
Using an External Data Source for Subscriber Data

Help and Information

Getting SQL Server 2005 Assistance