Practical Business Intelligence with SQL Server 2005

SQL Server Reporting Services is a server-based platform for designing, managing, and delivering both interactive reports and traditional printed reports. Although Reporting Services is a component of SQL Server 2005 and uses a SQL Server database as the catalogue, you can include data in your reports from any database with an OLE DB or ODBC driver. This version of Reporting Services also has great integration with Analysis Services, making it easy to build reports that take advantage of the rich multidimensional information available in cubes.

Reporting Architecture

Many reporting technologies required a client application to be installed on every user's computer. Reporting Services is different: It is a fully server-based application built around web services, as shown in Figure 2-10, which can render and deliver the same report in whatever format the user requests. The report can be an HTML page that is displayed using a browser, a PDF file that uses Adobe Acrobat Reader, an Excel spreadsheet, or one of several other formats.

Report developers can design a report on their workstation and then publish the report definition to the reporting server. When a user makes a request for a report, the Reporting Services web service is responsible for querying the underlying data sources, rendering the report in the necessary format, and returning the information back to the client for display. Reports can also be configured so that the data is cached to improve performance.

Reporting Services requires Internet Information Services (IIS) to be installed on the server, and in common with many Web applications, it is usually installed on a separate server from the database server.

Designing Reports

BI Development Studio includes a Report Server project type for building reporting solutions. Reports are developed using a drag-and-drop report layout and query designer, as shown in Figure 2-11, and the report can also be executed within the designer to preview the results. The report designer is flexible and not restricted to "bands" (in contrast to many other reporting environments). You can combine tables, lists, and text boxes in a single report that may have multiple data sources.

You can add parameters to the reports so that users can select the data that they are interested in seeing before running the report. You can link each parameter to a query so that a list of possible values is presented to the user. Reports can also include complex expressions using the VB.NET syntax, to provide totals or more complex calculations. For more complex applications, you can add functions to the report's code section or even register a .NET assembly and make calls to it from within report expressions.

Report solutions that you create in the BI Development Studio environment consist of RDL (Report Definition Language) and other files on your development machine. After you have finished designing the reports, you can deploy the solution to a server running Reporting Services so that users can access the reports.

Accessing Reports

Because Reporting Services is essentially a web service that delivers reports on request, you can integrate reports into your Web-based or Windows client applications easily. Visual Studio 2005 includes a ReportViewer control that you can use to display server-based reports within a Web-based or Windows client application. Reporting Services also includes two Web parts that enable you to display reports in a Windows SharePoint Services (WSS) team site.

You can also access every report via a simple URL, so the easiest way to integrate reports into your own Web applications is just to supply a hyperlink that the user can click to display the report in his browser. Alternatively, one of the most common ways that users access reports is via a Web application called Report Manager that is installed as part of Reporting Services, as shown in Figure 2-12. Report Manager enables users to browse a list of reports that they have access to, enter parameter values, and view the reports in the browser.

Reporting Services Features

One of the most interesting features of Reporting Services is that a single report can include information from different queries, which are available as datasets in the report designer. Each dataset can be associated with data regions in the report to display the information. The data region could be a simple table, a flexible list that gives you more control over the formatting, or a matrix, which is similar to a table but with dynamic columns determined at runtime.

Interactive Reports

Rather than just statically presenting a list of information, you can also configure reports to include some interactivity. For example, in a report that shows revenue by product with subtotals for each product category, the report could be designed so that only the summary data is initially displayed and the user can drill down to see the detailed product-level information for a particular category.

You can also configure data elements in reports to include hyperlinks. These links can be used to create drillthrough reports, where the user clicks the link to display another report containing the details of the total the user selected. Alternatively, the link could open another Web application and pass across the data selection as a parameter, or jump to a particular section of the same report.

Charts

Reports can also include charts to graphically represent data in a report. All the standard chart types are supported, including bar and column charts, pie charts, and line and area charts, as shown in Figure 2-13. The chart designer enables you to drag and drop fields for the data values, categories, and series onto the design surface and then specify display options such as fonts and colors.

End-User Reporting with Report Builder

Access to predefined, parameterized reports suffices for many users, but most organizations have a number of people who need more flexibility in reporting but who don't have the technical background to be comfortable with BI Development Studio and SQL or MDX query syntax. Report Builder is a component of Reporting Services that enables business users to create their own ad-hoc reports against a more user-friendly view of the data called a report model.

Report models can be built on top of SQL Server relational or Analysis Services databases using BI Development Studio, or a complete report model can be generated from an Analysis Services data source in one step using the Report Manager or SQL Server Management Studio tools. After the report model has been deployed to the reporting server, business users can launch the Report Builder application to create reports, as shown in Figure 2-14.

Report Builder is designed along the same lines as other Microsoft Office applications (although it is a click-once application that doesn't require a complex client installation before use). Users start by selecting the area of the model and a standard layout, and then they can drag and drop fields onto the design surface and specify filters. Reports are then published to the Reporting Services server, either to the user's personal "My Reports" folder or to more widely available folders if the user has the appropriate level of access.

Subscriptions

So far we have looked at how users can view reports when they want, but Reporting Services also enables users to subscribe to a report and have it delivered to them via e-mail on a periodic basis. Users can specify parameter values for the report so that they receive the information relevant to their job, such as for a particular sales region or product group. Users can manage their subscriptions using the My Subscriptions feature in Report Manager, which allows them to modify or delete subscriptions.

A subscription can also be set up to send the report directly to a file share rather than by e-mail, which enables users to send a copy of a report to their folder of choice in a handy format such as an Excel spreadsheet or PDF. This proves useful for standard reports such as weekly sales reports that can be made available in the user's folder automatically every week.

Other than individual subscriptions to a report, Reporting Services also supports a feature called data-driven subscriptions that enables you to broadcast a report to a list of users. You can set up a query that returns a mailing list of e-mail addresses (or file share locations) and associated parameter values so that individual users can receive customized reports, and this list is then used at runtime to determine where to send the report.

Managing and Securing Reports

Reporting Services can be managed using SQL Server Management Studio, as shown in Figure 2-15; alternatively, if you are managing a single report server over a Web connection, you can use Report Manager. Many of the report properties are defined in BI Development Studio, but you can use the management tools for tasks such as changing the data source connections to point to production servers or modifying the parameters that are shown to the user.

Reporting Services has a number of settings that you must define, such as the location of the catalog database and the credentials used to access it, as well as the virtual directories for the web service and Report Manager Web application. SQL Server 2005 has a tool called the Reporting Services Configuration Manager that enables administrators to specify these settings and test the results.

Access to reports is based on the users' Windows account credentials and the groups that they belong to. Using the management tools, a single report can be secured so that only specific users or groups can access the report, or the security can be defined at the level of a folder containing multiple reports.