Hi folks,

I'm trying to understand my options for integrating Oracle and REST XML Web Services data in Cognos 11.1.7

Can anyone add or clarify the options I came up with below?

Thanks

Mike

Goal (fictitious)

---------------------

Create a list report from Framework Manager modeled data published CQM pointing to Oracle data that includes city and state

• Bring in the weather and temperature for the city/state from a REST XML Web Service

Possibilities

----------------

1. Make a data source out of a a web service permitting retrieval of all the cities and states in one fell-swoop, either by default or by a customized Cognos service configuration file
   1. Integrate the data in a data set or module, joining city to city
   2. Question: what if it was required to make thousands of web service calls, one by one for each of the cities in the country?
2. Complete the integration through the Oracle database
   1. G. Create a SQL Object in Cognos pointing to Oracle having SQL that calls a PL/SQL function that accepts city and state and makes packaged procedural calls to things like UTL_HTTP or SOAP_API OR APEX_WEB_SERVICE to return the weather or temperature
3. Question: is it possible in non-interactive reports to include Javascript as an HTML object in the List so that the HTML object receives the city and state from other columns in the list row and returns the weather from the API?
   1. So far, I've found no way to have that Javascript accept a value (a city/state) from another cell in the list row and use that to make the REST call. Moreover, even if the REST call is identical so that a hardcoded city and state is used, what seems to happen is that all of the rest calls are made and the results returned not in each list row column but altogether in one big column spanning the entire list
4. Question: for interactive reports, is it possible to include a custom control affected by JavaScript stored on the server so that a particular list column cell winds up displaying the weather returned from the web service using the city and state in other cells in the list row to make the call
   1. It seems that the delivered JavaScript samples do not include code illustrating this but rather are limited to things like allowing the end user interactively to display everything or nothing in a given list column
5. Other thoughts?

It would be cool if there were a relatively simple, straight forward way just to pass values from a list column row's other cells to a web service and to have it use those values to return a value that is then included in the list column