Introducing Microsoft Reporting Services

So much information, so little time ... the character “Poison Ivy” would likely say if the Batman saga was taking place in today’s enterprise.

We all know that the dot.com boom is history and so are the lavish IT budgets. In the doldrums of the economic recovery, organizations tend to spend their money on streamlining internal processes to gain a competitive advantage. According to Microsoft, today’s information workers spend as much as 80 percent of their time gathering information, with only 20 percent left to analyze it and make a decision. In many organizations, such requests consume significant IT and development resources. Too often, Excel spreadsheets are the prevalent reporting tools today and manual data entry or “pencil-pushing” is among the top reasons for inaccurate data and wrong decisions. Aware of these issues, Microsoft initiated the Microsoft SQL Server 2000 Reporting Services project at the beginning of the new millennium, with a bold vision to “enable employees at all levels of an organization to realize the promise of Business Intelligence to promote better decision making.”

This chapter provides a panoramic view of Reporting Services (RS). Throughout the rest of this book I will use the terms Reporting Services and RS interchangeably. You will see

1.1 WHAT IS RS?

Regardless of the alphabet soup of terms and acronyms that are popping up like daisies almost every day and that have probably become a part of your IT vocabulary—terms such as BI (business intelligence), OLAP (online analytical processing), data mining, DSSs (decision support systems), EISs (executive information systems), digital dashboards, enterprise portals, and enterprise data buses—the purpose of enterprise reporting is to simply “get out” what was “put in.” Therefore, for many applications, reporting represents the last, and often most important, stage of the IT pipeline.

To clarify the last point, let’s consider a typical scenario that RS can address effectively. Let’s say that an organization has built a web portal for submitting orders online. As the business grows, the same organization may need to implement a reporting infrastructure to analyze sales data and understand its business, for example, to find out the top-selling products, customer demographics, and so forth. To accomplish this goal, the organization could leverage RS.

We use the term report to refer to the web-based or saved-to-file counterpart of a standard paper-oriented report. For example, an organization may want to give its customers an option to generate various reports online—an Order History report, for instance. Web reporting has traditionally been difficult to implement. Even more difficult has been exporting reports to different file formats. RS solve both problems elegantly, for two reasons. First, out-of-the-box RS is web-enabled. Second, most popular export formats are natively supported.

1.1.1 Why do we need RS?

Ironically, despite the important role that reporting plays in today’s enterprise, creating and distributing reports have been traditionally painstaking and laborious chores. To understand why we need RS, let’s analyze the reporting problem space. Table 1.1 lists some of the most pressing issues surrounding the reporting arena and how RS addresses them.

Reporting Need	How RS addresses it?
Report authoring can be labor intensive.	By using the powerful Report Designer, you can author reports as easily as you can with Microsoft Access.
Centralized report management is needed.	RS enables you to save your reports in a single report repository.
Reports need to be distributed to various destinations.	RS supports both on-demand and subscription-based reporting. Reports can be requested on-demand by WinForm and web-based applications. Alternatively, reports can be distributed to a list of subscribers.
Reports often need to be exported in different electronic formats.	RS supports many popular export formats out of the box.
Proprietary nature of reporting tools doesn’t allow you to extend them.	RS has a flexible architecture that allows you to extend RS capabilities by writing custom code.
Reports need to be secured.	RS offers a comprehensive security model that administrators can leverage to enforce secured access to reports by assigning users to roles. When the default Windows based authentication is not a good fit, it can be replaced with custom security implementations.
Enterprise reporting solutions can be costly.	To minimize cost, RS is bundled and licensed with SQL Server. If you have a licensed copy of SQL Server 2000, you may run RS on the same server for no additional license fee.

Table 1.1 How Microsoft RS deals with the reporting problem space

Depending on your particular situation you may find other compelling reasons to target RS as your reporting platform of choice. We revisit the RS features throughout this chapter.

Supported report types

Your reporting requirements may call for authoring various types of reports that differ in complexity. For example, your users may request that a large report include a document map for easy navigation. RS lets you design a variety of report types, as listed in table 1.2.

Report Type	Purpose	Example
Tabular	Displays data in a table format with a fixed number or rows and columns.	Excel-type reports
Freeform	Data regions are positioned arbitrarily on the page by the report author.	Invoice-invoice details report
Chart	Presents data graphically.	Employee performance chart
Crosstab (matrix)	Data is rotated to present row data as columns.	A report that shows products on rows and time on columns
Drilldown	Includes expandable sections.	A company performance crosstab report where product can be expanded by category and brand
Drillthrough	Generated from clicking on a hyperlink.	Customer Order History with hyperlinks on the order identifier to show the order details report
Interactive	Includes interactive features, such as document maps, hyperlinks, visibleon-demand sections, and so forth.	Adobe Acrobat–type reports with document maps on the left side

Table 1.2 RS supports various report types

Although most popular reporting tools support many of the report types shown in table 1.2, RS makes the report-authoring process as easy as working with Microsoft Access reporting functionality. For example, report authors can drag and drop items to define the report’s appearance. Now that we understand what RS is, let’s see how it fits in the Microsoft BI vision.

1.1.2 How is RS implemented?

Microsoft released version 1.0 of RS at the beginning of 2004 as an add-on to Microsoft SQL Server 2000. At a very high level, RS can be defined as a server-based platform for authoring, managing, and distributing reports. We discuss the RS architecture in more detail in a moment. For now, note that RS is integrated with and requires several other Microsoft products, including:

For more information about installing RS, please refer to appendix A.