About the Series ...

This is the fifth 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 and subsequent articles, 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 subsequent articles.

Introduction

In our last article, Managing Reporting Services: Data Connection and Uploads, we introduced Managing as the post-Authoring phase of the Reporting Services development life cycle. In beginning a set of Managing articles, we began by looking at data connections within Reporting Services. We set up a shared data source item for the sample reports set, both as practice and for supporting the reports for later uses within our series. We then practiced the upload process with each of the sample reports, to gain familiarity with the steps involved.

We then associated the sample reports with the new shared data source, for the purpose of returning the reports to a working status, as well as for the benefit of practicing a method of re-establishing connections to a data source, in cases where a previous connection has been deleted or altered. Finally, we verified each of the newly established data connections through initial report viewing, ensuring that each report brought back data as expected.

In this article, we will introduce two further considerations within the Managing phase of the reporting lifecycle: the execution of reports (including viewing options), and the creation and maintenance of standard subscriptions. We will review the process of running reports, partially touched upon in our last session, and then we will examine viewing reports from a web browser. We will then examine the steps involved in setting up a standard subscription as a means of delivering reports to information consumers.

The objective of this article is to continue our overview of Managing, and, as was the case in our previous article, the primary focus of the remaining Managing phase articles is 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 stated in the Authoring overview, I intend to make this a series on enterprise reporting in the widest sense. Reporting Services presents a unified, common platform from which I can show the techniques and methods I have accumulated over the years to support robust and creative business intelligence, and I look forward to sharing the knowledge I have accumulated in the Business Intelligence arena.

A Return to the Managing Phase

As we discussed in the introduction for Managing Reporting Services: Data Connection and Uploads, once we have completed the Authoring phase of the report development life cycle, the next phase is Managing, where the report is processed and rendered, (assuming, of course, that the reports concerned are managed in the first place). We noted, in general, that publication of the new report definition to a Report Server designates it as a managed report, and that managed reports are associated with meta data, and have properties, that allow actions to be taken with them. Examples of these actions include scheduling (one of the items we partially expose in this article), linking to other reports, application of security, movement to various locations, and various aspects of file maintenance.

We discussed processing as taking place in a general sequence that includes extraction of the data specified in the report definition, and the subsequent marriage of data and report layout, prior to the presentation of the data. In this article, we will begin an examination of the generation aspects of the reports we have uploaded from Report Manager, and then the viewing of those reports both from Report Manager and from the browser interface, together with other operations we can perform within Report Manager.

Let's continue our overview of the Managing phase, and get a deeper look into this exciting area within Reporting Services. 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.

Executing and Viewing Reports

We can execute a report within Reporting Services through two general means. First, we can run a given report on demand, which simply means that the report executes any time we access and open it. Alternatively, we can dictate that a report is subscription based, whereby we specify information that is used to run the report at the time we dictate, together with delivery information to enabled Reporting Services to "push" the report to us after execution.

Execution and viewing often become very closely related, particularly in the case of an on-demand scenario, where we are triggering execution by an attempt to view. Let's take a look at the options for executing and viewing our reports.

On-Demand Execution

We can view reports on demand via the Report Manager or through a browser. Let's take a look at the steps involved in a simple scenario. First, we will view a report using Report Manager, something we have already touched upon in previous articles. Our first step is to start Report Manager.

We arrive at the Report Manager Folder view, as shown in Illustration 3.

The sample reports we have published appear as we left them in our last lesson.

NOTE: If you did not complete the last lesson, of if you have worked with Reporting Services other than within the last lesson, the Folder view will appear different from the above. To populate the view with the sample reports, which we will be using throughout our examination of the Managing phase of Reporting Services, please refer to the steps taken in Managing Reporting Services: Data Connection and Uploads.

As is obvious, Report Manager is, itself, a web-based interface. In addition to serving as a management tool for Reporting Services, it also acts as a viewing tool. An advantage in its use for viewing is that the involvement of the report server, which underlies the capability to view a report in a web browser, is minimal. This comes in handy at times, particularly for developers who, for whatever reason, may not have access to a report server.

Let's execute and view a report here, then take a look at the same process via the browser. Clicking the report name triggers execution, as we shall see in the next steps.

The report executes when we click the link in Report Manager's Folder view, as evidenced by a Report is being generated message. The report then appears, as shown in Illustration 5.

We can also view reports directly from a browser, simply by typing in the URL address of the report. Within the URL is embedded the Web server, report server virtual directory, and the fully qualified report name. An example (the report we have accessed, based upon its URL on my machine), is as follows:

http://MOTHER1/Reports/Pages/Report.aspx?ItemPath=%2fProduct+Line+Sales

