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.
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.
- 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)
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
- 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
3 comments:
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?
You may want to check out this site https://pro-essay-writer.com/blog/how-to-become-better-editor for some tips on how to became better editor. Good luck
Asian mail order brides are great supporters and asian mail order wife cheer up their lovers anytime they feel bad, so you won't be bored or sad when a local lady is around you.
Post a Comment