Introducing Microsoft SQL Server Reporting Services - Mauro Cardarelli

The Microsoft Business Intelligence (BI) vision is focused on leveraging Microsoft tools to get the “right data to the right people”. For those familiar with both SQL Server and Analysis Services, you are aware of the robust and flexible tools Microsoft provides for data gathering and manipulation. As a member of the beta team, I’ve had the opportunity to work with an early version of SQL Server Reporting Services and view it as a natural extension of these tools. Simply stated, it is a server-based platform for creating and delivering data-driven reports. Microsoft has announced that Reporting Services will be available at the beginning of the year. This article will detail some of the key features that make it an exciting and integral part of the Microsoft BI framework.

Overview
SQL Server Reporting Services is designed to facilitate the creation and distribution of reports across a variety of very different data sources. It supports the full life cycle of reporting, including authoring, management (i.e. timing) and delivery.

Authoring
Reports are defined using the Report Definition Language (RDL), an XML format for describing report detail. Reporting Services includes a Report Designer, embedded in the Visual Studio .NET environment, which provides an easy-to-use, graphical tool for report definition. It is important to note that no coding is required.

Management
Report definitions, as well as organization and security, are managed as a Web service. Reports can be created on-demand or scheduled. They can also be parameterized to allow for maximum flexibility.

Delivery
Reports can be generated as web-based or emailed directly to users. They can also be created in a variety of output formats including HTML, PDF and XML.

Architecture
There are three main components to Reporting Services:

Report Manager
This is the application tier. It is a web-based front end that provides access to the reports and report management. Report Manager is installed on the server.

Report Server
This is the server tier. Report Server is a web service that handles all URL requests, processes report requests and generates formatted output and enforces all security rules.

Report Server Database
This is the database tier. It is a SQL Server database that stores all information related to reports, security and scheduling.

Five Key Features
Having had the opportunity to build some prototypes with Reporting Services, I’ve identified five key features that highlight the main capabilities.

1. Data Source Support
SQL Server Reporting Services supports a variety of data sources. This data can be an enterprise data source like SQL Server as well as flat files or Excel spreadsheets. A single report can have multiple data sources.

2. Simplified Report Creation
Report creation is facilitated through the use of a graphical design tool, contained within the Visual Studio .NET environment. Creating a report is as simple as selecting the data source, presentation style (pivot table, pie chart, etc.) and format (HTML, PDF, etc.). Debugging is greatly simplified because it is in the .NET environment.

3. Delivery Flexibility
Reports can be scheduled or auto-generated based on data events. They can be delivered via a web interface or emailed directly to users.

4. Subscriptions
Users can create their own subscriptions around report formatting and delivery. It is a highly personalized experience. As another means of personalization, pre-defined parameters can be defined. A user can also designate the format and delivery mechanism for specific reports.

5. Security
SQL Server Reporting Services uses roles and permissions to manage access to specific reports. Reporting Services provides a variety of roles, from Browser through Publisher. Security can also be managed to control all levels of administrative capabilities.

Conclusion
SQL Server Reporting Services is a good fit for organizations looking to provide a consistent and manageable means of controlling information distribution. It fits well in Microsoft-focused environments and at its price level (it’s free for current SQL Server 2000 owners!) is a great opportunity to facilitate corporate data sharing.