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


Build an XML Data Extension for SQL Server Reporting Services

Get to know the main features of SQL Server Reporting Services and learn how to extend it with an XML Data Provider Extension.

QL Server Reporting Services (RS) is:
  • A new component of the SQL Server platform that Microsoft recently made available (January 27, 2004)
  • A server-based package that comes in four configurations: Standard Edition, Enterprise Edition, Developer Edition, and Evaluation Edition
  • Part of the SQL Server 2000 license (If you have a licensed copy of SQL Server 2000, you may run RS on the same server for no additional license fee.)
Microsoft designed the SQL Server RS architecture to expose all its SQL Server functionalities through a Web service-based interface. With Report Manager (an ASP.NET-based application) and URL access to reports built on top of this Web service interface, developers get out-of-the-box, IE-based access to RS functionalities without having to write a single line of code. Figure 1 shows the overall architecture of RS.

As you can see, RS is a totally server-based solution. As such, it doesn't require you to install any additional software on client machines in order for them to consume reports. A SQL Server database acts as the repository for reports definition and the overall server configuration. The API for storing, managing, and running reports is exposed as a set of Web service methods. The Web service interface dispatches requests to the report processor. The report processor calls into the selected data-processing extensions (unless caching options state otherwise) and then into a rendering extension. While Web service access is the preferred way to access RS management functions, a WMI provider is available as well.

Figure 1. The SQL Server Reporting Services Architecture: A Web service-based interface provides access to all Reporting Services functionalities. The Report Manager, an ASP.NET-based application, and URL access to reports provide ready-to-use, IE-based access to Reporting Services functionalities without a single line of code.

Report definitions are stored using a new XML-based, ad-hoc language called RDL (Report Definition language), which Microsoft developed with some other ISVs. Each report is an RDL document containing references to data sources, the report layout (in a neutral way), and rendering logic. RDL documents are stored in the reporting database. It's up to the rendering extension to generate the human-readable output stream according to layout specifications.

Installing RS on a computer that runs VS.NET 2003 adds a couple of new project types: Report Project and Report Project Wizard. These project types allow you to define data sources and design reports visually, as well as automate the publishing of reports to the SQL Server Repository.

Objects within a report are bound to the data source via expressions. Most times, you bind the value property straight to a data field in the Designer:

However, you can bind to other properties as well, such as the foreground color of a text field in the report. The code snippet below sets the foreground color to red if the Cost record field is greater then the Revenue record field. Otherwise, it sets it to black:

Color =IIf (Fields!Cost.Value > Fields!Revenue.Value, "Red", "Black") 
The report execution environment obviously offers a wide range of aggregate functions, and you can even develop custom functions that are invoked within an expression. Such functions can be declared within the report definition (right click the report designer, select properties, and then the Code tab), or you can implement them in .NET assemblies (provided you've registered them in the RS repository). Note that you need to modify the RSReportServer.config file to grant execution (and eventually additional) permissions to your custom assembly. (See RS Books online for further information.)

However, there isn't a full scripting environment allowing you to interact and modify the report object model on the fly. In most situations, conditional expressions using the IIf statement fit the bill, but you'll find implementing some sophisticated formatting logic difficult (and sometimes impossible) in highly dynamic reports. Microsoft is aware of this issue. According to feedback I got from the RS Newsgroup, Redmond will offer an automation model for the report object and some report-generation-related events (you can hook to interact with the report object) in the next version of RS.

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