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 2

By William Pearson

Data-Driven Subscriptions: Introduction and a Scenario

As we learned in the introduction to standard subscriptions in Managing Reporting Services: Report Execution and Standard Subscriptions, when we establish subscription-based reporting, our designated report server uses information that we provide it to schedule and deliver the report to designated individuals in the organization, through the channels that we also specify. As we are about to learn, Data-Driven Subscriptions are established as "standing orders" to Reporting Services, just like Standard Subscriptions, to deliver reports in one of two ways: a subscription is based upon either an event or a schedule.

The two subscription types share the same general purpose: to provide information to organizational consumer(s) without requiring the targeted consumer(s) to perform any action to obtain an updated report. This "push" capability, in the context of the Data-Driven Subscription, is useful in establishing reporting based upon data, and other events, whereby a designated user can be informed of the occurrence of the event with little additional effort, freeing them to pursue other activities in the meantime. We will focus on specification of scheduling report generation in our practice exercise, but suffice it to say for now that Data-Driven Subscriptions, just like their Standard Subscription counterparts, can provide highly useful, automated delivery of updated information to intended consumers in a reliable manner.

The actual difference in how the two types of subscriptions operate lies largely in the source of the "destination instructions." Standard Subscriptions, whose delivery instructions are a static part of the report definition file, are usually established and maintained by the information consumers for whose benefit they are created. Data-Driven Subscriptions, on the other hand, generate their subscriber lists, along with other delivery options, when they are executed. They are typically created and maintained by Reporting Services administrators and other operatives with familiarity in report creation and operation.

Data-Driven Subscriptions allow us to determine to whom we deliver reports at the runtime of the report. Delivery to a designated list of subscribers, based upon the list itself, allows us a wide range of flexibility, including customization of our information products for specified recipients at report run time. An obvious benefit of having a list-based delivery mechanism is that distribution of the report remains tied to a subscription list that can change in composition on a regular basis. We might, as an example, leverage a Data-Driven Subscription to deliver a weekly general ledger update to members of a corporate accounting group, or to update personnel with approaching milestone employment anniversaries that various election forms are due to the HR department by a given date.

In Managing Reporting Services: Report Execution and Standard Subscriptions, we explored Standard Subscriptions, and performed a practical exercise in setting such a subscription in place to deliver reports, via a file share, and in an MS Excel format. As we shall see, the primary differences between a Standard Subscription and a Data-Driven Subscription lie in the source of the subscription information that is evaluated by the process. Whereas this information is largely fixed for a Standard Subscription within the subscription definition, the Data-Driven Subscription looks to both the subscription definition (for the fixed facets) and a specified data source (for the dynamic aspects) for its operational instructions.

The fixed instruction elements of a Data-Driven Subscription consist of the following:

  • The specific report that is subscribed;
  • Delivery extension information;
  • Subscriber data external source connection information;
  • A query to extract the dynamic data that drives the subscription.

The query returns a rowset each time that the Data-Driven Subscription is processed. The retrieved data supplies the dynamic components of the subscription, including the:

  • Subscriber list;
  • User-specific delivery preferences;
  • Parameter values, where appropriate.

Let's continue our overview of the Managing phase, and get some hands-on experience with a Data-Driven Subscription. Because this article focuses on management of reports that are already designed (and in keeping with our objective to make articles "free-standing" with regard to readers being able to participate in each without having joined us in previous articles), we will continue to work with the set of report samples that accompany Reporting Services.

The Business Requirement

Before we begin building the Data-Driven Subscription mechanism, which is really quite similar to the steps we took for the Standard Subscription in Managing Reporting Services: Report Execution and Standard Subscriptions, let's examine a hypothetical business need to embed a degree of reality into the picture. Again, we will make the requirement very similar to the requirement in our last article, so as to emphasize likenesses, and distinguish differences, in a manner that makes the concepts memorable.

We will return, for purposes of illustration, to the business requirement in our last lesson, to establish a subscription whereby we will "push" the FoodMart Sales report to a designated group of information consumers. We will assume again that, up until our establishment of the subscription, the report was regularly distributed manually to users, by attaching the respective report file to individual e-mails, and mailing these, once this was accomplished.

While we might certainly take the existing e-mail approach in a more automated scenario within either type of subscription (and, indeed, the documentation on the circuit at this early stage in the life of Reporting Services has focused exclusively upon this approach), we will again state that the information consumers have requested that the report files be "parked" in a shared folder that interested parties can access at will (this saves them from having to refer to an e-mail at a later time, assuming that they are too busy to review the report when it arrives in the in-box, as in the e-mail scenario.) The members of the prospective audience tell us that the report would be most useful, for purposes of analysis, as the specific type of file with which each respective consumer has a fondness. The report, which will be the same as the one we selected for the Standard Subscription, is parameterized, a fact that we will leverage in our exercise, because the intended audience consists of managers who are (again) concerned with how specific product families are faring within the FoodMart chain, and have little interest in members of the other product families. The ability to customize the selection parameter to individual subscriber's needs presents a distinct advantage in using a Reporting Services Data-Driven Subscription.

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