Click here to Skip to main content
15,867,141 members
Articles / Database Development / SQL Server

SQL Server Reporting Services 2005 XML Extensions

Rate me:
Please Sign up or sign in to vote.
4.37/5 (8 votes)
25 Sep 2007CPOL4 min read 99.2K   1.3K   54   4
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 article shows a step-by-step example (basic report, no parameters) how to use this new SSRS 2005 feature.

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 is the example code for the report and the webservice.

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.

Screenshot - SQLextension.jpg

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

Screenshot - xmlExtension.jpg

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.

Screenshot - aspnetwsprojecttemplate.jpg

Screenshot - Webservice.jpg

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):

Screenshot - webtab.jpg

Test the web service:

Screenshot - testwebservice.jpg

Screenshot - webserviceresult.jpg

Report Data Source

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

Screenshot - datasource.jpg

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 looks like when you execute a web method request.

Screenshot - wsstudio.jpg

Screenshot - dataset.jpg

Execute the data set:

Screenshot - runDataset.jpg

Report Layout

Make your report layout.

Screenshot - layout.jpg

Preview the report.

Screenshot - preview.jpg

Report Deployment

Deploy the report on your report server.

Screenshot - deployment.jpg

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.

Screenshot - runreport.jpg

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

History

  • 25th September, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Belgium Belgium
Alexander Nowak is .NET application developer working for Capgemini Belgium. Topics of interest are .NET Application architecture ,design and programming as well as Software development practices.

Comments and Discussions

 
SuggestionCool Pin
vasile torpedo24-Feb-13 5:07
vasile torpedo24-Feb-13 5:07 
GeneralGood Article Pin
vikas amin5-Oct-09 5:23
vikas amin5-Oct-09 5:23 
GeneralGood one Pin
feeza012518-Aug-09 22:54
feeza012518-Aug-09 22:54 
GeneralShow report using form authentication in SSRS 2005 Pin
MSinha4-Dec-07 3:00
MSinha4-Dec-07 3:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.