HttpCode

Recently I had to write a SQL Server Reporting Services Report that used a web service for the data source, the web service returned a horrible .NET DataSet object which I had no control over.

To use a web service from Reporting Services, the first step is to create a new Data Source of type: XML:

Then enter the URL to the web service in the connection string section.

The next step is to create a new DataSet for the report.

In the Query Designer enter the following:

<Query>
   <Method Name="<web service method name>" Namespace="<webservice namespace url>”>
   </Method>
   <ElementPath IgnoreNamespaces="True">
<web service method name>Response/<web service method name>Result/diffgram/NewDataSet/Table1   (replace Table1 with the dataset name if used)

    </ElementPath>

</Query>

If you need to pass parameters to the web service, this can be done by adding something like:

<Parameters>
       <Parameter Name="<parameter name>">
           <DefaultValue></DefaultValue>
       </Parameter>
   </Parameters>

But be sure that the case of the parameter name is exactly the same as the parameter in the report that you wish to pass in.