Recently I was part of a project team that was completing a .NET design session for the rewrite of several existing business systems. We had just spent a very grueling two days documenting, designing, and arguing over every possible piece of application architecture and feature request we could think of. It appeared that the session was starting to wind down when one of the business analysts stood up and announced that the only remaining questions he had were around the 300 existing reports and the additional 75 new ones that the business had requested. Everyone in the room groaned as I started to laugh.

I looked around the room and asked if anyone had ever heard of Microsoft SQL Server 2000 Reporting Services. The collective silence and strange looks gave me the answer I expected. In this article I will explain, as I did to the project team, the features and architecture of Reporting Services and how this tool enables enterprises to author, manage, and deliver reports.

By definition, Reporting Services is a server-based solution designed to quickly enable the creation, management, and delivery of traditional paper, Web, and interactive reports to virtually any type of device. This solution is designed as an integrated component of the Business Intelligence Framework, and consists of a Web service-oriented design that uses the data management capabilities of SQL Server to deliver real-time information that supports the daily enterprise operations necessary to drive better business decisions.

Integrated Architecture
Reporting Services is a .NET Framework application that supports not only SQL Server 2000 but also SQL Server 7, OLE DB, SQL Analysis Servers, ODBC data sources, and Oracle. Additionally, as I will discuss later, Reporting Services supports a wide variety of output formats that include the Web browser, office system applications, PDF files, and even XML files. This integrated reporting architecture (see Figure 1) is centered on an authoring environment within Visual Studio .NET 2003. This environment is designed to enable developers to connect to existing information sources, produce reports, and then deliver the reporting output.

The underlying report definitions are defined using the Report Definition Language (RDL). RDL is an XML Schema definition that describes the reporting element and data in a single encapsulated file. It isn't necessary for report authors to write or even understand RDL to create a report. Visual Studio's graphical authoring interface is designed to abstract and generate RDL as part of the report definition process.

Report Authoring Basics
The authoring interface is installed onto a developer's machine as an add-in to Visual Studio. Once Reporting Services is installed, a new project type called Business Intelligence Projects becomes available in Visual Studio, as shown in Figure 2.

The report authoring process is started by selecting the "Reporting Project Wizard". Once this project is selected, the wizard assists in defining the underlying data source that will be used to connect to a report. Once selected, the Reporting Project Wizard allows the entry of a database connection string, as shown in Figure 3.

Once the data source is completed, an author is able to define the underlying data query using the query builder shown in Figure 4. Similar to the Access Report Engine, the query builder provides a graphical design surface that is used to define and extract the underlying data query used within a report.

Once the query is completed the author is then able to complete and finalize the report using the authoring screen. Within this screen, authors are able to modify, update, and massage both the data and the presentation of the report they are designing, as shown in Figure 5.

The Report Designer defines RDL as part of the background processing. The RDL is then used to generate the report preview screen, as shown in Figure 6. The XML-based syntax of RDL uses the namespace definition:

http://schemas.microsoft.com/SQLServer/reporting/yyyy/mm/reportdefinition

In this schema definition the "yyyy" and "mm" represent the name and year of a specific RDL instance. Designed as an open, extensible syntax, RDL breaks down reports into the following elements.

1. Data: The actual report information, structure, and query used to obtain the data
2. Layout: The specific formatting information that describes how the data is presented
3. Properties: The metadata properties that include things like the author, images, and other types of data

Report Publishing
Once a report is completed within the authoring environment, it can then be self-published to the Report Server. The target server information is stored as part of the report properties. This enables different servers to be targeted for different reports or even defined as part of a test and deployment server farm. The Properties Page dialog box of the report allows authors to define the target server environments. Once the target server is identified, you can right-click and select "Deploy within Visual Studio". This promotes the completed report design to the specified target server, as shown in Figure 7.

Reporting Services employs a Web services architecture that includes a .NET Web-based Report Server and a Web-based Report management application. As reports are deployed to the Reporting Server they are stored in a folder structure that enables easy navigation and storage. The Report Server folder hierarchy is a namespace that contains a set of predefined and user-defined folders. This namespace is used to uniquely identify reports and other items stored in the Report Server database created during system setup.

Conceptually, this folder hierarchy is similar to a file system in which you may have multiple top-level and sublevel folders. This combination exposes a set of virtual folders that is accessible over the Web. In reality, these folders and reports are not part of the actual file system, but are stored in the Report Server database. As shown in Figure 8, any time a folder or report is selected, the selected name becomes part of the URL.

Within the Report Server, end users are able to create, modify, and delete reporting folders. By default, users belonging to the Everyone security group are given browse permission at the Home or top-level directory. It is important to remember that by default subfolders automatically inherit their parent's security characteristics. This inherited relationship establishes the default security for the remainder of the folder tree. Table 1 describes the default folder structure.

End users are able to populate new folders by uploading reports or creating linked reports within their existing reports. Report authors using the Report Designer are also able to publish reports directly to these folders, as shown in Figure 9.

Report Delivery
Reporting Services is designed as a .NET application. The use of the .NET Framework enables a variety of performance enhancements for report delivery. These include caching for report-based data that changes infrequently, along with support for a snapshot-based pre-execution of reports. This is ideal for reports that are generated periodically and viewed on demand by users. Reporting Services supports pre-execution of these reports and saves them as snapshots. These snapshots can be seen by users and quickly rendered. Additionally, you can save these snapshots across time to provide a historical reference.

In addition to the features available within the .NET Framework, Reporting Services provides a variety of delivery options. These include a rendering engine in addition to an on-demand delivery mechanism. The rendering engine enables reports to be delivered in a variety of formats and targets different device types.

Report Rendering Options
Reporting Services actually separates the report rendering from the initial process of generating the report. This enables the same report to be rendered into multiple file formats for distribution and delivery. Reporting Services natively supports the file types shown in Table 2.

Delivery Options
Reporting Services provides both an on-demand and a subscription-based report delivery model. The on-demand model of report delivery allows users to dynamically request reports from the Report Server. Additionally, administrators and users are able to schedule a specific report and - within the context of a subscription - are able to receive reports according to the refresh interval defined in the reports. On-demand report access is based on a browser model and scheduled report delivery is driven through a subscription model.

Within the context of a subscription the user receives reports that result from a combination of rendering and delivery options. This enables users to personalize these options. For example, one user may want to receive a report as a PDF, but another user may wish to receive the same report as an Excel-based attachment to an e-mail message. Subscriptions can be defined by users with the proper permissions or created on their behalf by an administrator defining a specific set of dynamic subscriptions through a runtime recipient list, for example, a specific set of sales managers. When creating a personalized subscription, users are given the ability to define a set of delivery preferences. Personal subscriptions also include the ability to include specific report-based parameters. For example, a user may want to filter reports to include only specific data segments.

Conclusion
As I explained during my design session, Reporting Services offers a tremendous number of features and advantages. I challenged my project team to leverage these advantages to handle their reports. When I met with them the other day I was happy to see that they had taken up the challenge and solved their reporting problem. As I challenged them, I now challenge you: install Reporting Services and take it for a test drive. I think you will be surprised at the possibilities it offers, and the ease with which you can create, manage, and publish your reports.

• Figure 1
• Figure 2
• Figure 3
• Figure 4
• Figure 5
• Figure 6
• Figure 7
• Figure 8
• Figure 9
• Table 1
• Table 2