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

Wednesday Jun 23rd 2004 by William Pearson
Share:

Build a Data-Driven Subscription with File Share Delivery for reports. Bill Pearson focuses on this largely undocumented method, versus the ubiquitous E-mail Delivery example.

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

1.  Right-click the Start button on the PC.

2.  Select Explore from the context menu that appears, as shown in Illustration 1.


Illustration 1: Select Explore ...

Windows Explorer appears.

3.  Navigate to the location to which you wish to install the folders (I chose the "D:" drive on my PC), to which the subscribed report files in our practice example will be delivered.

4.  Create a folder with the following name:

 RS_SubscriberShares

5.  Create folders within the new RS_SubscriberShares folder, with the following names:

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:


Illustration 2: The File Shares ...

6.  Close Windows Explorer.

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.



1.  Click Start.



2.  Navigate to the Microsoft SQL Server program group that installs within a typical setup. The equivalent on my PC appears as shown in Illustration 3.



Click for larger image

Illustration 3: Navigate to Enterprise Manager ...

3.  Click Enterprise Manager (shown circled above) to initialize the application.

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


Illustration 4: In the Enterprise Manager Management Console (Compressed View)

4.  Expand the MSSQL Servers group object by clicking the "+" sign to its immediate left.

5.  Expand the SQL Server Group object that next appears.

6.  Expand the server to which you intend to connect next (mine appears as LOCAL), as depicted in Illustration 5).


Illustration 5: Expand the Selected Server (Compressed View)

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

7.  Right-click the Databases folder under the selected server.

8.  Select New Database on the context menu that appears, as shown in Illustration 6.


Illustration 6: Select New Database ...

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

9.  Type the following into the Name box on the General tab:

RS_Subscription

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


Illustration 7: Database Properties Dialog - General Tab

10.  Click the Data Files tab.

11.  Ascertain that the default location is satisfactory for the placement of the new database. If not, make appropriate changes.

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


Illustration 8: Database Properties Dialog - Data Files Tab

12.  Click the Transaction Log tab.

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


Illustration 9: Database Properties Dialog - Transaction Log Tab

13.  After making any necessary adjustments specific to your environment, click OK to create the new RS_Subscription database.

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

Click for larger image

Illustration 10: The New Database Appears (Circled)

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

14.  Expand the RS_Subscription database by clicking the "+" sign to its immediate left, from the left pane in the Management Console, as shown in Illustration 11.

Click for larger image

Illustration 11: Expand the RS_Subscription Database

15.  Right-click the Tables node within the expanded database.

16.  Select New Table ... from the context menu that appears, as depicted in Illustration 12.


Illustration 12: Select New Table ...

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

17.  Add the columns and details depicted in Table 1 to the appropriate places in the Table Designer grid.

Column Name

Data Type

Length

Allow Nulls

Description

Participant

Varchar

50

Unchecked

Subscriber Name

File_Share

Varchar

165

Unchecked

File Share Qualified Name

Identification_No

Varchar

50

Unchecked

Associate ID Number

Media_Format

Varchar

50

Unchecked

Report File Type

Parameter

Varchar

50

Unchecked

Filter Parameter

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.


Illustration 13: The Completed Table Designer Grid

18.  Click the Save icon, depicted in Illustration 14, to name and save the new table.


Illustration 14: The Save Icon

The Choose Name dialog appears.

19.  Enter the following as the table name:

SubscriberDetails

The Choose Name dialog appears as shown in Illustration 15.


Illustration 15: The Completed Choose Name Dialog

20.  Click OK.

21.  Close the Table Designer grid by double-clicking the Enterprise Manager icon to the left of the Save icon (circled in Illustration 16).

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.


Illustration 16: Closing Table Designer

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


Illustration 17: The New Table Appears ...



Populate the Table with Query Analyzer and Create a Select Query



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



1.  From Enterprise Manager, with the new table highlighted / selected, select Tools -> Query Analyzer from the main menu, as depicted in Illustration 18.



Click for larger image

Illustration 18: Select Query Analyzer ...



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




Illustration 19: Query Analyzer Appears, with the RS_Subscription Database Selected

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

2.  Type (or cut and paste) the following script into the Query pane:

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


Illustration 20: Our Script in Query Analyzer

3.  Click the Execute Query button, shown in Illustration 21.


Illustration 21: Click the Execute Button ...

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.

4.  Save the script, if desired, by clicking the Query pane (versus the Results pane) and selecting File --> Save As from the main menu, and selecting an appropriate name for the .sql file, along with a convenient location.

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.

5.  Click the New Query button (see Illustration 22) in the main toolbar to open a new Query pane.


Illustration 22: Click the New Query Button ...

6.  Type the following simple query into the Query pane:

SELECT * 
FROM
dbo.SubscriberDetails

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


Illustration 23: Our Query in Query Analyzer (Compressed View)

7.  Click the Execute Query button, once again.

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


Illustration 24: Query Results in Grid View (Compressed View)

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.

