How to Call a BIP Report from OIC

Reading Time: 6 minutes

Problem

Many times we need to fetch SaaS in OIC for processing, one of the way is to call the BIP report and fetch the data into OIC.

Solution

To call the BIP report we need to use External Report WSS Service SOAP service. By using the run report operation we can call the report and report response will be base64 format, in OIC we need to write the response to stage directory using opaque schema and then read the stage file using the report output schema.

Approach

Let’s take a example of calling SaaS BIP report and calling local integration.

Prerequisites:

1. Oracle Fusion Instance with BIP report
2. Oracle Cloud Instance
3. SOAP connection to call BIP report (WSDL: https://servername/xmlpserver/services/ExternalReportWSSService?WSDL)
4. Local integration which accepts the payload
5. Opaque file (https://docs.oracle.com/en/cloud/paas/integration-cloud/integrations-user/use-stage-file-write-operation-opaque-schema.html)

Let’s get started.

Step 1: First let’s validate the report in SaaS. Login to the SaaS instance and navigate to Tools, then click on Reports and Analytics.

Step 2: Click on Browse Catalog.

Step 3: Navigate to the report path and click on Edit Report.

Step 4:  Click on View a List.

Step 5: Let’s see the report output format (Ex: In our case it is csv) and run the report to save which will be useful in the next steps. Note: Report output should be OIC readable format like CSV,XML,JSON etc.

Step 6: Click on View Report and give the parameter value to run the report.

Step 7: Save the report output.

Step 8: Click on the setting button and click on export to save the output file.

Step 9: Also copy the report path which is required to pass in the web service payload.

Step 10: Now let’s navigate to OIC and create a connection with the help of SOAP adapter.

Step 11: Click on Create and then search for SOAP adapter and then click on select.

Step 12: Give any name and select the role as invoke and then create.

Step 13: In the WSDL url give your SAAS url like for example we’ve used “example.com” here and then “xmlpserver/services/ExternalReportWSSService?wsdl”.

Step 14: In the security section select the username password token and then give your SAAS username and password.

Step 15: Click on Test and then click on save.

Step 16: Create a scheduled orchestration based integration.

Step 17:  Click on Create and the select the Scheduled Orchestration style and then click on select.

Step 18: Give the integration name and click on create.

Step 19: Click on edit Scheduled Parameters and create one parameter that will store the value of the input parameter of the BI report.

Step 20: Select the SOAP connection created to call the BIP report.

Step 21: Give the endpoint name and select the operation as “runReport” then click on next and then click on done.

Step 22: Go to the mapper and click on edit.

Step 23: Give the attribute format as the output format of report.

Step 24: Give the report absolute path as the report path from BIP.

Step 25: Give the size of data chunk download as -1, so that entire data from BIP will be fetched, If any other value provided then only partial data/No data will be fetched.

Step 26: In the parameter name values, assign the name of the parameter defined in the BI report.

Step 27: Map the value of parameter1 with the value item of the parameter name value as parameter1. Then, hold the value of the input parameter defined in the BI report.

Step 28: Click on validate and then close.

Step 29: Request payload file is created.

Attachment: ReqPayload

Step 30: Now we need to write the base64 response of BIP report to opaque schema file of stage write.

Step 31: Select the write file operation and give the file name and output directory.

Step 32: Select the sample schema format and choose the opaque schema file and click on done.

Step 32: In the mapper of the stage write operation map the reportBytes to the opaqueElement.

Step 33: Sample schema file of opaqueElement is attached below.

Step 34: In the mapper of the stage write operation map the reportBytes to the opaqueElement.

Step 35: OpaqueElement will convert the base64 encoded data to reference.

Step 36:Now let’s read the stage file using the report output schema.

Step 37: Choose the stage file operation and give the filename and the directory to read file from.

Step 38: Click on next and select the sample schema file for read file output.

Step 39: Give the record name and record set name.

Step 40:  Click on next and then click on done.

Step 41: Now we can process the report data in OIC as required from the read file response.

Step 42: Activate the integration and then submit.

Step 43: Then we need to pass the input parameter that we’ve defined in the integration.

Step 44: Give the parameter value and click on submit.

Step 45: Go to the tracking screen and monitor the integration.

Step 46: Integration will be successful with the below attached response.

Attachment: ResponsePayload

Advantages

Using this approach we can call the BIP report and process the data in OIC as required.