Reporting is an integral part of business intelligence development life cycle. Considering this, SQL Server 2005 includes a scalable and secure, enterprise reporting platform, called SQL Server 2005 Reporting Services. SSRS is a revolutionary reporting paradigm that can be used to author, manage, and deliver various kinds of reports containing data from relational or multi-dimensional data sources. Reporting Services was originally released as an add-on to SQL Server 2000. It is now natively integrated in SQL Server 2005 and also contains several enhancements over Reporting Services 2000. Before looking at these enhancements and new features, let's review some of the core components and tools that make up the reporting services platform.
Reporting Services Components and Tools
Reporting Services architecture consists of various components and tools that you can use to manage Reporting Services application. Here is an overview of these components and tools:
At the core of Reporting Services architecture is a component called Report Server. Report Server is responsible for data and report processing and report delivery. Report Server itself consists of other sub-components including a Windows service and a web service. The Windows service provides scheduling and delivery infrastructure and the web service provides processing and rendering programming interfaces. The Report Server interacts with a SQL Server database that is used to store information and metadata of published reports, report models, and folder hierarchy. All access to this database must be handled through the Report Server by using management tools (such as Report Manager and SQL Server Management Studio, discussed next), or programmatic interfaces such as URL access, Report Server web service, or the Windows Management Instrumentation (WMI) provider.
The web-based Report Manager tool can be used to access and manage reports, perform administrative tasks such as managing reports, folders, security, scheduling, subscription, and to launch Report Builder (which is discussed later). An alternative way to administer reports is to use Object Explorer in SQL Server Management Studio by connecting to a Report Services instance.
SQL Server Business Intelligence Development Studio can be used to author and deploy reports. The report designer in BIDS is a full-featured report authoring tool that can be used to create both simple and complex reports. BIDS provides three SSRS project types, Report Server Project Wizard, Report Server Project, and Report Model Project. The Wizard can be used to quickly create a report and then modify it; Report Server Project can be used to build a report from the ground up using the designer and the Report Model Project can be used to create data sources, data source views, and report models that can be used by business uses to create ad-hoc reports by using by Report Builder (which is discussed next). A report model is an additional layer of information that maps database tables and views into concepts that are meaningful to business users.
Report Builder is another report authoring tool available in SQL Server 2005. It is intended for business users who know their data and want to create ad-hoc reports. Report Builder is the result of the Microsoft's purchase of a company named ActiveViews in March 2004. Report Builder is a lightweight reporting tool that allows a business user to design and build reports without knowledge of SQL or MDX and without using complex tools such as BIDS. Report Builder provides an interface similar to those of Microsoft Office tools such as Excel. After a user defines his or her connection information to access a cube, the interface exposes the cubes, dimensions, and measures that are accessible by the user. The user then drags and drops the dimensions and measures that he or she wants to analyze. The user can then build a report that can be deployed and managed like any other report. A report that is built using Report Builder could be further embellished, however, through BIDS. In this scenario, a business user could define reports for one-time analysis, or he or she could decide that the report he or she has designed will have ongoing benefits to himself or herself as well as to additional users. The user could then request a more formalized report, based on the report definition, from a formal report writing department in the organization. Report Builder is a ClickOnce application, which means when the a user clicks Report Builder link inside Report Manager website, the application gets downloaded from the report server to a user's local computer. ClickOnce facilitates centralized management of the application.
The reports created using BIDS and Report Builder are saved in an XML format called Report Definition Language (RDL). Once a report is created, it can be made available to other users by publishing or deploying a report project in BIDS or by saving a report in Report Builder.
Users can view the reports by browsing to Report Manager website. Developers can present the reports in their custom applications by using new ReportViewer controls or on SharePoint sites by using report viewer web part. BIDS and Report Builder both allow previewing the reports. Reporting Services allows exporting reports to various formats such as XML, PDF, CSV, TIFF, Web archive (.mht), and Excel. Developers can extend the Reporting Services functionality by leveraging the WMI and SOAP web services programming APIs provided by SSRS.
SSRS provides three command line utilities that can be used to administer a report server, rsconfig.exe, rskeymgmt.exe, and rs.exe. The rsconfig.exe utility is used to set configuration information for the specified report server. The configuration values are stored into RSReportServer.config file under %ProgramFiles%\Microsoft SQL Server\MSSQL.instance_id\Reporting Services\ReportServer folder. For security reasons, some configuration values are encrypted before saving. The rskeymgmt.exe utility is an encryption key management tool that can be used to back up, apply, and recreate symmetric keys. The rs.exe utility is provided to allow administrators to automate report server deployment and administration tasks. This utility can be used to execute Visual Basic .NET scripts against the specified Report Server.
The new Reporting Services Configuration Manager tool allows configuring a local or remote Reporting Services installation. This tool allows managing Reporting Services virtual directories, Windows and web services credentials, report server database, encryption keys, and SMTP e-mail delivery settings.
Figure 13.8 shows the various configuration options provided by the Reporting Services Configuration Manager.
Figure 13.8. Reporting Services Configuration Manager allows configuring a report server deployment.
Reporting Services 2005 Enhancements
SQL Server 2005 Reporting Services builds on the solid foundation of Reporting Services 2000, providing a deep integration with other SQL Server 2005 components and SharePoint, core product enhancements, improved development productivity, and new tools. Here are some of the new features and enhancements introduced by SQL Server 2005 Reporting Services:
Setup and deployment enhancements: SQL Server 2005 setup has been enhanced to decouple setup and configuration. Setup provides a files-only installation option that copies the program files to disk. And later, Reporting Services Configuration Manager tool can be used to complete the configuration step. Setup also provides a default configuration option that installs a ready-to-use report server. Independent of which setup option was chosen, the Reporting Services Configuration Manager tool can be used to configure and customize a Reporting Services installation. In addition, the new SQL Server 2005 Surface Area Configuration tool provides options to quickly enable or disable Report Server Windows service (and hence enable or disable scheduled events and report delivery), and web service (and hence enable or disable HTTP SOAP access).
Management Studio integration: SQL Server Management Studio is enhanced to allow administering one or more report server instances. In addition, the scripting support allows generating a Visual Basic .NET script for report server objects. This script can be optionally updated, and then executed by rs.exe utility discussed earlier.
Report Builder: As discussed earlier, SQL Server 2005 includes a new tool for business uses to create ad-hoc reports based on the report models created and published by the model designers or developers. BIDS introduces a new project type called a Report Model that can be used to create the report models to be used by the Report Builder client.
Report functionality enhancements: Responding to the feedback received on SQL Server 2000 Reporting Services, Microsoft has added several improvements to enhance the report functionality. The examples of these enhancements include ability to specify multiple values for a parameter, enhanced printing support, interactive sorting in reporting, SharePoint integration, and improved RDL. Various report authoring enhancements have been made to report designer inside BIDS including a new graphical MDX query builder, enhanced expression builder, and SSIS integration.
Programmability enhancements: Reporting Services 2005 introduces two new web services interfaces to manage objects on report server and to control the report processing and rendering. In addition, Visual Studio 2005 includes a set of freely redistributable report viewer controls that you can embed in your custom Reporting Services client application.