Reporting off ADO.NET datasets using the RS 2005 XML Extension

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. #


  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!