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

By William Pearson

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.


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.

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