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.

I received an interesting error today while trying to ‘Manage Data Sources’ on a report “The resource object with classname ‘ReportServer’ and the key ‘DataSourceList’ was not found”:

I found that sometimes it would work, but other times it would fail. A bit of tracing showed that when I hit web1 in the farm it would fail, but web2 would succeed.

With this information in hand I hand a look at the App_GlobalResources folder under c:\inetpub\wwwroot\wss\virtualdirectories\<web application name>

Interestingly web2 (which worked) had some resources called ReportServer.resx, but web1 (which failed) did not have these resources, that was what was causing the failures.

I copied the resources across and the issue resolved itself.

I suspect that the report services add on wasn’t installed properly.