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:

Value=Fields!name.Value

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.Report Management and Execution
RS stores reports in a hierarchical, file-system type of structure. The structure entry point contains one or more folders. Each folder can contain reports, shared data sources, and other sub folders as well. Using the Web Service API, you can integrate report management and execution within an application. Listing 1 shows how to run a report and save the results on hard disk as PDF files.

You can cache and schedule RS reports. A Windows Service triggers the execution of scheduled reports by calling into the report processor. The report outcome is passed to a Delivery extension, which forwards the report to its destination recipient.

You don’t have to code against the RS API to manage RS and run reports, however. The Report Manager component is a set of ASP.NET pages that provide UI access to Report Management (reports and data sources, roles, scheduling, subscriptions, report caches, etc.). The Report Manager’s home page is located at http:///Reports.

You don’t even have to do any coding to run reports. As an alternative to Web service access, RS offers URL-based access to reports, which uses the URL query string to convey execution parameters. The default location for URL-based report execution is http:///reportserver?.

Figure 2. Protect Reports Using Role-based Security: You can secure each report or folder within the hierarchical structure by assigning execution and management permissions through roles.

Security Architecture?Seriously
The security architecture of RS is proof that Microsoft has started to take security seriously. During installation, RS asks for the Windows identity it will use to run the Web service and the Windows service. Additionally, it asks whoever is performing the install to specify the credentials that it must use to connect to the SQL Server Repository. The report server, however, will never connect to a remote server using its default credentials. While configuring each report, you must specify how credentials will be provided to the remote data source. The available options are:

• Prompted credentials
• Windows NT integrated security
• Stored credentials
• No credentials

RS uses encryption keys to secure data such as stored credentials (which are compulsory when running scheduled reports), connection information to the repository database, etc. Sensitive content on the report server is encrypted with a symmetric key. The symmetric key is itself encrypted with an asymmetric public key (created by the Report Server Windows service when it first runs) that corresponds to the computer and the user account under which the Report Server Windows service runs.

The RS security infrastructure uses a role-based security model to protect access to general configuration settings, report management, and report execution. Currently, RS does not offer any built-in authentication mechanism. It relies upon Windows Security. In other words, you currently can assign role ownership using Windows identities only. This doesn’t mean you cannot develop solutions based on custom authentication schemas such as ASP.NET Forms Authentication. However, you’d have to develop all the plumbing yourself (calling the LogonUser and stuff like that).

RS provides a distinction between system-wide and item-level tasks and, as a consequence, provides separate management for them. System-level tasks include operations such as managing roles, modifying shared schedules and site-level configuration settings, etc. Item-level roles protect access to specific items within the hierarchical report structure. Item-level roles (and their mapping to Windows identities and groups) are defined globally, but you need to navigate to a specific folder or report to assign a role to that resource. When a role assignment is specified at the folder level, all the reports and sub folders the folder contains inherit the parent’s settings (see Figure 2).

Reporting Services Extensibility
The data processing extensions, the rendering extensions, and the delivery extensions allow programmers and third-party vendors to register additional extensions. Built-in data processing extensions provide access to SQL Server, OLEDB, OLAP, Oracle, and ODBC. Built-in rendering extensions are CSV, Excel, HTML, Web Archive (MHTML), PDF, TIFF, and XML. Finally, built-in delivery extensions let you send report results via e-mail or drop them on a file server. Oddly, RS currently doesn’t support custom rendering extensions. This likely will change in the next version of RS, slated with the release of Yukon (SQL Server 2005).

Implementing an extension entails writing a bunch of .NET classes that implement specific interfaces defined in the Microsoft.ReportingServices.Interfaces assembly. The resulting assembly must be registered into the RS repository and given the required permission to execute. The following section addresses such registration details with a step-by-step guide to developing and deploying a Data Provider Extension.

Developing an XML Data Provider Extension
Quite often, the hierarchical structure of XML is better for representing data structures than tabular data. Additionally, XML is much more manageable when loaded in memory, thanks to the powerful query capabilities offered by XPath. Depending on your application’s requirements and the amount of data you need to manage, you basically have two options (or some combination of the two) to store XML documents:

1. Store XML documents in a database, mapping text elements and attribute values to table fields.
2. Persist the document as-is in its angle-bracket representation on the file system (typically conceiving a folder structure and naming schema that helps in identifying specific XML documents).

Reporting on XML data structures is getting almost as common as reporting on database data sources. Tools like ActiveReports have accepted an XML document as data source for two or three years now. SQL Server Reporting Services doesn’t natively support an XML stream as a valid data source. However, its flexible and extensible architecture lets you easily implement and plug in an XML data provider engine, which extracts and manipulates XML data, and passes it out to the report generator in a tabular form.

The interfaces you are required to implement when developing a data provider extension are modeled after a subset of the Microsoft .NET data provider interfaces. RS requires data extensions to provide data using a DataReader-like approach (i.e., using a forward-only, read-only cursor). The interfaces you implement when developing a custom data extension are:

• IDBConnectionExtension
• IDBCommand and IDBCommandAnalysis
• IDataParameter
• IDataParameterCollection
• IDbTransaction
• IDataReader

