Chapter 8: Reporting Services

One of the most important enhancements found in SQL Server 2005 is the new Reporting Services subsystem. Reporting Services was first introduced as an add-on to SQL Server 2000 and filled an important hole in the SQL Server product line. Since its introduction, SQL Server has always been one of the easiest relational database platforms to implement, and this ease of use has made it incredibly popular for department-level implementations as well as a database platform for small- and medium-sized businesses. Once SQL Server was installed, however, if you wanted to begin retrieving information from the database by generating reports, then you needed to go out and get another product with built-in reporting capabilities in order to output that data as a viewable or printed report. SQL Server had no built-in tools that were capable of generating reports— the most common and expected output from a relational database system. To get some initial reports out of SQL Server, many companies started using desktop reporting tools like Microsoft Access, but in the end most medium- and larger-sized organizations wound up adopting more powerful third-party reporting products such as Business Object’s Crystal Reports.

The introduction of Reporting Services with SQL Server 2000 changed all of that. Reporting Services provides an out-of-the-box reporting solution that goes far beyond the capabilities of simple reporting solutions like Access. Reporting Services is an enterprise-capable reporting system that not only provides the ability to graphically design reports but also enables you to securely deploy those reports across the enterprise rendered in a variety of different formats including web-based HTML reports, Windows-based rich client reports, and reports rendered for mobile devices. SQL Server 2005 Reporting Services continues to evolve into the new standard for SQL Server enterprise reporting. In the first part of this chapter, you’ll get an overview of the architecture and system components used by SQL Server 2005’s Reporting Services. Then in the second part of this chapter, I’ll dig in deeper and give you a look at how you design and deploy reports using Reporting Services.

Reporting Services Architecture

SQL Server 2005’s Reporting Services isn’t just a report design tool. Instead, it’s a reporting platform that not only enables the creation of reports but also stores report definitions, provides secure access to reports, renders reports in a variety of different output formats, schedules report delivery using either a push or pull delivery mechanism, and enables the deployment of those reports.

Reporting Services is an optionally installed component that requires the presence of IIS (Internet Information Services) in order to be installed. If IIS is not present on the system running the installation, the option to install Reporting Services will not be present on SQL Server 2005’s installation dialogs. While Reporting Services can be installed on the same server system as the SQL Server database engine, for improved scalability it’s usually better to install Reporting Services on a separate server.

Note 

Reporting Services is licensed as a part of SQL Server 2005 and does not require any separate licensing for use on a single system. However, it does require an additional license if you implement it on a separate system.

SQL Server 2005 Reporting Services is more than just a single application. It’s a server-based subsystem that’s designed to enable the creation, management, and deployment of reports across the enterprise. Reporting Services can be used to create tabular, graphical, and freeform reports that can be viewed and managed using a web-based connection. These reports can run against a SQL Server 2005 database as well as any OLE DB- or ODBC-compliant database sources. You can see an overview of the Reporting Service architecture in Figure 8-1.

Figure 8-1: Reporting Services overview

As you can see in Figure 8-1, Reporting Services reports are created using the Report Designer. The Reporting Services Report Designer has a fully graphical design surface and interface program that enables you to interactively design and test reports. Unlike the banded report designer that’s part of the familiar Microsoft Access program, the Reporting Services Report Designer is completely unbanded, giving it a much greater degree of flexibility. A banded report designer restricts specific areas of the report designer for predefined uses; for example, the top portion of the page is always the heading, the middle portion of the page is for the report body, and the bottom part of the page is for the reporting footer. While many reports follow this style, you don’t have the flexibility to do anything else. The Reporting Services Report Designer’s unbanded layout gives you a much greater degree of freedom; for example, you can easily lay out your report in four quadrants of the page, each section using its own formatting and data. A much more detailed look at the Reporting Services Report Designer is presented in the second part of this chapter.

After a report is designed using the Report Designer, the report definitions are stored in the ReportServer database. Reporting Services reports are stored in a new XML-based data format called Report Design Layout (RDL). By default, these RDL definitions are stored in SQL Server 2005’s ReportServer database. In addition to the report’s RDL specifications, the ReportServer database also stores information about a report’s security and destination.

The Reporting Services Report Server, the core component in the Reporting Services architecture, is an ASP.NET application that runs on top of Windows Internet Information Server (IIS). The primary responsibility of the Report Server is to process the stored report definition files and then render reports in the appropriate format for the specified delivery mechanism. In addition to standard printed reports the Reporting Services Report Server can render reports in the following different output formats:

• HTML

• Excel

• Web archive

• PDF

• TIFF

• CSV

• XML

Reporting Services is managed using the Report Manager. The Report Manager is a web-based application that enables the DBA or reporting administrator to control the security and overall management attributes of the reports created using Reporting Services. This includes specifying who can create and change a report as well as who can run a given report. The Report Manager can also set up both push and pull delivery schedules for Reporting Services reports.

In the next section, you get a more in-depth look at the functionality provided by each of these components.