An Introduction to Sql Server Reporting Services I...

Sql Server reporting services provides the means to design, author, render and deploy business reports via the web.

By: Chris Sully Date: February 19, 2005

Introduction

SQL Server reporting services (subsequently SRS) provides the means to design, author, render and deploy business reports via the web. In this series of articles we shall look at what SRS offers via a consideration of the following areas:

• Overview and installation
• Report authoring
• SRS and .NET
• Deploying reports
• Managing reports
• Securing reports
• Business Intelligence (BI) and SRS
• The future of SRS

We'll commence the treatment of the above topics with an overview of SRS.

Background

SRS integrates tightly with Microsoft's .NET technologies whilst at the same time providing interoperability with other platforms through the product’s use of Web services. SRS was originally intended to ship with the Yukon release of SQL Server, now called Microsoft SQL Server 2005. However, customer demand and feedback indicated an early SQL Server 2000 release was warranted.

The process of designing reports hasn’t changed substantially for 15 years. The report designer lays out report objects, which are subsequently populated with data from a data source, in a design application such as Crystal Reports or Microsoft Access. He or she then tests report execution and then distributes the report on to those who need access to it. SRS is largely the same. The key differences to be aware of however, are:

• It provides a standard reporting platform based on Report Definition Language (RDL), which is an XML schema that dictates the common structure of all SRS reports. This allows for report creation from any third party application that supports the RDL schema.
• If you already have a SQL Server license then SRS is essentially free!
• SRS offers features out of the box that in other products would be expensive additions, including subscription services, report caching, report history and scheduling of report execution.
• SRS, being a web based solution, can be deployed across a variety of platforms.

.NET developers have the added advantage that the report designer for SRS is a plugin to the VS.NET IDE.

Architecture

The main tiers of the SRS architecture are:

• client
• web service
• SQL server storage databases
• data source

The data source for the reporting solution can in fact be any supported data provider, e.g. LDAP, Oracle or Analysis Services as well as SQL server itself.

Note that it is possible but is'’t recommended to configure a single server to be the SRS web and database server as well as the data source server, unless there is a small user base. We'll look at performance in later articles.

The Report Server itself exposes the programming interface which is accessible via SOAP, HTTP and WMI). Behind the scenes are the:

• Report processor with rendering and data processing extensions, and
• Scheduling and delivery processor

With these items communicating with both the Report Server databases (ReportServer and ReportServerTempDB) and the actual data source for the reports as and when required.

SRS also includes a number of client applications that use the SRS programming interface, namely the Web service APIs and URL access methods to provide tools for users and developers to access SRS reports and report objects. These tools provide management, configuration and report rendering functionality. These are:

• Command line tools (rs, rsconfig, rsactivate)
• rs: processes report services script files that are written with VB.NET code.
• rsconfig: configures the authentication methods and credentials for how SRS connects to the ReportServer database. rsconfig also sets the unattended SRS execution credentials for SRS.
• rsactivate: activates an instance of SRS by creating a unique symmetric key used to encrypt data in the SRS databases. When a server is activated the SRS web service us associated to another SRS database.
• rskeymgmt: manages the encryption keys that SRS uses to store sensitive data securely, such as authentication credentials.
• report manager
• This is a browser based application that ships with SRS and provides a GUI for users who view or print reports or manage report objects for their workgroup or department.
• report designer
• Provides embedded support for designing reports in the VS.NET IDE. Other 3rd party tools are also available.
• custom applications
• Can be window or web applications that call the SRS web service to perform such tasks as report rendering and report object management. SRS includes sample application projects that you can compile and run to extend the functionality of the main tools listed above.

SRS Report Server

The SRS report server comprises both the SRS web service and the ReportServer windows service and is central to SRS. It handles every client request, either to render a report or to perform a management request. The report server functionality can be sub-divided as follows:

• programming interface
  Web service API and URL access methods are supported. Depending on the type of request the programming interface either handles it directly by accessing the ReportServer database or passes it on to another component for processing. If the request is for an on demand report or a snapshot (delivered by non-web means), the programming interface passes it to the report processor before delivering the completed request back to the client or storing it in the ReportServer database
• report processing
  The report server is responsible for all report requests. It communicates with the ReportServer database to obtain the report definition information which it combines with the data returned from the data source to produce the report.
• accessing data stores
  For types were supported at the time of writing: SQL Server, Oracle, OLE DB and ODBC.
• report rendering
  The populated report is then rendered in one of the several supported rendering types: HTML, PDF, HTML using Office Web Components, Excel, XML, CSV, MHTML and TIFF.
• report scheduling and delivery
  If the request from the client is one that requires a schedule or delivery extension, such as a snapshot or subscription, the programming interface calls the scheduling and delivery processor to handle the request. Note that SRS uses the SQL Server Agent to create the schedule job so this must be running!

SRS Databases

Two databases are created as part of the SRS installation: ReportServer and ReportServerTempDB. ReportServer stores all the data relating to reports, report objects and report parameters as well as folder hierarchy and report execution log information. ReportServerTempDB’s main purpose is to store cached copies of reports that you can use to increase performance. Additionally, the RSExecutionLog database can be added after the initial installation. This database stores additional logging information.

Installation

Installation should be painless and can be done via Setup or the command line. If using the developer edition as opposed to the standard edition you will have the additional option of connecting to an existing SRS database, thus allowing the installation to join a web farm of other such servers.

For the minimum installation requirements for SRS see:

http://www.microsoft.com/sql/reporting/productinfo/sysreqs.asp

You should use the edition of SQL Server 2000 that corresponds with the edition of Reporting Services you will be running. Note that Windows XP Professional and Windows 2000 Professional only support Reporting Services Developer Edition.

Summary

This first article in the series has provided an overview of SRS. In the next article we'll actually start using the tool, looking at report authoring.

References

Pro SQL Server Reporting Services
Landrum and Voytek
Apress

www.microsoft.com/sql/reporting/default.asp