RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Generate Ad-hoc Reports with Microsoft Reporting Services 2005

Among the many new features in Microsoft Reporting Services 2005 is one that can truly help reduce the workload for developers—the ability to give end users the power to generate their own ad-hoc reports.


his article explains the major components of Microsoft Reporting Services' ad-hoc architecture and semantic model, and walks you through an end-to-end example that demonstrates how to author, manage, and deliver ad-hoc reports.

Microsoft Reporting Services (referred to as RS, for short, through the rest of this article) is a major component of SQL Server and one of the three pillars of the Microsoft Business Intelligence initiative. It is a complete platform for authoring, managing, and delivering standard reports. Report authors can use a visual Report Designer to create the report layout. Report administrators can publish reports and manage the report catalog using a Web-based Report Manager. Users can request published reports via standard Web protocols (URL or SOAP addressability) or administrators can push reports to end users via subscribed delivery.

While developers may rejoice over Visual Studio.NET Report Designer's ease-of-use, less technically-savvy users may find it too complex and intimidating. Still, one of the most common data analytics features that business users request is the ability to generate reports in an ad-hoc fashion. Ad-hoc reporting lets end users run queries and create reports without having to know the technicalities of the underlying database schema and SQL query syntax.

Author's Note: This article is based on a pre-release build of SQL Server 2005, so the usual disclaimer applies that the final Report Builder implementation is subject to change. To run the demo you will need the February Community Technology Preview (CTP) Build of SQL Server 2005.

The RS Ad-hoc Architecture
To support ad-hoc reporting, the RS 2005 architecture enhancements include several new components—shown in bold type in Figure 1.

Figure 1. The RS Ad-hoc Architecture: This high-level diagram shows the relationship and technology layers between Reporting Services, data sources, and the presentation layer tools.
As in RS 2000, at the heart of the RS architecture is the Report Server, a Web-based middle-tier layer that receives incoming report requests, generates, renders, and delivers reports. Here's a brief description of the three components involved in ad-hoc reporting.

  • Model Designer—Business users need not be familiar with the technical aspects of the database schema to generate an ad-hoc report; instead, developers design and implement one or more ad-hoc models that abstract the underlying data source schema. To design the ad-hoc model, you use the Model Designer, which is one of several RS designers provided by the new Business Intelligence Development Studio. When the model is ready, you publish it to the report catalog to make it available to end users.
  • Report Manager—You manage published ad-hoc models just like any other RS resource. For example, the report administrator can define Windows-based or Forms-based authentication security policies to enforce secured access to the model. To facilitate report and model management, RS provides a separate Web-based application called Report Manager, or administrators can use the new SQL Server Management Studio, which centralizes management of SQL Server, Reporting Services, Analysis Services, and Integration Services installations.
  • Report Builder—End users use Report Builder to create reports against the ad-hoc model. The Report Builder is implemented as a .NET Windows Form application. The Report Server machine hosts the Report Builder assembly, distributing it to end users via the "click-once" technology forthcoming with .NET 2.0. As part of the deployment process, the Report Builder detects and optionally installs the .NET 2.0 framework if it is not already present on the target machine.
When users launch Report Builder, it prompts them to select an ad-hoc model, and then it downloads the model definition from the report catalog. Next, users build reports by choosing entities from the ad-hoc model. The Report Builder user interface is intuitive—similar to PowerPoint 2003. To author a report you simply select one of the pre-defined report templates and drag-and-drop model entities to the report canvas.

Query Translator
While designing reports, users can preview them in the Report Builder. Behind the scenes, the Report Builder generates the report definition on the fly. This differs from generating standard RS reports because the ad-hoc report definition includes a semantic query that describes the model entities selected by the user rather than being pre-defined by a developer. The Report Builder sends the report definition to the Report Server by invoking the Render method of the new ReportExecutionService Web service.

Upon receiving the request, the Report Server extracts the semantic query and forwards it to the Query Translator. The Query Translator translates the semantic query to a SQL query, using the SQL syntax understood by the underlying data source. Then, it executes the query against the data source, retrieves the data, and passes the report definition and data to the Report Processor, which is responsible for rendering the report in its final format.

RS 2005 introduces a new rendering format called Remote GDI (RGDI) for the purposes of ad-hoc reporting. RGDI is a lightweight EMF-style stream that includes additional report metadata. It is not meant to be used by other applications and hence is marked as hidden in the Report Server configuration file.

After the Report Processor generates the ad-hoc report, the Report Server streams it back to the Report Builder which displays the report to the end user. Optionally, users can save reports for later retrieval by uploading it to a report catalog folder (e.g. My Reports).

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date