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.

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.

Preparation for Creating a Data Driven Subscription

Before we begin the setup of a Data-Driven Subscription in a report, we need to ensure that the following conditions are in place. They are likely to be easily accommodated for our ends, assuming that settings have not been altered in the sample reports / environment. Keep in mind the realities of bringing these items into existence in a large organization and / or and environment where we may not have the privileges, access, or overall authority to institute them quite so easily, and plan in advance.

• We must have access to / permission to view the report that we wish to establish as subscribed;
• We must have capability within the context of our role assignment to create subscriptions;
• The report must either require no credentials to run, or have the necessary credentials stored within its structure;
• The report server must be configured to use the appropriate extensions, depending upon the way wish to deliver the report (this will not be an issue here, as we will be using one of the two "out of the box" options);
• The SQL Agent Service must be in operation;
• An acceptable, properly configured, accessible data source must exist that contains subscriber information.

NOTE: We will cover these topics from numerous perspectives as we progress in the series. For purposes of this article, we will create the subscriber database in the following section.

Preparation - Create File Shares and a Subscriber Data Source

Let's create a source for the subscriber information that we will need to access as a step in the general setup later. We will create a small database with a table that contains data values that the report server will retrieve at scheduled runtimes, when the subscription information is processed. In the real world, this source will likely already exist, and, in any event, will likely be far larger, as most of us can imagine. In addition to creating a subscriber data source to allow us to successfully complete the steps of creating a Data-Driven Subscription, we will also need to have file shares in place, whose qualified names we will insert into the table we create in the next section.

Create the File Shares to Which Report Files Will Be Delivered

Windows Explorer appears.

 RS_SubscriberShares

AdamsV

DamascusB

DhueL

PearsonW3

These folders will represent file shares for a group of employees within the hypothetical organization in our practice example. Obviously, the real-life location of such file shares would be on the network somewhere, with the appropriate access rights assigned to the associated users. Alternatively, we might just as easily have created departmental, or other, shares for groups of users, and so forth, depending upon the needs and resources of the organization.

The newly created file shares appear similar to those depicted in Illustration 2:

Having created the file shares to which we will direct the delivery of the report files, and which we will specify within the subscription settings, we are ready to take the steps to create a database and table to contain the information that is required to establish our Data-Driven Subscription.

Create the Database and Table in Enterprise Manager

Let's go to MSSQL Server 2000 Enterprise Manager ("Enterprise Manager") to create our new source.

We arrive within the Enterprise Manager Management Console, as shown in Illustration 4.

NOTE: The registered servers and other objects will appear different in this view on your own PC, and will depend upon your environment.

The Database Properties dialog appears, defaulted to the General tab.

RS_Subscription

The Database Properties dialog - General tab appears as shown in Illustration 7.

The Database Properties dialog - Data Files tab for my server (I left all at default) appears as shown in Illustration 8.

I again left my settings at default; the Database Properties dialog - Transaction Log tab for my server appears as shown in Illustration 9.

The new database appears among any other databases that were already in existence, as shown in Illustration 10.

Now let's create a table to contain our Subscriber information.

The Table Designer appears. Here we can easily create columns and other table details.

Table 1: New Table Construction Details

Once the information in the table is entered, the Table Designer grid will appear as shown in Illustration 13.

The Choose Name dialog appears.

SubscriberDetails

The Choose Name dialog appears as shown in Illustration 15.

NOTE: Be sure to double-click the correct icon - the one on the Table Designer bar, not the one on the Management Console bar, as is noted in Illustration 16.

Once Table Designer closes, we see the new SubscriberDetails table, among the system tables of the RS_Subscription database, as shown in Illustration 17.

Populate the Table with Query Analyzer and Create a Select Query

Let's populate the new SubscriberDetails table by taking the next steps.

Query Analyzer opens, selected to the new RS_Subscription database, as shown in Illustration 19.

NOTE: If necessary, select the RS_Subscription database in the selector, as shown circled in Illustration 19 above.

-- RS006:  INSERT Query to Populate SubscriberDetails Table



INSERT INTO 

   dbo.SubscriberDetails (Participant, File_Share, Identification_No, Media_Format,       

      Parameter)