If you know the ADO.NET infrastructure, all the interfaces except IDBCommandAnalysis should look familiar. The IDBCommandAnalysis is inherited from IDbCommand. It is just a helper (optional) interface that provides support for parameters passing at design time. If it is implemented, VS.NET infers the required report parameters and asks for them when the report query runs within the designer.

You typically implement each interface (except the IDbCommand, IDBCommandAnalysis pair) in a separate, distinct class, placing code that raises exceptions when a non-supported functionality, such as transactions or parameters support, is required.

Implementation Details
The source code download that accompanies this article provides a fully functional XML data provider extension. Most of the data extension provider implementation consists of boilerplate code, so let’s focus on the most relevant implementation details.

The XMLConnection class implements the IDBConnectionExtension interface. The connection string syntax I’ve defined requires only an XMLDIRPATH parameter where the directory containing the XML files is specified (see Figure 3).

Figure 3. Define the XML Data Provider Connection String: The first step to setting up a report with the XML Data Provider is to define a connection string pointing to a file system directory.

In the XMLCommand class, I defined a command-text syntax that lets you define the XML file name containing the data and an XPATH syntax that maps a generic XML structure into a tabular form. I decided to provide the file name as a command parameter named FileName. For the XPath syntax, I opted for an approach very similar to the OPENXML command in SQL Server 2000. It indicates the row selection criteria and, within the select XML nodes, the field’s selection criteria. The query syntax is composed of three parts:

The field’s selection list is optional if you specify attr or elem in the second part. Using attr as the second parameter instructs the data provider extension to consider as row fields all the attributes of the XML nodes element that the row selection criteria extract. Using the elem option instructs the data provider extension to consider as row fields all the child elements of the XML nodes element that the row selection criteria extract. The following code uses attr:

  XML provider query text: /books/book,attr

You must specify mix as the second argument if fields are placed in an irregular mixed path relative to the row element. Notice that the fields in the selection expression are separated by the pound sign (#):

                 XML provider query text: /books/book,mix,@id#title#../@name

Most of the XML data’s functionalities are implemented in the XMLDataReader internal constructor, which is called by the XMLCommand’s ExecuteReader method. This constructor accepts a connection and a command object. The constructor implementation prepares the object internal state (using an XPathNavigator object) for subsequent calls to the Read method, which RS uses to pull data out of the data source (see Listing 2).

Note that the XMLDataReader class also is instantiated when the VS.NET designer tries to determine the field’s number, name, and type. In this case, the Read method won’t receive any subsequent calls. In the current implementation, I have no way to determine the row structure other than executing the actual XPath command (and assuming that the first row’s structure is the same for all the rows set).You could extend the sample to take XML schemas into account. With an XML schema, the interaction between the XMLCommand and the XMLDataReader classes can be re-factored to move XPath query executions to the top of the Read method implementation.

Installing the Extension
The first step to deploying the extension DLL is copying it to the report server bin directory (the default location is C:Program FilesMicrosoft SQL ServerMSSQLReporting ServicesReportServerin) and to the report designer bin directory (the default location is C:Program FilesMicrosoft SQL Server80ToolsReportDesignerin). In the downloadable code that accompanies this article, the C# project that implements the data extension does this automatically using a bat file run as a post-build step.

As a second step, you must add the following entry to both the RSReportServer.config and RSReportDesigner.config files under the Data element:

To enable the generic query designer for the sample data-processing extension, add the following entry to the RSReportDesigner.config file under the Designer element:

The following final steps register the new data extension to the Code Access Security infrastructure:

1. Open the report server policy configuration file (rssrvpolicy.config). The default location for it is C:Program FilesMicrosoft SQL ServerMSSQLReporting ServicesReportServer.
2. Data-processing extensions must be granted full trust. Therefore, add the following code group information to the configuration file as a child of the root code group (IMembershipCondition= AllMembershipCondition, PermissionSetName=”Nothing”):

3. If you are using the sample data-processing extension in Report Designer, repeat the previous step using the Report Designer preview policy configuration file (rspreviewpolicy.config). Add the following code group:

The default location for the rspreviewpolicy.config file is C:Program FilesMicrosoft SQL Server80ToolsReport Designer.

If you have set everything up properly, just fire up VS.NET and create a new Report Definition project using your language of choice. The newly registered XML Data Provider should appear as one of the available data source choices. To debug the extension, run another instance of VS.NET, open the XML Data Extension project, and attach the debugger to the other instance of VS.NET containing the report definition. Place some breakpoints and then ask for either a data preview using an exclamation point (!) in the Report Designer Data Tab or a report preview using the Preview Tab. Note that to recompile the Data Extension assembly you must exit the VS.NET instance that loaded the Report Designer (closing the solution is not enough).

RS: Already Good, and Will Improve
RS is an impressive product, considering it is just in version 1.0 (Microsoft will release a Service Pack quite soon, while version 2.0 is slated to ship with Yukon). In most situations, it fits the bill as-is, while direct access to its Web Services API, custom extension development, and third-party tools (such as runtime designer) should solve advanced application requirements.

Feel free to extend the connection string, parameter, and query syntax to fit your specific application needs.