About the Series ...

This is the first of several articles of a new 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.

The screen shots that appear in this series were taken from a Windows 2003 Server upon which I have implemented Reporting Services Beta 2. The final release may differ in appearance, as well as in the details of operation and general functionality. Any Microsoft Office components that appear in the series are members of the Office 2003 suite.

Introduction

I became a beta-tester of Reporting Services early in its development, and rapidly concluded that this new MSSQL Server 2000 add-on would literally change the face of enterprise reporting, as we know it today. Not only does Reporting Services provide an integrated, end-to-end set of tools for creating, managing, and viewing / delivering reports, but it does so with a scalable engine that supports server-based hosting and processing of reports. This is enterprise reporting at its finest, with several impressive advantages over the current offerings in the enterprise business intelligence arena. Moreover, the potential savings that await the implementing organizations could rank right up there with those promised by the recent fads of outsourcing, among other "follow the leader" activities so prevalent in business today.

As a recovering Certified Public Accountant, who also holds credentials as a Certified Management Accountant and Certified Internal Auditor, I spent several years dealing with reporting systems from the perspective of an information consumer. I am now nearing ten years in experience as a data architect and implementer of business intelligence for many Fortune 500 organizations; I have, for most of that time, worked daily with large enterprise reporting applications such as Cognos, Business Objects, Crystal Enterprise and Crystal Analysis, MicroStrategy, and divers other applications / combinations of applications.

To me, the Reporting Services model paints a bright future for all roles in the reporting life cycle, not only because an organization gets the entire solution in one package, but because the solution is open and extensible, allowing report authors, managers, and users at every level to benefit from familiar tools and systems that are already in place. The solution is a part of the Microsoft BI framework, and is the latest entry to a powerful family of tools that includes a relational database (SQL Server), a powerful ETL tool (Data Transformation Services), an OLAP engine for cube production (Microsoft Analysis Services), and a formidable data-mining component, among other substantial functionality. Needless to say, integration with Microsoft Office components is a given.

In this series of articles, I will share some of my insights and discoveries as I work with Reporting Services, in hopes of shedding more light on the practical realities I find. I will do this from the viewpoint of a practitioner who has worked closely and repeatedly with many BI products, comparing functionalities between some of these, and the new Reporting Services offerings. To kick the effort off, we will begin with a discussion of the phases of the reporting cycle as presented in the Reporting Services model, and then continue our coming articles with an overview of Reporting Services from the perspective of each of these phases.

At Last: A Genuine Integrated Approach

The major enterprise reporting application players (to whom I often refer as "the Big Sisters") have relatively recently begun to package and market their products from the perspective of the reporting life cycle (whereas, in their earlier literature they focused these efforts from what I like to call a "component perspective"). Reporting Services was designed within this integrated, life cycle perspective from its inception. It thus presents the paradigm of "one reporting tool," instead of presenting the implementer with a bundle of disparate product guides in an online library, from which one can get an integrated perspective only by milling through the set in a frustrating cross-referencing exercise. The obvious reason for the "component" approach of the Big Sisters was that they wanted to be everything to everybody, and to provide product combinations to meet the needs of their customers, while licensing each component separately as an overall marketing model.

Let's take an example: If you needed a relational report writer for general ledger and other transactional reporting, you might purchase an excellent tool like Cognos Impromptu to meet your organization's needs. If you wanted OLAP under the same vendor umbrella, you licensed a separate product, PowerPlay (which contained a cube-building component called Transformer). To get smooth and easy drill through from OLAP to its underlying transactional detail, you had to buy both components; although I have certainly accomplished drill through from PowerPlay without an Impromptu feed mechanism underneath it by creating workarounds, Impromptu is designed to optimally feed PowerPlay Transformer, with the two components working together to provide integrated security and other optimizations. When you needed to present your reports via web to enterprise consumers, the cost and complexity began to expand, as components had to be added for various functions.

This is a small example of the kinds of scenarios that can be avoided through the selection of a single, integrated model. It shouldn't be too taxing on the imagination to extrapolate the potentially huge savings in time and money that await adopters of Reporting Services.

The Phases of the Reporting Life Cycle

The Reporting Services literature breaks the phases of the reporting life cycle into the three primary stages, shown in Table 1.

We will overview each of the phases above in turn, beginning, in our next article, with the Authoring phase, then considering the Management and Access and Delivery phases in subsequent articles. In each of the overviews, we will examine the general steps contained from the perspective of the Reporting Services application, both as an introduction to later, more detailed articles, as well as to provide general, "high level" information. My hope is that this information will be useful in the coming months, to assist those leading the charge to the new reporting platform to evaluate the opportunity that awaits them. The strengths of Reporting Services that I have seen so far promise a migratory phenomenon that will be inspiring in its scope and magnitude, as OLAP moves toward a commodity market, and away from the highly specialized, expensive empire that has heretofore been ruled by a handful of dominant, proprietary players.

The Authoring Phase

The Authoring phase of the reporting life cycle witnesses the creation of a report definition. The definition is constructed on the client, and, once it meets the approval of the author, is published for general use on the report server. The query that underlies the report and its data source designation are combined in the definition, where we also do the layout design that determines the report's appearance. Virtually all content and presentation decisions are enacted in this phase, as we will discover in our next article.

Reporting Services leverages the Report Designer that it adds into Microsoft Visual Studio.NET (see Illustration 1), which means we have the added advantage of a single report design interface, regardless of the nature of the data source(s). This unifies the report writing process within a single tool that is consistent in look and feel (try finding a single writing environment for an OLAP report and a relational report with the products of the Big Sisters... your search will not be a productive one).

One of the most exciting aspects of report design within Reporting Services is the ease with which we can combine multiple data sources into a single report. One of my first challenges for Reporting Services was to create a report with an OLE-DB data source, based upon MDX (see a simple example in Illustration 2, based upon the sample FoodMart Warehouse cube supplied with MSSQL Server Analysis Services), and an ODBC data source based upon standard SQL (I used the sample NorthWind relational database that installs with MSSQL Server for the illustration). Reporting Services rose to the challenge, unlike the host of other reporting applications I have implemented for the last decade.

In contrast, let's take the Cognos Impromptu / PowerPlay combination we noted earlier: Cubes cannot be used as sources in Impromptu, to follow an earlier example, nor can relational report definitions, such as those produced by Impromptu, be pulled into a PowerPlay report, where cube data can be presented. Additionally, a catalog, upon which Impromptu is based, can only manage a single data source (I know, there are workarounds, but we're talking "out of the box," optimal functionality here). Again, the benefits of a single point of authoring are apparent to most Business Intelligence practitioners.

Underneath the Report Designer, Reporting Services generates Report Definition Language (RDL), which is XML-based, to support our authoring efforts. While we can embellish our designs with added functionality, due to the programming-capable environment provided by Visual Studio.NET, we can create sophisticated reports without going much further than the drag-and-drop capabilities to which many have become accustomed in enterprise reporting applications. The design environment also includes both local report processing and report rendering functionality, allowing us to conveniently preview our end report at various stages in its design.

We will explore many of the details of the Authoring phase in our next article in this series, The Authoring Phase.

The Managing Phase

Central management of the enterprise reporting function is a common and natural requirement in most organizations. Reporting Services again leverages its integration within MSSQL Server, and across the spectrum of the Microsoft applications that are pervasive in our enterprises. Reporting management functions, as many of us know, include a wide range of activities. Among these are security, scheduling, data source management, custody and control of the physical report files and the folders that contain them, and the maintenance of a diverse set of properties and resources.

One of the principal advantages that Reporting Services offers is the capability to manage reports and related items from a central location. Items that we can manage include reports, folders, data source connections, and resources. For these items, we define security, properties, and in some cases, scheduled operations. We can also create shared schedules and shared data sources, and make them available for general use.

The Report Manager centralizes these and other Reporting Services functions. Reports can be uploaded to the report server easily from either the Report Designer or the Report Manager, and can be viewed thereafter via a web browser. Multiple types of security can be assigned during the upload process, as well as elsewhere, as we see in Illustration 3, where I have chosen the sample reports folder, along with Windows NT Integrated Security, in the selection of the reports location (called the "data source" in this step).

NOTE: We will explore the many options available in Report Manager in our article, The Managing Phase, later in the series.

Next, we can simply select the reports we wish to upload by typing them in, or by navigating to their locations, with the Upload capability, after which the Report Manager window is populated with the reports in variable presentations, as shown in Illustration 4.

Management capabilities can be controlled for end users by an Administrator, who can assign permissions, so in addition to the access security provided within Reporting Services, we are also given functional security options, for a refined set of controls over user activities. We will explore the degrees of management and various capabilities that are possible in our article, The Managing Phase, later, but suffice it to say that the overhead involved is widely customizable to an organization's needs. I will also present a subsequent article on report server administration to explore the options and processes that can be involved from that perspective.

Report Access and Delivery

Like most web-enabled enterprise reporting packages that exist today, Reporting Services allows "self-serve" access, where information consumers can view reports on demand. Consumers can also access reports via subscription processes, whereby reports are generated and delivered to the consumer at a specific destination on a recurring basis. Consumers that are granted access privileges can select a report from a folder hierarchy on the web server, in a process as simple as clicking one of the reports as depicted in Illustration 4 of our last section. The result is a report generated on demand, an example of which is depicted in Illustration 5.

Subscription options include notification when a report is run, after which intended consumers can browse it at a standard location. E-mailing of a copy of the report to the intended audience is also accommodated. Notification and other processes can be based upon data-driven criteria: the recipient list can rely upon stored data that exists when the subscription is triggered, such as the data found in a customer database, for example.

Many interchangeable options exist with regard to viewing / exporting formats, as depicted in Illustration 6. Selections within the browser interface include XML file with data, CSV, TIFF, PDF (Adobe Acrobat), Web Archive, Excel, and HTML with Office Web Components. As we have already noted, integration with Microsoft Office is another obvious advantage offered by Reporting Services.

Summary and Conclusion ...

When Microsoft decided not to wait until the arrival of Yukon, the next release of MSSQL Server, to deliver Reporting Services, it offered organizations an early opportunity to shed the high-priced baggage of proprietary reporting options that have dominated enterprise reporting for many years. Reporting Services offers true integrated report authoring, management, and access and delivery in a single package, providing organizations a new paradigm in enterprise reporting. As we continue in our new series, we will examine in detail many of the capabilities of Reporting Services, comparing and contrasting them to the approaches of the past, and looking forward to a more cost effective, integrated tomorrow.

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

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