Thursday, 13 September 2007

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.

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


Preview the report.


Report deployment


Run the report



Sources and recommended reading

1 comment:

Anonymous said...

Because of the business domain needs, i need to fire the SQL , manipulate the data and build XML results in my code. How to pass this XML data to reporting service and use this to render the report table?