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

About the Series …

This is the sixth article of the series MSSQL
Server 2000 Reporting Services. The series is designed to introduce
MSSQL Server 2000 Reporting Services ("Reporting Services"), with the
objective of giving a preview of its features, as well as sharing my conviction
in its role as a new paradigm in enterprise reporting. As I advise clients on a
more and more frequent basis these days, this is the future in a big way. I hope
you will consider my input valuable, and that you will investigate closely the
savings and advanced functionality that will soon be available to anyone with
an MSSQL Server 2000 (and beyond) license.

Important: For information
concerning the applications to which you will require access to benefit the
most from our series, please see our initial Database Journal
article, A New Paradigm for Enterprise Reporting.

For purposes of this article, we assume that
you have uploaded the sample reports that ship with the current version of
Reporting Services. For a detailed upload procedure, see Managing Reporting Services:
Data Connection and Uploads, where we uploaded the reports to which we refer in this
and several subsequent articles.

For
many of the articles in this series, it is also assumed that you have prepared
security to allow "power user" status in virtually every regard. For
details on the specifics of the adjustments necessary to quickly allow full
freedom to complete the exercises in this and subsequent articles, as well as
important assumptions regarding rights and privileges in general, please see
the section titled Preparing Security for Our Exercises in our previous article, Managing
Reporting Services: Report Execution and Standard Subscriptions.

Introduction

In our last article, Managing Reporting Services: Report Execution and
Standard Subscriptions, we continued
our introduction to the Managing phase of the Reporting Services
development life cycle, and introduced the main topics of report execution and
viewing, together with standard subscriptions. We reviewed the process
of running reports, partially touched upon in our last session, and then we
discussed directly viewing reports from a web browser.

Next, we focused upon
the setup and operation of a basic standard subscription, discussing
conditions that must be in place prior to establishing a standard
subscription, before beginning our hands-on creation exercise. Throughout
our setup of a standard subscription,
with delivery via report server
file share, we discussed the options
that abound within this robust capability, as well as the details of the steps
we took to bring it to fruition. Finally, in our concluding comments, we
discussed additional options that standard subscriptions offer
knowledgeable Reporting Services developers and users.

In this article, we
will introduce the details of another option within the Managing phase
of the reporting lifecycle, the Data-Driven Subscription. To examine the
steps involved in creating a Data-Driven Subscription effectively, we
will need to create an environment that will support the scenario we will
encounter in our practice example, where we will satisfy a hypothetical
business need as expressed by information consumers. In meeting this need, we
will perform the following general procedures:

• Introduce the Data-Driven Subscription,
  together with an illustrative business need that we will satisfy in our
  practice example;

• Discuss the basic "prerequisites" we must
  meet to be able to establish a Data-Driven Subscription.

• Take appropriate steps to prepare for an in-depth
  practice exercise, including:
  • Creation of "target" file
    shares;

  • Creation of a Subscriber Information
    data source to support the subscription;

  • Design and creation of a Subscriber
    Data query;

  • Selection of the report to be subscribed;

  • Storage of Credentials for the selected report.
• Performance of Data-Driven Subscription setup,
  including:
  • Selection of Delivery Method
    and Details;

  • Connection to the Subscriber Data source;

  • Association of the Subscriber
    Data query with the subscription definition;

  • Assignment of a Parameter value
    for subscribers;

  • Designation of trigger
    for the subscription;

  • Specification of Scheduling details.
• Execution of our new Data-Driven Subscription;
• Verification of the creation and proper placement of the report files to the
  appropriate file shares.

Whenever possible, we
will make our hands-on practice example resemble the requirements of the
practice exercise we performed in Managing
Reporting Services: Report Execution and Standard Subscriptions, so as to provide the tandem benefits of
comparison and contrast in activating the subject matter in our minds. Once we
have completed our setup, and allowed for one or more operational cycles, we
will verify the results of our handiwork.

The general objective
of this article is the same as the two Managing phase overviews that have
preceded it: to continue our overview of Managing in Reporting Services,
specifically within the context of the central management of the reporting
function, from the perspective of the objects that we can manage, and the
actions that we can perform with, and upon, those objects. We will return to
most of the activities we touch upon here, just as we will return to many of
the topics we explore within our other life cycle overviews, as we get involved
in creating reports to accomplish illustrative business needs.

As I have mentioned
before, our series will address enterprise reporting in a wide sense. Throughout
the articles, we will exploit Reporting Services as a unified, common platform
from which I can share the techniques and methods I have accumulated during my years
as an Architect, supporting robust and creative business intelligence. As I
have proclaimed since beta testing Reporting Services, the future for
enterprise reporting includes commoditization, and this is a tremendous
step in offering powerful capabilities at a cost that is far less than that of
the predecessor group of "end-to-end BI solutions" that have
dominated the BI market for many years.