Microsoft SQL Server 2005 Reporting Services

The steps to create a new data-driven subscription are not too terribly different than the steps needed to create a standard subscription. The large difference in the data-driven subscription model is that the parameters for the recipient list and the rendering extension ET are all derived from a query. To aid end users in creating a data-driven subscription, both SQL Server Management Studio and Report Manager have wizards to set up all the information. Remember that to use a subscription, the report's data sources must have stored credentials or no credentials.

Creating a Subscriptions Database

Before creating a data-driven subscription, the end user must create a data set that can be used to hold the information for the subscription. To create a Subscriptions database, open the SQL Management Studio and run the following script:

use master go if exists(select name from master.dbo.sysdatabases where name = 'Subscriptions') begin drop database [Subscriptions] end create database [Subscriptions] go use [Subscriptions] go create table [SubcriptionInfo] ([To] nvarchar(50), [Format] nvarchar(50), [EmailAddress] nvarchar(50), [EmployeeId] nvarchar(50), [Linked] nvarchar(50), [IncludeReport] nvarchar(50)) go insert into [SubcriptionInfo] ( [To],[Format],[EmailAddress],[EmployeeId],[Linked],[IncludeReport]) select FirstName + ' ' + LastName [To], Format = case (EmployeeId%2) when 0 then 'MHTML' else 'PDF' end, EmailAddress, b.EmployeeId, Linked = case (EmployeeId%2) when 0 then 'True' else 'False' end, IncludeReport = case (EmployeeId%2) when 0 then 'True' else 'False' end from AdventureWorks.Sales.SalesPerson a, AdventureWorks.HumanResources.Employee b, AdventureWorks.Person.Contact c where a.SalesPersonId = b.EmployeeId and c.ContactId = b.ContactID

The preceding script creates a new database called Subscriptions. When setting up the data-driven subscription, a custom data source is created to connect to the table and pull the subscription information. It also pulls in the salespeople information from the AdventureWorks catalog, and sets some preferences.

Report Manager

Like everything else thus far, you can use both SQL Server Management Studio and Report Manager to create data-driven subscriptions. As always, we start with Report Manager, and later show you how to create data-driven subscriptions with SQL Server Management Studio. The following steps show how to create a data-driven subscription using Report Manager:

1.

Navigate to the Employee Sales Summary report from the Sample Reports included with SSRS, click the Subscriptions tab, and select New Data Driven Subscription.

2.

Enter a description, and choose Report Server Email for the delivery method. Select Specify for This Subscription Only under the prompt for the data source for the recipient information.

3.

Enter the information needed to log in to the Subscriptions database.

4.

Enter the following query to select information for the recipient list:

select *, datepart(m,getdate()) [month], datepart(yyyy,dateadd(yyyy,-1,getdate())) [year] from [SubcriptionInfo]

5.

You can click the Validate button to execute the query on the Report Server and check to see if it is valid.

6.

Change the following values on the delivery settings from the defaults:

  • To gets its value from EmailAddress .

  • IncludeReport gets its value from the Include Report field.

  • Renderformat gets its value from Format .

  • IncludeLink gets its value from Linked .

7.

Next enter the parameters from the database query:

  • Month comes from the Month .

  • Year comes from the Year .

  • Employee comes from the EmployeeId .

8.

Finally, create a custom schedule for the subscription or choose if it should be run on a shared schedule or after a new snapshot has been created. More information on creating shared schedules is in Chapter 20, "Report Execution and Processing."

SQL Server Management Studio

As previously mentioned, data-driven subscriptions can be created using SQL Server Management Studio as well as Report Manager. The following steps show how to re-create the data-driven subscription created earlier through Report Manager using SQL Server Management Studio:

1.

Using Object Explorer, navigate to the Subscriptions folder under the Employee Sales Summary report, right-click on the Subscriptions folder, and select New Data Driven Subscription. This should start the Data Driven Subscription Wizard.

2.

Click Next on the Welcome screen of the wizard. Enter a description, and choose Report Server Email for the delivery method.

3.

Select Custom Data Source and enter the information needed to log in to the Subscriptions database.

4.

Enter the query from step 4 in the preceding set of steps, and click Validate to confirm the data source and query run.

5.

Change the following values on the delivery settings from the defaults:

  • To gets its value from EmailAddress .

  • IncludeReport gets its value from IncludeReport .

  • Renderformat gets its value from Format .

  • IncludeLink gets its value from Linked .

6.

Next enter the parameters from the database query:

  • Month comes from the Month field.

  • Year comes from the Year field.

  • Employee comes from the EmployeeId field.

7.

Finally, create a custom schedule for the subscription or choose if it should be run on a shared schedule or after a new snapshot has been created.

Related

Категории