8.  Save the SELECT statement by clicking the Query pane (once again, versus the Results pane) selecting File --> Save As from the main menu, and selecting an appropriate name for the .sql file, along with a convenient location.

Select the Report to be Subscribed, and Store Credentials

1.  Click Start.

2.  Navigate to the Reporting Services program group that installs within a typical setup. The equivalent on my PC appears as shown in Illustration 25.

Click for larger image


Illustration 25: Navigate to Report Manager ...

3.  Click Report Manager (circled in Illustration 25 above), to initialize the application.

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.


Illustration 26: Alternative Access to Report Manager ...

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


Illustration 27: Report Manager, Home Page, Content Tab (Folder View)

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

4.  Click the link for the FoodMart Sales report, as shown in Illustration 28.


Illustration 28: Accessing the FoodMart Sales Report (Partial Home Page, Content Tab View)

5.  Select a value for the parameter in the ProductFamily selector box (atop the view), if necessary.

6.  Click View Report, as required.

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.

7.  Click the Properties tab at the top of the report page, as shown in Illustration 29.


Illustration 29: Properties Tab for the FoodMart Sales Report (Partial View)

The Properties page appears.

8.  Click the Data Sources link in the left margin of the page, shown in Illustration 30.


Illustration 30: Click the Data Sources Link on the Properties Page (Partial View of Page)

We arrive at the Data Sources page.

9.  Select the radio button labeled A custom data source.

10.  Select OLE DB in the Connection Type selector.

11.  Add the following (either type or cut and paste) in the Connection String box (if it is not there from a previous lesson):

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.

12.  Under Connect Using, select the Credentials stored securely in the report server radio button.

13.  Type an appropriate User name and Password in the respective boxes.

14.  Ensure, if appropriate to your environment, that the Use as Windows credentials checkbox is checked.

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


Illustration 31: The Completed Data Sources Page (Compact View)

15.  Click the Apply button at the bottom of the page to accept and save changes.

16.  Click the View tab, to return to the Report view.

17.  If required, select a ProductType once again in the parameter selector, and click View Report, to run the report again.

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

1.  Click the Subscriptions tab atop the FoodMart Sales report view.

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


Illustration 32: The Subscriptions Primary Page (Compact View)

2.  Click New Data-driven Subscription (the link circled in Illustration 32 above).

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.

3.  In the Description box, enter the following:

FoodMartSalesReport_Data-Driven_Subscription

4.  Select Report Server File Share as the delivery method.

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

5.  Under Specify a data source that contains recipient information, click the radio button to the immediate left of Specify for this subscription only.

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.


Illustration 33: The Step 1 - Create a Data-driven Subscription: Foodmart Sales Pagep>

6.  Click Next.

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

7.  In the Connection Type selector box, choose Microsoft SQL Server.

8.  Type (or cut and paste) the following into the Connection String box:

Data Source=localhost;Initial Catalog=RS_Subscription

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

9.  Input the appropriate user credentials, clicking Use as Windows credentials when connecting to the data source, if appropriate.

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


Illustration 34: The Step 2 - Create a Data-driven Subscription: Foodmart Sales Page

10.  Click Next.

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.

11.  Type (or cut and paste, either from below, or from the .sql file we tested and saved in the earlier Populate the Table with Query Analyzer and Create a Select Query section) the following simple query into the Query pane:


SELECT * 
FROM
dbo.SubscriberDetails

12.  Leave the Specify a time-out for this command setting at default.

13.  Click the Validate button at the bottom of the page.

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.


Illustration 35: The Step 3 - Create a Data-driven Subscription: Foodmart Sales Page

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.

14.  Click Next.

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.

15.  In the File Name section, near the top of the page, select the radio button to the immediate left of Specify a static value.

16.  Type the following into the Specify a static value box:

FoodMart_Sales_Report

17.  Under File Extension, select the radio button to the left of Specify a static value.

18.  Select True in the selector to the right of Specify a static value.

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.

19.  Under Path, select the radio button to the left of Get the value from the database.

20.  Select File Share (the column in our SubscriberDetails table where we specify the respective UNC file share locations for our subscribers) in the selector to the right of Get the value from the database.

21.  In the section below, Render Format, select Get the value from the database via its radio button again.

22.  Select Media Format in the selector to the right of Get the value from the 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.

23.  In the next section of the page, User Name, select the radio button to the immediate left of Specify a static value.

24.  Type a valid MSSQL Server User Name into the box to the right of Specify a static value.

I used my own User Name, as I am an MSSQL Server Administrator on my PC, which uses integrated NT security.

25.  Under Password, select the radio button to the left of Specify a static value.

26.  Type the password associated with the User Name above into the box adjacent to Specify a static value.

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.

27.  In the last section of the page, Write Mode, select the radio button to the immediate left of Specify a static value.

28.  Select Overwrite in the selector to the right of Specify a static value.

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.


Illustration 36: The Step 4 - Create a Data-driven Subscription: Foodmart Sales Page

29.  Click Next.

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

