Monday, November 12, 2012

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:
    1. <Query>
      <Method Name="test" Namespace="">

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

      <wsdl:operation name="Test">
          <soap:operation soapAction="" style="document"/>
              <soap:body use="literal"/>
              <soap:body use="literal"/>

  4. Here is what a query looks like to call a method named testWithParameters that takes one parameter
  5. <Query>
    <Method Name="testWithParameters " Namespace="">
    <Parameter Name="testParameter"><DefaultValue>1</DefaultValue></Parameter>

No comments: