Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
In our current proyect, we have a Report Server where we host all the reports that feed from our Data Warehouse (SQL Server). But recently we were asked to include a report that feeds from a web service (to show live information from the main system rather than backed up information from the DWH). The webservice is hosted in the main server (AIX) and we have no control over it. We were given a WSDL from wich I was able to parse an XML query (using SoapUI). To be able to query the webservice I need to pass in parameters in the soap body (as is expected) but I also need to pass in values for authentication in the soap header.

This is the soap request I need to translate:
XML
<soapenv:Envelope xmlns:soapenv="Namespace" xmlns:aut="Namespace" xmlns:con="Namespace">
   <soapenv:Header><!-- This is what I don't know how to send. -->
      <aut:AutenticacionRequestHeader>
         <UserName>?</UserName>
         <Password>?</Password>
      </aut:AutenticacionRequestHeader>
   </soapenv:Header>
   <soapenv:Body>
      <con:consultaMovimientosCuenta>
         <ACCOUNT_NUMBER>?</ACCOUNT_NUMBER>
         <BEGIN_DATE>?</BEGIN_DATE>
         <END_DATE>?</END_DATE>
         <ENTRY_RANGE>?</ENTRY_RANGE>
      </con:consultaMovimientosCuenta>
   </soapenv:Body>
</soapenv:Envelope>

I understand that I can add an XML DataSource to my report in SSRS, and then a DataSet with a query written in XML Data Provider Query Language. This seems simple enough and a pretty clean solution but I've searched for a whole day now and I just can't find a single example on how to provide values for the soap header in an XMLDP query. What tag do I need to use?
XML
<Query>
   <!-- Where do I provide the soap Header? ("UserName" and "Password") -->
   <Method Namespace="namespace" Name="consultaMovimientosCuenta">
   <Parameters>
      <Parameter Name="ACCOUNT_NUMBER">
      <DefaultValue>@ParamAccountNumber</DefaultValue>
      </Parameter>
      <Parameter Name="ENTRY_RANGE">
      <DefaultValue>@ParamEntryRange</DefaultValue>
      </Parameter>
   </Parameters>
   </Method>
   <SoapAction>namespace/consultaMovimientosCuenta</SoapAction>
   <ElementPath IgnoreNamespaces="True">
      consultaMovimientosCuentaResponse{ACCOUNT_NUMBER}
   </ElementPath>
</Query>

I think if I could manage to do this it would be the best solution for my problem, but since we're a bit short on time, I tried an alternative: create a webservice (.net) that consumes the other web service in the main system (and pass on the neccessary parameters and authentication values), and then have my report consume the new webservice. The problem is, I need to be able to host this service on the SSRS Report Server.

My questions are:

  1. Is my first approach (XMLDP query) the best way to go? (I surely think so) If so, how? Or is it impossible to pass on soap header parameters through an XMLDP query?
    I found some examples that assumed I had created the webservice, or that I consumed it in a console/windows application. I should state that I'm not using any code here (some examples managed to do it in C#) but just pure XMLDP queries in a report (.rdl) editted in Report Builder 3.0
  2. If I decide to go with the second approach, (which is working) is it possible (and I'm not sure I really know what I'm talking abount here) to use the Report Server Web Service to host my newly created webservice? Is there a different way to host the service in the Report Server? My boss doesn't want to raise an IIS server just for this single web service, and has asked me to research on any possibilities of using the RS that's allready installed instead.
Posted
Comments
PilotJosh 13-Nov-14 13:33pm    
Did you ever figure out how to do this?
Vic91 7-Jan-15 10:00am    
This was quite some time ago but I think I ended up creating a WCF service to act as a proxy. This service would consume the first service that required authentication parameters. Then the report would consume the proxy. I never found out how it could be done using native XMLDP

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900