Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 23, 2004

MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data - Page 8

By William Pearson

Creating a Data-Driven Subscription

Assuming that preparation has been accomplished, we can easily create and modify Data-Driven Subscriptions through the Data-Driven Subscription pages provided within Report Manager, where we are walked through the steps required. Similarly to the Standard Subscription, with which we worked in our last article, access to any Data-Driven Subscription that we create can be obtained either through the My Subscriptions page, or the Subscriptions list of the report concerned, as we shall see once we create our new subscription.

In this section, we will discover how the data store we have created, together with the query needed to return source data from that store, is used by the Data-Driven Subscription process. It will become evident why these components need to be in place as we progress. We will begin in the steps that follow, first selecting the report that we need to satisfy the business requirements of our audience.

Having selected the report for which we will establish a Data-Driven Subscription, let's begin creating the subscription with the next steps.

Define the Data-Driven Subscription

1.  Click the Subscriptions tab atop the FoodMart Sales report view.

The Subscriptions page appears, showing any existing subscriptions in place, in a manner similar to that shown in Illustration 32 (which depicts the Standard Subscription on my server that we created in our last article).

Illustration 32: The Subscriptions Primary Page (Compact View)

2.  Click New Data-driven Subscription (the link circled in Illustration 32 above).

NOTE: This button is not enabled if stored credentials are not defined / addressed for the report.

We arrive at the first of seven Create a data-driven subscription pages, from which we will be led, in a wizard-like fashion, to input the settings and select the options that define our Data-Driven Subscription.

3.  In the Description box, enter the following:


4.  Select Report Server File Share as the delivery method.

This specifies the delivery extension for distribution of the subscribed report. (We will revisit delivery extensions in subsequent articles).

5.  Under Specify a data source that contains recipient information, click the radio button to the immediate left of Specify for this subscription only.

The data source connection is used to retrieve subscriber data, for which we have created a basic database, RS_Subscription, for purposes of our present exercise. As we discussed as part of the preparatory setup, the subscriber data consists of subscriber names, designated file share "addresses," subscriber identification numbers, subscriber preferences for export formats (such as PDF, MS Excel or HTML), and specific parameter settings for each subscriber's version of the report.

While we can choose a shared data source here (which can be established in Reporting Services for our subscription database, just as we can establish a shared data source for any data store upon which we base a report), we will make the source specific to this subscription only at present.

The Step 1 - Create a data-driven subscription: Foodmart Sales page appears as shown in Illustration 33, with our settings.

Illustration 33: The Step 1 - Create a Data-driven Subscription: Foodmart Sales Pagep>

6.  Click Next.

We arrive at Step 2 - Create a data-driven subscription: Foodmart Sales.

7.  In the Connection Type selector box, choose Microsoft SQL Server.

8.  Type (or cut and paste) the following into the Connection String box:

Data Source=localhost;Initial Catalog=RS_Subscription

NOTE: Make any modifications to the string to customize it to your own data source. The above reflects my settings, which access the source we created earlier, as it appears on my local PC.

9.  Input the appropriate user credentials, clicking Use as Windows credentials when connecting to the data source, if appropriate.

The Step 2 - Create a data-driven subscription: Foodmart Sales page appears as shown in Illustration 34, with our settings.

Illustration 34: The Step 2 - Create a Data-driven Subscription: Foodmart Sales Page

10.  Click Next.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM