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="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.