VALUES 

   ('Damascus, Billy','\\MOTHER1\D$\RS_SubscriberShares\DamascusB','10059','EXCEL', 

      'Non-Consumable')

GO



INSERT INTO 

dbo.SubscriberDetails (Participant, File_Share, Identification_No, Media_Format, 

   Parameter)

VALUES 

   ('Adams, Victoria','\\MOTHER1\D$\RS_SubscriberShares\AdamsV','32981','PDF', 'Food')

GO



INSERT INTO 

   dbo.SubscriberDetails(Participant, File_Share, Identification_No, Media_Format, 

      Parameter)

VALUES 

   ('Pearson, William E. III','\\MOTHER1\D$\RS_SubscriberShares\PearsonW3','04089','HTML4.0', 

      'Non-Consumable')

GO



INSERT INTO 

   dbo.SubscriberDetails(Participant, File_Share, Identification_No, Media_Format, 

      Parameter)

VALUES 

   ('Dhue, Laurie','\\MOTHER1\D$\RS_SubscriberShares\DhueL','14761','IMAGE', 

      'Non-Consumable')

GO

Our load script appears in the Query pane as depicted in Illustration 20.

The script executes, and we receive a "(1 row(s) affected)" message for each of the four lines of the script in the Results pane.

We can easily verify the fact that the table is populated by going to Enterprise Manager, but since we will need to create a query to supply necessary data to the Data-Driven Subscription we will define later, let's use this query to ascertain the fact that all data is in place in the SubscriberDetails table.

SELECT * 

FROM

dbo.SubscriberDetails

Our query appears in the Query pane as depicted in Illustration 23.

The script executes, and the Results pane is populated (Grid View), as shown in Illustration 24.

We thus obtain confirmation from the results of the SELECT statement that our table has been populated as we expected. We will save the query, so as to be able to use it in our Data-Driven Subscription setup later.

Select the Report to be Subscribed, and Store Credentials

NOTE: If Report Manager does not appear as a menu item in the manner shown, whether because you declined setup of the program group, a disablement of the feature, or other, unknown reason, simply get there by typing the appropriate URL into the address bar of your web browser. The default URL is as follows:

http://<webservername>/reports

As an example, my <webservername> would be MOTHER1, the name of my server, and would appear, in this approach, in my browser address line as depicted in Illustration 26.

We arrive at the Report Manager, Home page, Content tab (Folder view), as shown in Illustration 27.

The sample reports we have published appear above as we left them in Managing Reporting Services: Data Connections and Uploads. Minor changes in the reports since then will not affect this lesson - we will be working with FoodMart Sales in our practice example throughout the lesson. (If you are just joining us with this lesson, simply refer to the upload process in the article, which we have cited.)

The report executes, as we have discussed earlier, either when we click the link in Report Manager's Folder view, or upon clicking the View Report button, and we again see the Report is being generated message. The report then appears, with the parameter selector dropdown list, set at default, appearing atop the report.

NOTE: If a default had appeared in the ProductFamily selector (this is how the sample FoodMart Sales report is configured upon installation) upon entering the report, the report would have executed automatically. Note that, as we proceed through the steps that follow, some of the settings we prescribe may have been left in place (by those readers, at least, who completed the steps of our last article). We repeat the relevant steps here, so as to keep within our "standalone" objective for the articles of this series.

Let's store credentials at this stage, to allow the report to access data without interruption when it is executed by the Data-Driven Subscription process.

The Properties page appears.

We arrive at the Data Sources page.

Provider=MSOLAP.2;Client Cache Size=25;Data Source=localhost;

	Initial Catalog=FoodMart 2000;Auto Synch Period=10000

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

The Data Sources page appears similar to that shown in Illustration 31, with our modifications.

Successful execution of the report verifies that the credentials we have supplied are adequate to support the report's operation. This finalizes our general preparation for the steps to create the Data-Driven Subscription. We will proceed to establish the subscription itself in the next section.

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

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).

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.

FoodMartSalesReport_Data-Driven_Subscription

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

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.

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

Data Source=localhost;Initial Catalog=RS_Subscription

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

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

This is the point at which we enter the query we defined and tested earlier, whose purpose is to retrieve subscription data from the RS_Subscriber database (and the SubscriberDetails table within the database), which we have constructed to hold the Subscriber information.