We could type this address directly into the browser, or establish a link in another page, a shortcut based upon the URL, or other options to gain similar execution and subsequent access to the report. The address I have shown above would be referred to in the Reporting Services documentation as a "complex URL," meaning that it contains encoded characters to handle spaces in the path (as in the report name Product Line Sales), as well as for the passage of parameters (Product Line Sales is a parameterized report, and requires parameters to function as its authors intended).

These comprise the basic options that we have for executing and viewing a report on demand. We can also view the data obtained by Reporting Services using a desktop application, such as MS Excel, instead. We will touch upon a method for doing this in our next section, where we will focus upon subscriptions.

On-demand reports, by their nature, allow us to perform "refreshes" based upon the most recent data in the data source through their easy execution, and thus they provide current information upon viewing. There are scenarios where a Reporting Services administrator might want to designate that a previously generated report be presented, instead of running the report every time an information consumer makes use of the respective link or other execution option. These previously generated reports are often referred to as static reports. Static reports might be used when the reports concerned are resource intensive, or perhaps where the use of static reports otherwise increases performance, in situations where data is not frequently updated at the database level, and so forth. We will examine the use of static reports in subsequent articles.

As we have mentioned earlier, in addition to on-demand reports, Reporting Services allows us to execute reports in an alternative way, through the use of subscriptions. We will look at subscription-based reporting next, from the perspective of a standard subscription.

Subscription-Based Reporting

Within the context of 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. Subscriptions are established as "standing orders" to Reporting Services to deliver reports in one of two ways: subscriptions are based upon either an event or a schedule.

One of the obvious benefits that subscriptions provide is the elimination of the need of the designated information consumer(s) to perform any action to obtain an updated report. This "push" capability can also be 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. Regardless of the trigger for report generation, subscriptions can provide highly useful, automated delivery of updated information to intended consumers in a reliable manner.

There are two kinds of subscriptions from which we can choose in Reporting Services, standard subscriptions and data-driven subscriptions. Standard subscriptions 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 when they are executed, along with other delivery options, and are typically created and maintained by Reporting Services administrators and other operatives with familiarity in report creation and operation.

Let's take a look at subscription basics in the following hands-on exercise steps. While our focus in this exercise will be the definition and operation of a standard subscription that delivers a single instance of an on-demand report, we will delve into data-driven subscriptions, which are a bit more involved, in a later article.

The Business Requirement

Let's say, for purposes of illustration, that we have been tasked with establishing a subscription whereby we will "push" the FoodMart Sales report to a designated group of information consumers. (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).

Ideally (according to the information consumers), the report will be "parked" in a shared folder that interested parties can access at will. The report would be most useful, for various analysis purposes, as an MS Excel spreadsheet. The report is parameterized, a fact that we will leverage in our exercise, because the intended audience consists of managers who are concerned with how Non-Consumable products are faring within the FoodMart chain, and have little interest in members of the other product families.

The report executes, as we have discussed earlier, when we click the link in Report Manager's Folder view, and we again see the Report is being generated message. The report then appears, defaulted to the Drink Product Family in the parameter selector dropdown list appearing atop the report. Let's set the parameter to the appropriate selection.

The report regenerates to accommodate the new parameter we have supplied, and appears as depicted in Illustration 8.

Our purpose here is merely to get an idea of "where we want to go," and to create a report against which we can compare our subscribed report, to ascertain that it is selecting the parameter that has been designated by the information consumers in the business requirement that we have received.

Before we begin the setup of a standard 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, but it is never too early to be exposed to real-world rules:

• We must have access to / permission to view the report;
• 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.

NOTE: We will cover these topics from numerous perspectives as we progress in the series.

Preparing Security for Our Exercises

We will make adjustments to ensure that the credentials requirement above presents no issue within our immediate exercise, assuming "out of the box" Reporting Services / sample reports setup. First, let's modify security to enable all capabilities from the outset, establishing ourselves as complete "power users" for this and subsequent lessons, rather than addressing all possible settings involved in each of the many exercises ahead.

NOTE: In establishing all rights and privileges to the BUILTIN\Administrators group in my example, I am assuming that you are 1) in a development or other non-production environment, 2) that you have authority to set security this way in your environment for purposes of our exercises, and 3) that you have all necessary accesses / capabilities to make and apply the modifications that we undertake. If your environment or other situational factors vary from these assumptions, and for specific roles for which you may need to perform modifications (perhaps other than BUILTIN\Administrators), please review the Books Online for guidance in the setup of security in a manner that will comply with your local requirements, while allowing you the privileges and accesses you need to effectively complete the steps we will undertake.

The Edit Role Assignment page appears.

The Edit Role page appears.

All checkboxes become checked for the Browser role, as shown in Illustration 11.

