Bagikan melalui

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

  • Artikel

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="https://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:soap12="https://www.w3.org/2003/05/soap-envelope">
<soap12:Body>
<GiveMeDataResponse xmlns="https://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:

 https://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>https://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>https://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

  • Anonymous
    April 26, 2006
    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!).

  • Anonymous
    May 24, 2006
    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>
      <Method Namespace="http://tempuri.org/" 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>

  • Anonymous
    July 25, 2006
    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?

  • Anonymous
    September 22, 2006
    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

  • Anonymous
    September 25, 2006
    XML data sources are not supported in SQL Express:

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

    (See "Unsupported Features" section.

  • Anonymous
    October 10, 2006
    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/"       Name="GiveMeData">   </Method></Query>

  • Anonymous
    November 29, 2006
    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).

  • Anonymous
    September 12, 2007
    Hey thanks for this great step by step tutorial, it's much better than any other information I found on MSDN :)

  • Anonymous
    October 17, 2007
    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.

  • Anonymous
    October 17, 2007
    Hey ,Sorry...i'got the solution.its like clubing all the datasets into one and use that for reportingthnx guys

  • Anonymous
    October 18, 2007
    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> <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>

  • Anonymous
    November 21, 2007
    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

  • Anonymous
    December 05, 2007
    Works perfectly ONLY if i remove that SoapAction tag and insert <Method> in there.  Great article. thanks for the details

  • Anonymous
    December 17, 2007
    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.

  • Anonymous
    May 14, 2008
    Thanks very much on writing this excellent article. It has really made the process very easy!

  • Anonymous
    July 18, 2008
    Zolpidem eszopiclone indications. Zolpidem.

  • Anonymous
    July 22, 2008
    The example in Comments Section helped us

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

  • Anonymous
    July 28, 2008
    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 RoofAlexei,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> <Method Namespace="http://tempuri.org/" 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>

  • Anonymous
    September 02, 2008
    You're the best, I was having challenges until I found this article!

  • Anonymous
    October 30, 2008
    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.

  • Anonymous
    November 06, 2008
    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?

  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/2193664-xml-data-source-method-not

  • Anonymous
    April 09, 2009
    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.

  • Anonymous
    April 22, 2009
    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!

  • Anonymous
    May 01, 2009
    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> <Method Namespace="http://Recontrust/iSeriesForSSRS/" 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!

  • Anonymous
    May 26, 2009
    PingBack from http://backyardshed.info/story.php?title=russell-christopher-s-semi-useful-bi-musings-using-the-xml-data

  • Anonymous
    June 09, 2009
    PingBack from http://insomniacuresite.info/story.php?id=11422

  • Anonymous
    October 08, 2009
    Good information posted here. very helpful.

  • Anonymous
    October 19, 2009
    Still having trouble with the Failed to prepare web request for the specified URL error... <Query>  <SoapAction>http://server/EmployeeADSearch/GetEmployeeList</SoapAction> <Method Namespace="http://server/EmployeeADSearch/" 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

  • Anonymous
    December 10, 2009
    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">  </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

  • Anonymous
    December 10, 2009
    (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">  </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

  • Anonymous
    February 05, 2010
    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>

  • Anonymous
    November 10, 2011
    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,.