30.  Select the radio button to the left of Get the value from the database.

31.  Select Parameter (the column in our SubscriberDetails table where we specify the "fill in" for the report parameter, for each respective subscriber) in the selector to the right of Get the value from the database.

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


Illustration 37: The Step 5 - Create a Data-driven Subscription: Foodmart Sales Page

32.  Click Next.

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.

33.  Click the radio button to the left of On a schedule created for this subscription, as depicted in Illustration 38.


Illustration 38: The Step 6 - Create a Data-driven Subscription: Foodmart Sales Page

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.

34.  Click Next.

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.

35.  Click the radio button to the left of Hour in the Schedule details section.

36.  Specify a time that is a few minutes away, if convenient. (I chose five minutes later than my present time when inputting the time initially).

37.  Assign today in the Begin running this schedule on box, in the Start and end dates section of the page (the calendar icon allows selection of a date, or it can be typed in).

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


Illustration 39: The Step 7 - Create a Data-driven Subscription: Foodmart Sales Page

38.  Ensure that MS Excel is not open.

39.  Click Finish

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.


Illustration 40: Status of the Data-driven Subscription (Post-Processing)

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.

40.  Click My Subscriptions to open the My Subscriptions page.

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


Illustration 41: The My Subscription Page

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.

41.  Return, via the Edit link, to reset the subscription settings you have made (that is, the recurrence settings, etc.), or perhaps take steps to delete the Data-Driven Subscription entirely, as desired.

42.  Close Report Manager, after reviewing the steps we have accomplished in this lesson - and after experimenting further, as desired.

Verify the Results of the Data-Driven Subscription

Seeing is believing, right? Let's take a quick look at the report files that our labors have produced.

1.  Go to the file shares that we designated as the Path in the SubscriberDetails table that we created in preparation for the Data-Driven Subscription we created above.

We see, in each of the subscriber folders we created, that a file of the specified type has been placed, as shown in Illustration 42.

Click for larger image

Illustration 42: The Report Files, as Generated and Stored (Composite Presentation)

2.  Click / double-click the files, as appropriate to your PC's settings, to open them for examination, as desired.

The files open, and present the appropriate rendition of the report, assuming that the program(s) with which each file type can be associated is onboard the PC we are using. Note that the image file is a .tif extension that opens in many image editors and graphics packages.

Keep in mind that interactivity is, of course, lost when we choose to deliver reports in this manner, but this is still a very popular option for distribution, depending upon the need. We do, however, obtain a "drilled view" in Excel automatically (an example partially appears in Illustration 43), which many find useful.


Illustration 43: The Excel Rendition of Our Subscription (Partial View)

We note that the parameters, too, have been passed correctly in the results that we obtain. Further, the formats appear to be intact, a scenario that many of us will find pleasantly surprising after years of suffering under less amiable circumstances with the "Big Sisters." (As I stated in my previous article, I have heard, over the years, hundreds of individuals complain about issues with this particular capability in their experiences with Business Objects, Cognos and the rest of their older generation enterprise reporting family. This is only a tiny, symbolic vestige of the advantages that Reporting Services promises those organizations that convert!)

While our exposure to Data-Driven Subscriptions has focused on the creation of a Data-Driven Subscription, with delivery via File Share, it is important to remember that, as with standard subscriptions, far more can be accomplished with Data-Driven Subscriptions, including the following:

  • Creation of a subscription to deliver reports via e-mail to individuals and / or groups (this is the "typical" use of subscriptions - and, unfortunately, the only type for which any hands-on, procedural documentation has been published, at this writing);

  • Bypass of browser viewing and delivery of reports to file shares for alternative viewing (similar to the example we performed in this lesson), both for individuals and for groups. This is often an effective strategy in the case of large reports that might be time consuming to load in a browser, etc. It might also offer options for remote users and other consumers "outside the organization," to which perhaps we would prefer to "push" selected data, rather than allowing them access to corporate networks, etc.

  • Consumer selection of various file formats for offline viewing (which would then be entered into the subscriber database);

  • Automated report archiving, established via file shares.

Summary and Conclusion ...

In this article, we continued our introduction to the Managing phase of the Reporting Services development life cycle, and introduced the topic of Data-Driven Subscriptions. First, we devoted time to preparation of a sample subscriber data source, to illustrate in detail the types of contents we might house in such a source to support our Data-Driven Subscriptions. We then completed preparation steps, and discussed conditions that must be in place before establishing a Data-Driven Subscription, as a prelude to beginning our hands-on creation exercise.

Next, we focused upon the setup and operation of a straightforward Data-Driven Subscription, throughout our setup of the subscription, with delivery via Report Server File Shares. We discussed the abundant options within this powerful capability, as well as the details of the steps we took to bring it to fruition. Throughout our session, we covered several settings in more detail than we can easily find in existing documentation. Finally, in our concluding comments, we discussed additional capabilities and uses that Data-Driven Subscriptions offer Reporting Services practitioners and consumers in meeting their analysis and reporting objectives.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved