Using the XML Data Extension to leverage an ADO.NET dataset in your SQL Reporting Services Report.


Here’s a quick walkthrough on how to return  records in an ADO.NET dataset (via a Web Service) and use them in a report. Many, many, many thanks to Ian Roof and Mary Lingel for forcibly pushing me in the right direction when I was clueless.


First, here’s the WebMethod we’ll be using to return our data:


    public DataSet GiveMeData()
    {


        SqlDataAdapter myDa = new SqlDataAdapter(“Select * from HumanResources.vEmployee”, “server=.;database=adventureworks;uid=sa;pwd=fooSqlMrT.Rocks”);
        DataSet myDS = new DataSet();
        myDa.Fill(myDS, “vEmployee”);
        return myDS;
    }

   
1. Browse to the ASMX page of your service, and choose the GiveMeData method. Note the SOAP 1.2 response you should expect:



HTTP/1.1 200 OK
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length


<?xml version=”1.0″ encoding=”utf-8″?>
<soap12:Envelope xmlns:xsi=”
http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:soap12=”http://www.w3.org/2003/05/soap-envelope“>
  <soap12:Body>
    <GiveMeDataResponse xmlns=”
http://localhost/EmployeeInfo“>
      <GiveMeDataResult>
        <xsd:schema>schema</xsd:schema>xml</GiveMeDataResult>
    </GiveMeDataResponse>
  </soap12:Body>
</soap12:Envelope>


Note the GiveMeDataResponse and GiveMeDataResult elements? You’ll use these later.


2. Click Invoke to call the method.


3. Explore the document which is returned and note the following:



  • When trying to navigate down to our data, we’re going to hit a diffgram
  • Next, we’ll hit NewDataSet (we created a dataset in our webmethod without naming it, so NewDataSet is a default name).
  • Finally, we hit vEmployee, which is what we named the table in our dataset in the webmethod.
      

4. Now that you understand how to navigate to the data (more on that later) we’ll actually create a report.


5. Create a new report, and create a new dataset. You’ll need to define the datasource for the dataset, so create a new datsource, too.


6. Choose the XML data extension as the Type of extension to use. Your Connection String needs to point to your web service, so it’ll look something like this:


 http://myHappyMachine/appSpace/Service.asmx


7. Creating the query is the most difficult part, and what I had problems with. First, you must specify a SoapAction so that we know what WebMethod (GiveMeData, in this case) to call. Do so like this:


<Query>
  <SoapAction>http://localhost/appSpace/GiveMeData</SoapAction>
</Query>
 


8. Next, you have to actually specify HOW to pull the data out of the response you get from the web service. We’re going to continue to play with what we started in step 7:


<Query>
  <SoapAction>http://localhost/appSpace/GiveMeData</SoapAction>
  <ElementPath IgnoreNamespaces=”True”>GiveMeDataResponse{}/GiveMeDataResult{}/diffgram{}/NewDataSet{}/vEmployee{EmployeeID,FirstName,MiddleName,LastName,JobTitle,Phone,EmailAddress,EmailPromotion,AddressLine1,City,StateProvinceName,PostalCode,CountryRegionName, AdditionalContactInfo}</ElementPath>
</Query>


Notice our path? First we hit GiveMeDataResponse and GiveMeDataResult from step 1. Then, we name the diffgram, dataset and table name from step 3. Finally, we list the field names we want to bring back.


9. Click “!” to test out your query…it should return a resultset.


That’s it…now that you have data, you can go ahead and build a report. To filter the data more, you can add a filter directly on the dataset or data region itself.

Comments (37)

  1. Alexei Pavlov says:

    How about to create "GiveMeData(string someParameter)".

    I’ve found a big problem with this. RS doesn’t passes parameter value to webservice (IE does!).

  2. Ian Roof says:

    Alexei,

    RS should pass any regular query parameters in the SOAP request. You can also specify the parameters directly in the query. Also, parameters specified directly in the query are overridden by the values of the regular dataset query parameters.

    Here is an example of calling a web method that requires two parameters, a query string and a database name, and returns the resultant dataset.

    <Query>

      <!– Specify both the SoapAction and Method elements when the webservice namesapace ends in a ‘/’ –>

      <SoapAction>http://tempuri.org/RunQuery</SoapAction&gt;

      <Method Namespace="http://tempuri.org/&quot; Name="RunQuery">

      <Parameters>

         <Parameter Name="query" Type="String">

            <DefaultValue>Select * From Sales.Customer</DefaultValue>

         </Parameter>

         <Parameter Name="database" Type="String">

            <DefaultValue>AdventureWorks</DefaultValue>

         </Parameter>

       </Parameters>

       </Method> <ElementPath>RunQueryResponse{}/RunQueryResult{}/diffgram{}/DataSetName/TableName</xmldp:ElementPath>

    </Query>

  3. Craig Haroldson says:

    We use objects as parameters for our web method (per MS best practices).  For example:

    [WebMethod]

    public MyResponse GetData(MyRequest request)

    {

     // call to get data here based on request contents

     

     return new MyResponse();

    }

    Is it possible to pass in MyRequest to this method from the Query?

  4. Moby says:

    Hi,

    I’ve been trying to deploy a report which uses XML data source and data is returned by a web service in the form of dataset. Now the problem is when i preview the report it works displays results fine but when i deploy it and view it using web browser it simply gives error message stating:

    An error has occurred during report processing. (rsProcessingAborted)

    An attempt has been made to use a data extension ‘XML’ that is not registered for this report server. (rsDataExtensionNotFound)

    I also checked the data source using Manager and found warning msg saying :

    The data processing extension used for this report is not available. It has either been uninstalled, or it is not configured correctly.

    Please help me if i am missing some configuration setting or need to install some thing on top of my SQL Server Express 2005.

    Thanks,

    Moby

  5. XML data sources are not supported in SQL Express:

    http://msdn2.microsoft.com/en-us/library/ms365166.aspx

    (See "Unsupported Features" section.

  6. Carolyn says:

    my dataset isn’t returning any data. it’s just returning one row that contains empty data. the web service returns data, i tested it by hitting the "invoke" button.

    this is my query:

    <Query>

    <ElementPath IgnoreNamespaces="True">

       GiveMeDataResponse {}/

       GiveMeDataResult/diffgram{}/

       Results {}/Table {CompanyID, CompanyName, ABN, Abbreviation}

    </ElementPath>

       <SoapAction>

           http://tempuri.org/GiveMeData

       </SoapAction>

       <Method Namespace="http://tempuri.org/&quot;

           Name="GiveMeData">

       </Method>

    </Query>

  7. StillRockin77 says:

    you need opening/closing braces right after "GiveMeDataResult".  i would also not have any blank spaces right before any opening brace.  in fact, to be sure just take out any/all blank spaces in the entire element path value (except for maybe in the comma’d list of field names, after each comma).

  8. Severin Ulrich says:

    Hey thanks for this great step by step tutorial, it’s much better than any other information I found on MSDN 🙂

  9. anjani Kumar says:

    I wonder that when when some one said that in future one of our web services gonna return more than one dataset.They asked me to findout a way to display in reports if it does so.I’m confused.Please guide me. Thank U russell and friends who all are helping me.

  10. anjani Kumar says:

    Hey ,Sorry…i’got the solution.its like clubing all the datasets into one and use that for reporting

    thnx guys

  11. cdk says:

    This is fantastic!  Thank you.   Just one question however.  I am having a problem getting our RS 2005 server to talk to ASP.Net 1.0 Web Service.  2.0 works like a charm.

    Here’s my Query:

    <Query>

    <SoapAction>http://tempuri.org/GetStuff</SoapAction&gt;

     <ElementPath IgnoreNamespaces="True">GetStuffResponse{}/GetStuffResult{}/diffgram{}/NewDataSet{}/PFDASSIGN{FIRST_NAME,MIDDLE_NAME,LAST_NAME,TITLE,ADDRESS1,ADDRESS2,CITY,STATE,ZIP,CONTACT1,CONTACT2,EMPLOYEE_ID,DOB,BATTALION,UNIT,SHIFT}</ElementPath>

    </Query>

  12. Big thank you,

    I’ve found the Elementpath help of big help and all the other stuff discussed in your article.

    Nice to see people like you arround!

    Regards, from Catalunya

  13. Nelazmeh says:

    Works perfectly ONLY if i remove that SoapAction tag and insert <Method> in there.  

    Great article. thanks for the details

  14. Hemant G. says:

    This article helped me a lot, I have one problem where I want to send the user name trying to run the report to the webservice automatically as the report is based on Windows Authentication credentials, is there a way to pass user name from the report and then access that user name in the web service.

  15. George says:

    Thanks very much on writing this excellent article. It has really made the process very easy!

  16. Zolpidem eszopiclone indications. Zolpidem.

  17. ANU AZEEZ says:

    The example in Comments Section helped us

  18. MK says:

    But It gives error if we use any aggrigation function becasue it does not load the schema with data, any Idea?

  19. Eoin OSullivan says:

    Thanks Ian, I looked everywhere and couldnt find the syntax for passing parameters, thanks for posting the example. The msdn example was not very helpful.

    #  re: Using the XML Data Extension to leverage an ADO.NET dataset in your SQL Reporting Services Report.

    Wednesday, May 24, 2006 8:48 PM by Ian Roof

    Alexei,

    RS should pass any regular query parameters in the SOAP request. You can also specify the parameters directly in the query. Also, parameters specified directly in the query are overridden by the values of the regular dataset query parameters.

    Here is an example of calling a web method that requires two parameters, a query string and a database name, and returns the resultant dataset.

    <Query>

     <!– Specify both the SoapAction and Method elements when the webservice namesapace ends in a ‘/’ –>

     <SoapAction>http://tempuri.org/RunQuery</SoapAction&gt;

     <Method Namespace="http://tempuri.org/&quot; Name="RunQuery">

     <Parameters>

        <Parameter Name="query" Type="String">

           <DefaultValue>Select * From Sales.Customer</DefaultValue>

        </Parameter>

        <Parameter Name="database" Type="String">

           <DefaultValue>AdventureWorks</DefaultValue>

        </Parameter>

      </Parameters>

      </Method> <ElementPath>RunQueryResponse{}/RunQueryResult{}/diffgram{}/DataSetName/TableName</xmldp:ElementPath>

    </Query>

  20. Dena Moore says:

    You’re the best, I was having challenges until I found this article!

  21. Keith Hobbs says:

    Ignore my post of yesterday. I managed to solve my problem. I found that my Xpath was incorrect, and my web service used an encrypted connection string that was failing on the server! So I’ve temporaily removed the encryption and modifed the xpatha and it works.

    This web site seemed to be the most useful in using xml data source with SSRS.

  22. Keith Hobbs says:

    I now have a problem in that the web service returns multiple records that I can see when I preview the query result in Visual Studio 2005. However, when previewing the report, or running a deployed version on the SSRS server the report only ever shows the first record.

    Anyone come across this kind of behaviour?

  23. Ben Kotvis says:

    The thing that really confused me about this is the fact that you need the raw node names instead of the object names that you see in your code or in the browser when using the wsdl.  If you use soapui or .net web service studio you can get the raw response.  Thanks for the information.

  24. CBS says:

    Excellent article. This was pretty much the only resource I found that explains clearly how to get a Dataset from a Web Service to work with SSRS. Thanks again!

  25. Natalya says:

    Hello,

    I have created a web service’s method called GetMBSScheduleMortgageData that returns a DataSet.

    I create a query:

    <Query>

    <SoapAction>http://Recontrust/iSeriesForSSRS/GetMBSScheduleMortgageData</SoapAction&gt;

    <Method Namespace="http://Recontrust/iSeriesForSSRS/&quot; Name="GetMBSScheduleMortgageData" />  

    <ElementPath IgnoreNamespaces="True">

    GetMBSScheduleMortgageDataResponse{}/GetMBSScheduleMortgageDataResult{}/diffgram{}/NewDataSet{}/Table{XMITDT,CONTRACT}

    </ElementPath>

    </Query>

    When I run this query it returns a blank row. The Web Service returns data, I have tested it. Also,I do not have any blank spaces in my element path. Please help!

  26. Murali Vaddiparthi says:

    Good information posted here. very helpful.

  27. Jules says:

    Still having trouble with the Failed to prepare web request for the specified URL error…

    <Query>

     <SoapAction>http://server/EmployeeADSearch/GetEmployeeList</SoapAction&gt;

    <Method Namespace="http://server/EmployeeADSearch/&quot; Name="GetEmployeeList"></Method>

     <ElementPath IgnoreNamespaces="True">GetEmployeeListResponse{}/GetEmployeeListResult{}/diffgram{}/Results{}/Employees {cn,employeenumber}</ElementPath>

    </Query>

    Any other suggestions?

    Don’t know what else to try

  28. Josiah Achevolo says:

    My SSRS report contains a Web service data source, and I’d like to specify as a parameter what Web service it should query.  (This is to be able to differentiate testing and production scenarios.) Is it possible to specify the namespace itself as a parameter? For example, this query works in Visual Studio 2005 in the Data pane:

    <Query>

     <Method Name="GetComplexReportData" Namespace="http://MyServ.com/MyServDataService"&gt;

     </Method>

     <ElementPath IgnoreNamespaces="true">*</ElementPath>

    </Query>

    If I add report and data source parameters called dataSourceURL, this does not work and causes a syntax error message:

    <Query>

     <Method Name="GetComplexReportData" Namespace=@dataSourceURL>

     </Method>

     <ElementPath IgnoreNamespaces="true">*</ElementPath>

    </Query>

    Thanks, J

  29. Josiah Achevolo says:

    (Sorry for posting into the wrong thread at first.)

    My SSRS report contains a Web service data source, and I’d like to specify as a parameter the URL of the Web service it should query.  (This is to be able to differentiate testing and production scenarios.) Is it possible to specify the namespace itself as a parameter? For example, this query works in Visual Studio 2005 in the Data pane:

    <Query>

     <Method Name="GetComplexReportData" Namespace="http://MyServ.com/MyServDataService"&gt;

     </Method>

     <ElementPath IgnoreNamespaces="true">*</ElementPath>

    </Query>

    If I add report and data source parameters called dataSourceURL, this does not work and causes a syntax error message:

    <Query>

     <Method Name="GetComplexReportData" Namespace=@dataSourceURL>

     </Method>

     <ElementPath IgnoreNamespaces="true">*</ElementPath>

    </Query>

    Thanks, J

  30. Sarah says:

    How would you go about selecting a particular field in ElementPath when the field name has a space in it?  I can’t figure out how to escape the space, and I get an error if I leave it in:

    <ElementPath IgnoreNamespaces="true">

    GetListItemsResponse/GetListItemsResult/listitems/data/row{@ows_Release,@ows_Theme,@ows_ID,@ows_Pre-Req Estimate}

    </ElementPath>

  31. Meenatchi Natarajan says:

    Could u please tell me,is it possible to send a Query parameter as custom object. For ex, if my web method accepts an Emp object. The Emp class has 2 properties id and name, There are 2 Report parameters in the same name. How can i pass it from my Query string?,

    Thaks,.