Calling webservices from SqlServer Reporting Services (SSRS)

There is surprisingly very little information on the web on how to call webservices from SSRS. So here is a quick list of stuff that I found out:

Note: I am using WCF webservices for what I was doing (shouldn’t matter if you are using some other kind of service, as long as it’s a SOAP service).

Note 2: Use something like Fiddler to inspect the traffic to figure out what the request is going out as. It can be useful to figure out why a call is erroring out.

1. Create a new data-source.
1. Choose “Use a connection embedded in my report”
2. Set the “connection type” as xml
3. Enter the url to the webservice (eg: http://myserver/ssrsTest.svc)
2. Right click on the data-source and select add “DataSet”
1. Select “Use a dataset embedded in my report”
2. Open the query designer and build your query in there.
3. Here is what a query looks like to call a method named test that takes no parameters:

<Query>
<Method Name="test" Namespace="http://tempuri.org/">
</Method>
<SoapAction>http://tempuri.org/ITestService/Test</SoapAction>
</Query>

The namespaces were found by looking at the WSDL (target namespace defines the namespace)

<wsdl:operation name="Test">
    <soap:operation soapAction="http://tempuri.org/ITestService/Test" style="document"/>
    <wsdl:input>
        <soap:body use="literal"/>
    </wsdl:input>
    <wsdl:output>
        <soap:body use="literal"/>
    </wsdl:output>
</wsdl:operation>

4. Here is what a query looks like to call a method named testWithParameters that takes one parameter

<Query>
<Method Name="testWithParameters " Namespace="http://tempuri.org/">
<Parameters>
<Parameter Name="testParameter"><DefaultValue>1</DefaultValue></Parameter>
</Parameters>
</Method>
<SoapAction>http://tempuri.org/ITestService/testWithParameters</SoapAction>
</Query>