Scroll to top
Please assign a menu to the primary menu location

How to Call a BIP Report from OIC


Roshan - June 28, 2022 - 2 comments - 271 Views

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.

step1Step 2: Click on Browse Catalog.

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

Step 4:  Click on View a List.step4

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.s5

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

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.s9

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.step11

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

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

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

step14Step 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.

step17

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

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

step19Step 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.

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

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

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

s18Step 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.

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

s20Step 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.

s21Step 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.

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

s25Step 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.

step33

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.

s27Step 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.

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

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

s31Step 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.

s35Step 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.

s38

Attachment: ResponsePayload

Advantages

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

Author avatar

Roshan

Roshan comes with an extensive experience in the Oracle Implementation domain after completing his Electronics and Communication Engineering from Bengaluru. He holds a plethora of professional certifications from Oracle after starting as an Associate Developer role and quickly made his way to a Senior Manager. He has worked with clients across the globe in ANZ, EMEA, APAC and Europe. At Conneqtion Group, Roshan has been a key part of managing the organization’s largest risk teams apart from sharing his experience and skills to develop and nurture young talent with his dynamic vision.   

Related posts

2 comments

  1. basavaraj

    After decoding the report bytes we can call directly read file no need of write stage file activity.
    and what if bip reort exceeds 10 mb?

    • Karan Tulsani

      We will utilise same approach now till 50 MB as Oracle has recently increased the limit. If data is more than 50MB then it is suggested to use BIP Bursting in UCM or SFTP and get the data.

Post a Comment

Your email address will not be published. Required fields are marked *