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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles