• Sidebar: Why Organizations Use Managed Reporting
• Illustration: Reporting Services Overview
• Illustration: Report Builder Client
• Illustration: Report Builder Report
• Illustration: Report Designer for Creating Reports

A new end-user reporting feature and other incremental updates in Reporting Services, SQL Server's managed reporting platform, could encourage upgrades to SQL Server 2005. The new end-user feature, called Report Builder, is based on technology acquired from ActiveViews in Apr. 2004 and allows users without programming skills to construct, format, and publish ad hoc reports. However, the new feature works only with SQL Server data sources and eats into ISV opportunities to supplement Reporting Services with add-ons—for example, Microsoft partner Cizer offers a suite of tools with features similar to Report Builder.

What Is Reporting Services?

First introduced as an add-in for SQL Server 2000 in Jan. 2004, Reporting Services is a collection of development tools and programming interfaces, run-time services, and management utilities used to define, generate, store, and manage reports. Although Reporting Services is part of SQL Server, it can be used to build reports from other databases, such as Oracle. (For more information about the advantages of server-based managed reporting, see the sidebar "Why Organizations Use Managed Reporting".)

Reporting Services development tools, hosted in Visual Studio, help developers create report definitions, which identify the data sources to be used in reports, contain the queries that extract data from those sources, and include instructions for formatting and rendering those data in actual reports. Developers publish completed report definitions to Reporting Services' core server component, the report server. Report servers store report definitions in SQL Server databases, process report definitions (for example, querying underlying data sources and formatting reports for display), and control report scheduling and delivery. Users view reports through various mechanisms—most commonly by pointing a Web browser at the Report Manager, a Web-based report portal that ships with Reporting Services. The Report Manager passes user requests to report servers for processing. (For an illustration and definitions of the components that comprise Reporting Services, see "Reporting Services Overview".) Reporting Services is part of SQL Server's business intelligence platform, which also includes Analysis Services and Integration Services. It has gained popularity among organizations that need to distribute reports to users on an ongoing basis and among software vendors (including Microsoft) building Web-based reporting features into applications. For example, Microsoft Operations Manager (MOM) 2005 includes a reporting system built on Reporting Services.

New Feature: Ad Hoc Reporting

SQL Server 2005 Reporting Services (RS 2005) will debut Report Builder, a new feature that allows nondevelopers to define, format, and publish reports without requiring SQL Server programming skills or an understanding of SQL Server concepts. Report Builder is based on technology acquired with Microsoft's purchase of ActiveViews in Apr. 2004. Although it will likely support other data sources in subsequent releases, Report Builder will initially be able to generate reports based on data in SQL Server only (this includes both relational databases and Analysis Services cubes).

Different Approach to Report Development

Report Builder could prove interesting to relatively technical end-users who want the flexibility to design and create reports without involving development or IT staff.

It offers an alternative to Reporting Services' developer-oriented report creation tools, namely the Visual Studio—based Report Designer, which shipped with SQL Server 2000 Reporting Services (RS 2000) and remains a core feature of RS 2005. With Report Designer, developers decide in advance which data will be contained in reports and how reports are formatted. In contrast, Report Builder puts those decisions in the hands of business managers and end users, albeit with some limits compared with the report development capabilities of Report Designer. For example, Report Builder users cannot create reports that contain conditional formatting (displaying negative numbers in red, for instance).

Report Builder involves two user groups, developers and end users.

Developers create report models. Before end users are able to create reports, developers build report models using a new Visual Studio plug-in called the Report Model Designer. Report models contain data-source connection information, a data source view (which identifies a subset of data in the data source), and a map of the data source view's tables, data elements, and their relationships. Developers publish these report models on a report server.

Users construct and publish reports. Using the Report Builder client, end users create report definitions based on published report models. Users define a report's layout (for example, a chart or table), choose data from a report model to populate the report, and specify formatting (such as text font and color). Once reports are defined, users can run them and view the results with the Report Builder client. Users can also publish report definitions on a report server, thus making those reports accessible to other users through the Report Manager portal site.

The Report Builder client is a .NET Windows Forms application—users click on the Report Builder link in the Report Manager, which causes the client to download and run on the user's desktop. (The Windows Forms application used for creating ad hoc reports is illustrated in "Report Builder Client"; a report created with Report Builder is shown in "Report Builder Report".)

Existing Features Updated

Along with the introduction of Report Builder, RS 2005 provides several incremental updates for users, developers, and administrators. However, because RS 2005 will follow the release of RS 2000 by only a year and a half, the new release will not contain the sweeping changes of many other SQL Server 2005 feature areas.

More Flexibility for Users

Several incremental updates will improve the way users view and work with reports and report data. These updates include the following:

Report printing is provided by a new ActiveX control that offers standard print and formatting features, such as listing available printers, previewing reports before printing, and changing report layout and formatting. Previously, users were required to export reports to a file format of their choice (Excel, Word, or PDF, for example), open the report in the application appropriate to that format, and use that application's printing features to print reports.

(The report printing feature, which appears in the Report Manager, is also included in RS 2000 SP2, which was released in Apr. 2005.)

New SharePoint Web Parts. Two Reporting Services Web Parts let users navigate and view reports from within Windows SharePoint Services (WSS) or SharePoint Portal Server (SPS) sites. This could improve the utility of those sites for users; for example, users do not have to navigate to a separate site (e.g., the Report Manager) to view reports.

The first Web Part, called the Report Explorer, lists available reports on a report server. The second Web Part, called the Report Viewer, is used to view reports. (Reporting Services Web Parts also debuted in RS 2000 SP2.)

Multivalue parameters. Developers working with Report Designer can create reports that accept end users' parameters, which are useful for filtering data or selecting report layout options. For instance, a developer could specify that a sales report must contain a region parameter; by selecting from a list of valid parameter values, end users could then choose to view sales data for specific regions.

In RS 2000, users could select only single values for parameters—a user could filter on North America or Latin America, but not both, for instance. RS 2005 allows users to select and filter report output using multiple parameter values (e.g., North America and Latin America) simultaneously.

Interactive sorting. RS 2005 allows end users to sort report data by clicking on column headings in the rendered report. To build a sortable report in RS 2005 with Report Designer, developers simply identify and tag the appropriate columns at design time. (Columns in reports created with Report Builder are sortable by default.) Creating sortable reports with RS 2000 is more laborious: developers must either write separate SQL queries for each desired sort order of a report's underlying dataset or include user-supplied parameters to specify sort order in a query.

The net result is a reduction in the amount of code developers must write to create sortable reports in RS 2005. In addition, sorting report data will be faster for end users, since processing sort requests in RS 2005 does not require underlying data sources to be queried.

Date picking parameters. A new calendar control will make it easier for users to select date ranges for filtering a report. The feature is similar to the date picker controls found in many travel Web sites.

New Tools, Controls for Developers

RS 2005 includes several productivity enhancements for developers using Report Designer. In addition, new controls in Visual Studio 2005 will aid developers building reporting capabilities into applications.

(For an illustration of the Report Designer in RS 2005, see "Report Designer for Creating Reports".)

Compared to its predecessor, RS 2005 offers developers the following incremental improvements:

Graphical MDX query designer. Report Designer allows developers to build reports using data stored in SQL Server Analysis Services cubes. To do so, developers construct queries in the multidimensional expression (MDX) language, a special query language for accessing and manipulating data stored in cubes.

Report Designer includes a new graphical query designer to help with this task. A "design view" lets developers build MDX queries by dragging and dropping cube data elements into a report. In addition to simplifying the task of writing basic MDX queries, the designer can also serve as a training tool—a separate code view displays the actual MDX code generated by the graphical designer.

Expression editor enhanced. All data items in report definitions are represented as Visual Basic expressions. (Microsoft has not indicated that it will support expressions in other .NET languages, such as C#, in future releases.) Expressions can be simple representations of data items extracted from a table, or complex functions that aggregate data or apply conditional formatting.

RS 2005 improves the usability of the Report Designer expression editor, which developers use to construct and edit expressions. Specifically, the editor lists available functions (such as math or string manipulation functions) available to developers and includes IntelliSense features, which can help complete expressions and check expression syntax on the fly.

Visual Studio controls. Visual Studio 2005 will include a set of Reporting Services controls that will reduce the amount of code application developers must write to build report navigation and viewing capabilities into applications. Developers can embed report constructs in applications: this could include tables or matrices for displaying report data, or dialog boxes to enable users to input parameters. For example, a custom health-care application could display a table of patient records and incorporate a dialog box to allow physicians to select records for particular patients. The controls work with report definitions stored and processed on RS 2005 report servers, but do not work with RS 2000 report servers. The controls can also work with report definitions stored in the file system and with data supplied by the application itself. The latter method allows developers to include predefined reports in applications without requiring those applications to access servers running Reporting Services.

Visual Studio 2005 will include two versions of the controls: one for ASP.NET Web pages and another for Windows Forms applications.

WMI Provider Rewritten, 64-Bit Support

RS 2005 includes a rewrite of the Reporting Services Windows Management Instrumentation (WMI) provider, an API that allows management applications or scripts to perform server administration tasks, such as remotely deploying and configuring a report server.

Microsoft claims the new WMI provider will give administrators more control over configuring reporting services environments. In fact, Reporting Services includes a new configuration tool that is built on top of the provider. However, as of the June 2005 Community Technical Preview (CTP) release of SQL Server 2005, the WMI provider is undocumented. (CTPs are interim product releases between major milestones, such as beta releases; however, CTPs do not undergo the same level of testing as beta releases.) In addition, the WMI provider is not backward compatible: management applications or scripts that used the RS 2000 WMI provider will need to be rewritten for RS 2005.

Finally, Microsoft will ship 64-bit versions of Reporting Services, supporting both Intel's Itanium architecture and the Extended 64-bit (x64) architecture, designed by AMD and subsequently adopted in some Intel processors. Support for 64-bit servers could help IT workers in large shops consolidate Reporting Services infrastructure on fewer servers and reduce the processing time of memory-intensive reports (for example, a report that extracted, summarized, and sorted data from a large database).

Caveats and Considerations

Although RS 2005 is not a massive technical departure from RS 2000, several considerations warrant the attention of existing and prospective Reporting Services customers.

Developers and administrators should consider the following:

Proliferation of tools. The addition of Report Builder adds another vector for report development that IT developers and business users must consider: when to use Report Builder and when to provide predefined reports created with the more powerful and flexible Report Designer.

Report Builder is a SQL Server-only tool. Using Report Designer, developers can build report definitions from a variety of Microsoft and non-Microsoft data sources; however, reports created with Report Builder are limited to SQL Server relational databases and Analysis Services cubes. Future versions of Report Builder will likely provide more general data access capabilities.

New Web services API. A Web services API provides programmatic access to Reporting Services functions, such as configuring report servers, scheduling and distributing reports, and processing and rendering reports.

RS 2005 updates this API, adding functions to support programmatic access to new capabilities in RS 2005, such as interactive sorting of reports. In addition, the new API contains separate classes for report-server management functions and report rendering and processing functions. To maintain backward compatibility, Microsoft will ship the RS 2000 Web service with RS 2005. However, the old API has not been updated; thus, developers must use RS 2005's new Web services API to access new features.

Availability and Resources

At its TechEd conference in June 2005, Microsoft announced that it would launch SQL Server 2005 the week of Nov. 7, 2005. The most recent SQL Server 2005 CTP, released in June 2005, contains the Report Builder feature. Report Builder will ship with the SQL Server 2005 Workgroup, Standard, and Enterprise Editions; it will not be available with the SQL Server Express Edition. The Express Edition will contain other Reporting Services features; however, some of these features will be scaled back. For example, Reporting Services in Express Edition will not work with Analysis Services cubes.

The SQL Server 2005 site is www.microsoft.com/sql/2005/.

The most recent SQL Server 2005 CTP can be downloaded at www.microsoft.com/sql/2005/productinfo/ctp.mspx.

Reporting Services home page is www.microsoft.com/sql/reporting.

The SQL Server 2005 business intelligence platform is described at www.microsoft.com/technet/prodtechnol/sql/2005/evaluate/dwsqlsy.mspx.

For more information on Report Services, see "SQL Reporting Services Launches" on page 3 of the Mar. 2004 Update.

Reporting Services 2000 SP2 is described in "Web Improvements in Reporting Services SP2" on page 13 of the Apr. 2005 Update.