Walkthrough - Creating a Data-Driven Subscription
This tutorial shows you how to use data-driven subscriptions step by step. In this walkthrough, you create a small subscriber database and populate it with values you will reference later in the subscription.
- A report that includes parameters. This walkthrough assumes the sample report, Employee Sales Summary. For more information, see Reporting Services Sample Reports.
- A report server that is configured to use the e-mail delivery extension. For more information, see Configuring Report Server E-Mail Delivery Extension.
- SQL Agent service must be running.
- A local SQL Server database that contains subscriber information. If you already know how to create a database in SQL Server, this step takes just a few minutes. You will need a minimum of three rows of subscriber data to complete this walkthrough. Step 1 describes the data you need.
To make this tutorial meaningful, use a valid e-mail alias. This tutorial asks you to repeat the value of a single valid alias (your own). In this walkthrough all subscriber data is fictional except for that value.
Create a Sample Subscriber Database
This step creates the subscriber data values that the report server retrieves when the subscription is processed. Do not omit this step even if you have a data source that contains names, e-mail aliases, and department data. Use Enterprise Manager to create the database, table, and columns.
- Create a SQL Server database named Subscribers.
- Create a table.
- Add five columns: Name, Alias, EmployeeID, Format, Linked. For all columns, set the data type to Varchar and the length to 50.
- Name the table UserInfo.
- Insert three rows of data. You can use SQL Query Analyzer to add the data. The following example shows how to insert a single row of data using the INSERT statement.
INSERT INTO UserInfo (Name, Alias, EmployeeID, Format, Linked)
VALUES ('Fernando Caro', '<your e-mail alias>', '24', 'IMAGE', 'True')
- Repeat the INSERT INTO statement from the previous step two more times to insert the following data:
VALUES ('Rachel Valdez', '<your e-mail alias>', '35', 'MHTML', 'True')
VALUES ('Michael Blythe', '<your e-mail alias>', '38', 'PDF', 'False')
- Use a SELECT statement to verify that you have three or more rows of data. For example: SELECT * FROM UserInfo
Specify Stored Credentials
- Click Start, click Programs, click Microsoft SQL Server, click Reporting Services, and then click Report Manager.
- In Report Manager, select the Employee Sales Summary sample report.
- Click the Properties tab at the top of the page, and then click Data Sources.
- Click A custom data source.
- For Connection Type, select Microsoft SQL Server.
- Type the following connection string:
data source=<the name of your SQL Server>; initial catalog=AdventureWorks2000
- Click Credentials stored securely in the report server.
- Type your user name and password, click Use as windows credentials when connecting to the data source, and then click Apply. If you do not have permission to access the AdventureWorks2000 database, specify a login that does.
- Click the View tab to verify that the report runs with the credentials you specified. Note that you must select an Employee name and then click View Report to view the report.
Start the Wizard and Choose a Delivery Method
- To define a data-driven subscription, click the Subscriptions tab, and then click New Data-Driven Subscription.
- (Optional.) Type a description for the subscription.
- Select Report Server E-mail as the delivery method, and then click Next.
Connect to the Subscriber Data Source
- Select Microsoft SQL Server as the connection type.
- Type the following connection string:
data source=<the name of your SQL Server>; initial catalog=Subscribers
- Type your user name (including the domain) and password, click Use as windows credentials when connecting to the data source, and then click Next.
Define a Query That Retrieves Subscriber Data
- Type the following query, click Validate, and then click Next.
Select * from UserInfo
Set Delivery Options
- For the To option, click Get the value from the database, and then select Alias.
- For CC, BCC, and ReplyTo options, click No value.
- For the IncludeReport option, click Specify a static value, and then select True.
- For the RenderFormat option, click Get the value from the database, and then select Format.
- For the Priority option, click Specify a static value, and then select Normal.
- For the Subject option, click Specify a static value, and then type @ReportName was run at @ExecutionTime.
- For the Comment option, click No Value.
- For the IncludeLink option, click Get the value from the database, and then select Linked.
- Click Next.
Specify a Parameter Value
- Use the default parameter values for Month and Year. These are December and 2003, respectively.
- For Employee, click Get the value from the database, and then select EmployeeID.
- Click Next.
Choose a Subscription Trigger
- Click On a schedule created for this subscription, and then click Next.
Schedule the Subscription
- Click Once to run the subscription exactly one time. Specify a start time that is 10 minutes from the current time.
- Click Finish.
Evaluating the Subscription Results
When the subscription runs, three e-mail messages will be delivered to your e-mail inbox, 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 whether it includes a link.