We have, in effect, given each role all its possible privileges, and are now assigning all roles to our security group. The intent here, again, is not simply to prepare for the steps ahead in this article, but to eliminate time-consuming adjustments for articles that will follow this one in the Reporting Services series.

The Edit Role Assignment page, all roles selected, appears as depicted in Illustration 12.

We are returned to the Home page - Properties tab.

We are now ready to undertake the creation of a standard subscription.

Creating and Using a Standard Subscription

We arrive at the Properties page.

We arrive at the Data Sources page. Here we will embed our data source access credentials, for purposes of completing the immediate exercise.

NOTE: If you are using a different authentication method than Windows integrated security, make the appropriate adjustments on this page to embed your credentials into the report.

The Data Sources page appears as shown in Illustration 13, with my adjustments.

The Subscriptions page appears, with a "no items to show in this view" statement in place, assuming no subscriptions have been created. Illustration 14 depicts this circumstance.

Because the report is parameterized, and because we know the parameter that we wish to pass upon its execution (we wish to present the Non-Consumables sales data), we need to pass the parameter as part of the process of subscription; otherwise the manual elements of report generation that existed before we obtained the new business requirement will remain with us. We will begin our setup of subscription for the FoodMart Sales Report by taking the following steps:

The Subscription properties page opens. Here we make the settings for the subscription we are creating.

The options on the properties page change to reflect those that are relevant to our Report Server File Share delivery choice. It is important to keep in mind that, although we can chose to have any report delivered to a respective designated file share, any reports that contain interactive features (including drill-down, drill-through, and so forth) will be delivered as static files.)

Foodmart Sales Report

The name must be in UNC (Uniform Naming Convention) format. For example, my entry here is as follows:

\\MOTHER1\D$\RS_SubsTest

A Books Online reference specifically states that trailing backslashes (or "slatches," as I have been calling them awhile now) are not to be used here.

Our report file, with an .xls extension, will be delivered to the specified file share with the name we have given it. Within the Render Format selector, we can select various file formats (to the delight of many clients to whom I have presented this feature to date, most of whom had to write macros, etc., to handle the same functionality in Cognos, Business Objects, and other enterprise solutions). Options include:

• XML file with report data
• CSV (comma delimited)
• TIFF File
• Acrobat (PDF) file
• Web page for IE 5.0 or later (.htm)
• Web page for most web browsers (.htm)
• Web archive
• Excel

Others are certainly possible (Lotus, etc.), with the flexible API afforded by Reporting Services, as well as through other easy approaches.

The related properties page opens, providing access to the scheduling settings for the subscription we are creating.

NOTE: Remember to return after the session to reset the subscription settings we have made, as desired.

The scheduling-related settings of the properties page should resemble those depicted in Illustration 15.

The Subscription properties page should resemble that shown in Illustration 16.

The properties page closes, and we arrive at the Subscriptions tab, initial page, where we see our new subscription listed, as depicted in Illustration 17.

Editing a subscription's setting is accomplished here by simply clicking Edit, between the checkbox and the Description for the entry.

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 also shows (depending upon our security setup) only those subscriptions that we create - not those that are created by other users.

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

The organization of subscriptions in a central location provides us an efficient means of maintenance.

Let's take a quick look at the Excel file that our labors have produced.

The file appears in the folder I designated, as shown in Illustration 19.

The Excel file opens, and appears, as shown in Illustration 20.

We note that the filter (Non-Consumables) has been passed correctly in the results that we obtain. Further, the formats appear to be intact, as well.

NOTE: Over the years, I have heard hundreds of individuals complain about issues with this particular capability in their experiences with Business Objects, Cognos, and the rest of the Big Sisters. Not a bad showing to get right, the first time, what other enterprise solutions have failed to accomplish over several years!

This is only a tiny, symbolic vestige of the advantages that Reporting Services promises those organizations that convert - in addition to the dramatic savings that cannot help but ensue.

While our exposure to basic subscriptions has focused on the creation of a standard subscription, with delivery via Report Server File Share, it is important to remember that far more can be accomplished with standard subscriptions, including the following:

•   Creation of a subscription to deliver reports via e-mail to individuals and / or groups;

•   Optional separate control of subscription processing by consumers (to enable selection of reports they wish to receive, etc.);

•   Bypass of browser viewing and delivery of reports to file shares for alternative viewing (similar to the example we performed in this lesson). This is often an effective strategy in the case of large reports that might be time consuming to load in a browser, etc.;

•   Consumer selection of PDF / Web archive formats for offline viewing;

•   Automated report archiving, established via file shares.

We can also achieve many more objectives with data-driven subscriptions, which we will examine in detail in the next article of the Reporting Services Managing phase articles group.

Summary and Conclusion ...

In this article, 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 before 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 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.

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

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