SQL Server Reporting Services 2005 XML extensions

Introduction

Microsoft SQL Server Reporting Services (SSRS) has support for a plethora of data sources. Out of the box, its data modules, called data extensions, allow you to build reports from SQL Server and Oracle database, as well as any other database that comes with an OLE DB or ODBC provider.
In SSRS2005 a new provider is available: XML Data provider. This enables you to create reports from ADO.NET datasets or XML documents from URL-addressable resources, e.g. Web services.
This entry shows a step-by-step example (basic report , no parameters) how to use this new SSRS 2005 feature.

Here the example code for the report and the webservice.

Download xmlreport.zip - 10.6 KB

Download Northwind.zip - 28.2 KB

I have included several screenshots but some of them are not really readable but together with the example code you will be able to go through the example.

Mind that you will need

• Visual Studio 2005 Service pack 1
• Visual Studio 2005 report Designer (part of SSRS2005 client components)
• SqlServer 2005
• SqlServer 2005 reporting Services
• SqlServer 2005 reporting Services Service pack 2

Background

XML Data Extension

Principle

The common way for a SSRS server-side report to get its data is through the specification of a relational database and the SQL statement to retrieve the data. The report (interpreted by the report engine) is the consumer and the database is the provider of the data.

It's the SQL database data extension that will retrieve the data set specified in the report definition from the configured database during design time and run-time when the report has been deployed.

With the XML data extension is now possible to specify an xml document as data source or a web-service method that returns an ADO.NET (typed) dataset.

Instead of specifying a database and using SQL statements, you must point to a web service and specify the web methods and the Xpath for the returning result.

Benefits

• Out of the box. No need to make a custom Data Extension for processing (serialized) ADO.Net datasets. Also you do not need to deploy extra software to the server and the report designer workstations.
• You can put data retrieval and/or transformation in code behind the web service method instead of using pure SQL or stored procedures.

Concerns

• Pure data push functionality still not available like in for example crystal Reports where you can give data to the report (and report engine). SSRS2005 has this possibility for client-side reports used together with the Report viewer control. Need for custom data processing extension for server-side reports.
• ADO.Net serialization over SOAP is resource-intensive

Example

Webservice

Define a webservice with a webmethod that returns a (typed) dataset.

Configure the web service to use the IIS as host and not the default Visual Studio ASP.NET hosting server when you use VS2005 to build a web service. The web tab appears when you use the ASP.NET web service project template (available in VS2005 Service Pack 1 or you can download it as separate add-on)

Test the web service

Report data source

Specify a data source that point to the previous defined webservice. Choose XML as data extension. The connection string the URL to the webservice

Report data set

Specify the report data set. Instead of SQL syntax you must talk the "web-service" language. You can deduce this information from the web service test page and result page.
Basically you specify

• Name of the webmethod
• Xpath to get result from the web service response document , among others the name of the dataset and the name of the dataTable
  
  With a tool like Webservice Studio you can actually see what the soap messages look like when you execute a web method request.

Execute the data set

Report layout

Make your report layout.

Preview the report.

Report deployment

Deploy the report on your report server.

Run the report from the report server portal. So now the data source ( web-service) will be called from the SSRS server to deliver the data to the report.

Conclusion

I found working with this new extension fun and very useful for some reporting needs we have. We used to work with a custom database extension for ADO.NET datasets. Now you don't need maintaining and deploying this custom extension for SSRS2005.

Sources and recommended reading

• Lesson 2: Defining a Report Dataset for an ADO.NET DataSet from a Web Service, MSDN, http://msdn2.microsoft.com/en-us/library/aa337489.aspx
• Element Path Syntax for Specifying XML Report Data, MSDN , http://msdn2.microsoft.com/en-us/library/ms365158.aspx
• Defining Report Datasets for XML Data, MSDN , http://msdn2.microsoft.com/en-us/library/ms159741.aspx
• XML Query Syntax for Specifying XML Report Data, MSDN ,http://msdn2.microsoft.com/en-us/library/ms345251.aspx
• Create Reports from Any Data Source Using SQL Server Reporting Services Custom Data Extensions, Teo Lachev , http://www.devx.com/dbzone/Article/31336
• Reporting off ADO.NET datasets using the RS 2005 XML Extension , Teo Lachev, http://prologika.com/CS/blogs/blog/archive/2005/11/20/695.aspx
• Professional SQL Server 2005 Reporting Services, Paul Turley, Todd Bryant, James Counihan and Dave DuVarney , Wrox Press 2006