Prologika Forums
Making sense of data
Reporting off ADO.NET datasets using the RS 2005 XML Extension

Blogs

Prologika (Teo Lachev's Weblog)

Books

Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling)Learn PowerPivot and Analysis Services-Tabular at your own pace with our latest book Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling). It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.

Training

Applied Microsoft SQL Server 2008 Reporting Services

We offer onsite and online Business Intelligence classes!  Contact us about in-person training for groups of five or more students.

For more information or to register click here! 

Syndication

Archives

In case you missed this little gem, RS 2005 comes with a brand new XML Extension which allows you to report off an XML data returned from an URL-addressable resource, such as an ASP.NET page or a web service. For example, suppose that you have a web method called GetOrders that return the customer orders as a typed dataset (table in my case) and has the following signature:

 

[WebMethod]

public CustomerOrders.SalesOrderHeaderDataTable GetOrders(int customerID)

 

You can create a report from the returned dataset by using the RS 2005 XML dataset extension:

  1. Create a data source that uses the XML extension by selecting XML as the data source type.
  2. Enter the URL address of the web service in the connection string, e.g. http://localhost/AdventureWors/Customer.asmx
  3. Once the data source is ready, create a dataset with the following query:

 <Query>

   <Method Namespace="http://tempuri.org/" Name="GetOrders"/>

   <SoapAction>http://tempuri.org/GetOrders</SoapAction>

   <ElementPath IgnoreNamespaces="true">GetOrdersResponse/GetOrdersResult/diffgram/DocumentElement/SalesOrderHeader</ElementPath>

</Query>

 

Unfortunately, the documentation stays shy about all the parameters supported by the XML extension but it is fairly easy to deduce what’s going on here.

The Method element describes the web method to be called. This is somewhat redundant since the SoapAction element does the same and explained in more details here. The ElementPath describes the full XPATH to the elements to be retrieved and flattened in a two-dimensional resultset. Finally, the IgnoreNamespaces attribute tells the XML extension to ignore any schema namespaces when parsing the XML content.

 

   4. To pass a parameter(s) to the web method, use the Parameters tab and link the report parameter to the query parameter as usual, e.g:

 

Name                          Value

customerID              =Parameters!CustomerID.Value

 

One cautionary note that bit me really bad is that the parameter name is case-sensitive and must much the argument name of the web method verbatim. Thus, if you change the parameter Name to CustomerID, you will find out that the XML extension doesn’t pass the parameter value.

 

Thanks for to the XML Extension, in my cases, reporting off XML data from URL-addressable sources may not require a custom data extension with RS 2005. Happy reporting!


Posted Sun, Nov 20 2005 12:47 PM by tlachev
Filed under:

Comments

Andrea Dottor - il silenzio di un urlo » Blog Archive Link della sessione sui Reporting Services » wrote Andrea Dottor - il silenzio di un urlo &raquo; Blog Archive Link della sessione sui Reporting Services &raquo;
on Wed, Nov 15 2006 4:01 AM
bdy73 wrote re: Reporting off ADO.NET datasets using the RS 2005 XML Extension
on Mon, Jan 29 2007 5:26 PM

having a little difficulty with 2 areas.  First, I have a ASP.Net 1.1 web service and it throws an error because the datatable in 1.1 cannot be serialized.  So, I've changed it to return a dataset, but I'm not sure how to set up the ElementPath to get to it.

Braulio_Malaga wrote Passing Dynamic DataSet to ReportViewer or SSRS
on Thu, Mar 29 2007 4:56 AM

Many people misses a push mode like Crystal Report Does, passing a DataSet at runtime to the report engine....

Useful Reporting Services (Advanced) Links « Omar’s Crib wrote Useful Reporting Services (Advanced) Links &laquo; Omar&#8217;s Crib
on Tue, Jun 12 2007 5:12 AM

Pingback from  Useful Reporting Services (Advanced) Links &laquo; Omar&#8217;s Crib

http://prologika.com/CS/blogs/blog/archive/2005/11/20/695.aspx wrote http://prologika.com/CS/blogs/blog/archive/2005/11/20/695.aspx
on Sun, Mar 16 2008 4:26 PM