Reporting Services

Since before the invention of computers, the primary method for sharing business intelligence has been the report. This is slowly changing as BI tools become easier to use and more widely available. In fact, some of the tools and capabilities of SQL Server 2005 encourage that process.

Still, in the business intelligence world, the report is king. That being the case, it is important to have a capable and easy-to-use reporting environment at our disposal. Reporting Services fits that bill.

If Reporting Services were simply a report authoring environment, it would be a good tool. However, Reporting Services has much more to offer beyond just report creation. In fact, Reporting Services is a complete, enterprise-wide report management and distribution service. Reporting Services enables us to securely distribute reports throughout an organization using an existing intranet infrastructure and it provides for the integration of reports with both desktop and web-based applications.

Reporting Services also is a bridge between static, paper reports and interactive business intelligence tools. In addition to creating reports for printing, Reporting Services permits users to interact with reports, drilling down from summary to detail information, navigating to related reports, and even jumping to related websites. To use Reporting Services to its fullest extent, we need to create reports that encourage user interaction.

Reporting Services enables both developers and users to create reports. It offers two report authoring tools: the Report Builder and the Report Designer. The Report Builder is geared toward those power users and analysts who want to do their own ad hoc reporting, without having to learn all the details of database structure and query creation. The Report Builder presents report authors with a simplified model of our database, so these authors do not need to know the details of querying databases to create reports. Once the Report Builder creates a report, that report can be deployed to the Report Server and functions exactly like a report created with the Report Designer.

The Report Builder is an outstanding tool for these types of users, but to be easy-to-use, it must remain fairly simple. Therefore, Report Builder cannot support all the many wonderful features of Reporting Services reports. The Report Designer offers far greater capabilities for creating interesting and highly functional reports used to convey business intelligence to report users. The Report Designer contains everything necessary to create a wide variety of reports for Reporting Services. Everything we need to select information from data sources, create a report layout, and test our creation is right at our fingertips. Best of all, the Report Designer is found in both the Business Intelligence Development Studio and in Visual Studio 2005.

Report Structure

A report project can contain a number of reports. Each report contains two distinct sets of instructions that determine what the report will contain. The first is the data definition, which controls where the data for the report comes from and what information is to be selected from that data. The second set of instructions is the report layout, which controls how the information is presented on the screen or on paper. Both of these sets of instructions are stored using the Report Definition Language.

Figure 15-1 shows this report structure in a little more detail.

Figure 15-1: Report structure

Data Definition

The data definition contains two parts: the data source and the dataset. The data source is the same as we have seen used in OLAP projects: it is the set of instructions the report needs to gain access to a data source that provides data for the report. The data source definition can be created right inside the report definition, or it can be created externally and shared by a number of reports. In most cases, report management is easier when we use external shared data sources.

When the report is executing, it uses the data source instructions contained in the report to gain access to the data source. It then extracts information from the data source into a new format that can be used by the report. This new format is called a dataset.

The content of the dataset is defined using a tool called the Query Designer. The Query Designer helps us build a database query. The database query may be in T-SQL for querying relational data, MDX for querying multidimensional data, or DMX for querying data mining data. The query provides instructions to the data source, telling it what data we want selected for our report. The query is stored in the report as part of the data definition.

The data selected by the query into the dataset consists of rows and columns. The rows correspond to the records the query selects from the data source. The columns correspond to the fields the query selects from the data source. (Reporting Services does not support hierarchical result sets, so MDX and DMX query results are flattened into a single table of rows and columns.) Information on the fields to be selected into the dataset is stored in the report as part of the data definition. Only the information on what the fields are to be called and the type of data they are to hold is stored in the report definition. The actual data is not stored in the report definition but, instead, is selected from the data source each time the report is run.

Report Layout

The data the report has extracted into a dataset is not of much use to us unless we have some way of presenting it to the user. We need to specify which fields go in which locations on the screen or on paper. We also need to add things such as titles, headings, and page numbers. All of this forms the report layout.

In most cases, our report layout will include a special area that interacts with the dataset. This special area is known as a data region. A data region displays all the rows in the dataset by repeating a section of the report layout for each row.

Report Definition Language

The information in the data definition and the report layout is stored using the Report Definition Language (RDL). RDL is an Extensible Markup Language (XML) standard designed by Microsoft specifically for storing report definitions. This includes the data source instructions, the query information that defines the dataset, and the report layout. When we create a report in the Report Designer, it is saved in a file with an .RDL extension.

Report Delivery

We have discussed how a report is created by the Report Server. What we have not discussed is where that report is going after it is created. The report may be sent to a user through the Report Manager website. It may be sent in response to a web service request that came, not from a user, but from another program. It may also be e-mailed to a user who has a subscription to that report.

Report Manager Website

One way for users to request a report from the Report Server is through the Report Manager website. The Report Manager website organizes reports into folders. Users can browse through these folders to find the report they need. They can also search the report titles and descriptions to locate a report.

The Report Manager also includes security that can be applied to folders and reports. With this security, the site administrator can create security roles for the users who will be accessing the site. These security roles control which folders and reports a user is allowed to access.

In the Report Manager, reports are always displayed using the HTML format. Once a report has been displayed as an HTML page, the user can then export the report into any of the other available formats.

Subscription Delivery

If the users do not want to go to the report, Reporting Services can make the report go to them. In other words, users do not necessarily need to come to the Report Manager website to receive a report. They can have the report delivered to them through a subscription service. The Report Manager enables users to locate a report on the site, and then subscribe to it, so it will be delivered to them in the future.

When users subscribe to a report, they provide an e-mail address to which the report is to be delivered, either as the body of the e-mail or as an e-mail attachment, depending on the requested format. Users can specify the format for the report at the time they create their subscription.

The site administrator can also set up report subscriptions. These function like a mass mailing, using a list of e-mail addresses. Rather than requiring each user to access the Report Manager to create their own subscription, the site administrator can create one subscription that is delivered to every user in the list.

Web Service Interface

In addition to delivering reports to humans, either at their request or on a subscription basis, the request handler can deliver reports to other software applications. This is done through a series of web services. A program calls a web service on the Report Server, requesting a particular report in a particular format. The completed report is returned to the program that originated the request as the response to the web service request.