The simple query we constructed earlier will return a result set that contains one row for each of the subscription recipients we have defined. As we will see on subsequent pages, the dataset is used to populate Data-Driven Subscription extension settings.

SELECT * 

FROM

dbo.SubscriberDetails

A Query validated successfully message appears (in green lettering) in the bottom left corner of the page. The Step 3 - Create a data-driven subscription: Foodmart Sales page appears as shown in Illustration 35, with our settings.

NOTE: If you did not obtain indication of a successful validation as shown, check your query, then perhaps the steps setting up the database and table, if the query itself is not the issue.

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

Here we specify the fields in our SubscriberDetails table that we will use to supply the respective requirements in the Data-Driven Subscription we are establishing. One of the obvious advantages in pointing the subscription to a table in this way is that the subscription will change to reflect changes in the composition of the data in the database. This means that we can manage large lists of subscribers, who might come and go regularly, as well as assorted other details of the subscription, without modifying anything except the central database.

FoodMart_Sales_Report

We will be using a "built-in" extension for File Share Delivery, and thus will not be required to retrieve this information from the RS_Subscriber database.

Recall that the Media Format column in our SubscriberDetails table houses the format type, such as Excel, PDF, HTML and Image, for each respective subscriber.

Keep in mind the distinction between the stored credentials of the FoodMart Sales Report and the credentials we have inserted above, which allow us to access the RS_Subscriber database, and the SubscriberDetails table we created therein.

This instructs Reporting Services to overwrite an existing file in the individual folders, to which we will be writing our report files. This is one option for how to handle, say, a scenario where Reporting Services is scheduled to deliver a file on a recurring basis, and meets with an identically named file when it attempts to do so.

While there is some flexibility here, and the setting will need to fit the need of the report consumers to whom the report is to be delivered, one way to ensure that the file is the "most current" is to simply write over any pre-existing file. (If consumers want to archive "snapshots in time," they can rename the file with a date, as one option, or simply move the files from the "landing folder" after receipt, but before the next scheduled report delivery. I have formulated many other approaches at various clients, including elaborate sweep mechanisms, and so forth).

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

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

On this page, we have the option to specify the last of the subscriber database references, the source of the parameter information for the Data-Driven Subscription. The potential for this capability is great, as we have the ability to specify (via the appropriate field in our database) the parameters that are applied to a report before it is delivered - for each individual subscriber! We can thus ensure that consumers receive only relevant information on a recurring basis, with parameterization of the report supporting virtually any sort of filtering we might imagine. This is a Reporting Services capability that, together with numerous others within the Data-Driven Subscription functionality, provides advantages over the capabilities of other enterprise reporting solutions. (Sure, you can accomplish this in many with macros or other coding, but this is about as "out of the box" as it gets.)

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

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

Finally, we have come to the scheduling portion of the Data-Driven Subscription setup process.

We thus elect to establish a schedule (versus an event) trigger for our new Data-Driven Subscription. We will specify the details on the next page.

We arrive at Step 7 - Create a data-driven subscription: Foodmart Sales, the last of the Data-Driven Subscription creation pages. Here we specify the processing schedule of the subscription, including time and starting / ending dates.

The next page that appears lists the subscriptions we have created, and, once the scheduled time for processing has passed, the various details about the most recent processing cycle (we may have to refresh the page at some point after the subscription was scheduled, in our last step, to run). Our entry should indicate that four reports have been processed, with no errors. The page with entry for our newly created and processed subscription should appear similar to that depicted in Illustration 40.

NOTE: If the subscription indicates errors, a good place to start to troubleshoot it is the ReportServerService_[DATE].log, although other trace logs are also available. See the Books Online for more information. I will also be addressing various troubleshooting and processing optimization topics in future articles in this series.

We can edit both Standard and Data-Driven Subscriptions from this page by clicking the Edit link appearing next to the respective subscription title, and walking through the setup pages to the point(s) we wish to modify.

The My Subscriptions page opens, and appears as depicted in Illustration 41.

We can also view, modify and delete existing subscriptions from the My Subscriptions page. This is a "maintenance" center, as it were, and does not provide the capability to create subscriptions. My Subscriptions typically also shows (depending, however, upon our security setup) only those subscriptions that we have created - not those that are created by other users.