January 15, 2021

Invoke BI Report in OIC



In this article we will see the steps to invoke a BI Report in an Integration and then the steps to read its base64 encoded output.


we will do the development by breaking it into 2 parts :

PART 1 : Steps to invoke a BI Report
PART 2 : Steps to read the base64 encoded report output. (access link)




Lets start the development : ~

PART 1 :  Steps to invoke a BI Report :

Lets develop one schedule integration where we will configure a soap adapter connection to call the BI Report using runReport Operation and then do the mappings. First we will create the connection and then create the integration.




Create Connection : 
Here we will configure  " ExternalReprtWSSService WSDL URL " which can be used to run a report. The same connection we will use in below steps while developing integration.
    • Navigate to connection window and select SOAP adapter, give any meaningful name for connection.



    • Enter the ExternalReportWSSService WSDL URL  and Select the TLS Version. TLS Version depends on your ERP Cloud Application Instance. Mostly it is TLSv1.1
WSDL URL Format : https://<your oracle cloud application instance domain>/xmlpserver/services/ExternalReportWSSService?WSDL


    • Enter the *Username and *Password of the Oracle Cloud Application Instance. Click SAVE and TEST the Connection.

 


Create Integration :
Here we will create the integration using the above configured SOAP connection, do the mappings of required field and then do one round of testing.
    • Create one Scheduled Integration by giving some meaningful name.

    • Search for the connection which we have created above and select it.

    • Enter some meaning full name(ex: invoke BI Report) and then click NEXT.


    • Select the operation = runReport and then click NEXT

    • In headers configuration window, select Accept attachments in response. Click Next
    • SOAP Endpoint configuration completed. Click Done.


STEP 2 :
Now let's do the MAPPINGS. We will pass all the required fields to invoke BI report ( like Report Path, Report Parameters Name and its Value etc.)



    • Expand the "parameterNameValues" element

    • Expand "Item" and then enter your BI Report Parameter name in name* element (as shown in below image)
    • Now expand values* and map or pass Parameter Values in Item element (as shown in below image)
    • Parameter names and its values configuration completed

*Note : suppose you have multiple parameters for your BI Report then you have to repeat the Item tag and then pass the Parameter name and its Values similar as in above Step 2.
(check below image for reference)



    • enter your report path in reportAbsolutePath* element

    • enter "-1" sizeOfDataChunkDownload*



Step 3 : Inside this step we will enable the tracking and do one round of testing.

    • Enable the Tracking



    • BI Report call configuration completed. Click save and Close the integration edit window.

    • Activate the integration 

    • Submit the integration by clicking Submit now and then click on Instance Id to track the flow 

    • Once processing succeeded , open the flow by clicking start time
    • Click on active stream and check the payload received as output from report



We have successfully develop and tested the BI Call in Integration πŸ˜€. Now in next blog we will further see the steps to read this base64 encoded output data i.e. PART 2 of development work. Click to see next blog.

23 comments:

  1. very use fill and understandable can do any one by following this blog

    ReplyDelete
  2. Hi Kabir ,
    Thanks for the Article on BI Report integration with OIC .
    1. We have a use-case that there can be multiple BI report at a given point of time . How can we read the mutiple reports in OIC
    2.Can you tell me scenario where we are passing some parameters in runReport() operation

    ReplyDelete
    Replies
    1. Dear
      1. you have to call it separately because for each BIP you may have unique report path, its unique parameters.

      2. In the above blog only I have passed parameter to call runReport() operation.Please cross check :)

      Delete
  3. Hi Kabir,
    Need small info..what happens if BI report sends empty data..how to handle this exception in OIC..

    ReplyDelete
    Replies
    1. Note : below is just a suggestion for your requirement not the solution :)


      Using 'If' condition and 'stringlength' function you can achieve this :
      If stringlength (-- report response i.e. *reportBytes --- )> 0
      then only proceed to execute downstream flow
      otherwise stop or send error notification using notification action.

      Delete
  4. Hi Kabir,
    For multiple values my report is not working. Even if we pass repeating elements in below format-
    a
    b
    c
    Report is giving me output only for "a". Whatever first value in items tag, its considering that only.
    However, i tried a,b,c this is giving me correct result but in this case how we can restrict count till 1k since IN clause is having limitation.

    ReplyDelete
  5. Hi,

    While invoking bi report we are getting time out.
    is there any configuration to increase timeout value

    ReplyDelete
  6. I have updated a UDT template (first three rows). How do I verify the update will come up when running the interface? Do I need to trigger anything? How do limit he testing to those three rows?

    ReplyDelete
  7. Hi Kabir,
    Can't we invoke a BI Report which is located at My Folders instead of Custom Folder?

    ReplyDelete
    Replies
    1. i guess no, u can't invoke BI Report which is located at 'My Folder'

      Delete
  8. Hi Kabir ,

    How can i fetch the incremental data for suppose from GL bi report, if integration is set to run once in a day?

    ReplyDelete
  9. Can you tell how to accept attachment in bi response

    ReplyDelete
  10. Hi Kabir,
    I am getting below error while executing Integration.. Can you please advice what to do. Even I have checked all permissions in Fusion Cloud.
    Error Below
    Fault Reason :
    PublicReportService::generateReport failed: user: krishnam does not have permission to run the report: /shared/XXINVVENRPT.xdo
    Thanks

    ReplyDelete
    Replies
    1. please cross check that you are giving correct report path or not ?

      Delete
    2. u can check the sample path which i have given in above article (STEP 2)

      Delete
  11. Hello Kabir,

    can you please mention the roles required for a user to invoke BIP reports ??

    ReplyDelete
  12. Hi Kabir,

    AM looking for Part-2..wher can i find.?

    ReplyDelete
    Replies
    1. Link is present in the last line of the above blog, i think without reading the blog u raise the question πŸ˜‹.... any way its okay ...

      Delete
  13. Hello Kabir,

    We have this requirement where the BI report size is exceeding its maximum size. Now, we want to handle such data using OIC. What will be the process to achieve such requirement.

    Thanks in advance

    ReplyDelete
  14. I think you would need break the process into 2 parts. One part to execute the 'submitReport' operation (not the 'runReport' as Kabir brilliantly took us through above)...then wait for the report to complete.

    The 'submitReport' executes the BI Report, but the output can be placed in a UCM account (folder). In a second part, or better still a separate integration, run the 'GET_FILE' operation on an OIC Connection based on the '../idcws/GenericSoapPort?WSDL'.

    e.g.






    Surabhis_Very_Large_File_Name.csv






    The 'GET_FILE' operation allows you to provide things like the 'dOriginalName' (File Name) to obtain the file in Base64.

    If the 'File Name' is dynamically named by your BI Report, and therefore you do not know exactly what the file name is, then you will have to run the 'GET_SEARCH_RESULTS' operation (again using the 'GenericSoapPort' connection) with a query to find your specific file in UCM before you use the 'GET_FILE' operation.

    Here's an example call that you'd have to map in OIC too.






    dDocAccount = `hcm$/dataloader$/export$` <AND> dDocTitle <starts> `Surabhi` <AND> dOriginalName <SubString> `Very_Large`
    dInDate
    ASC






    Once you have eventually got your 'GET_FILE' call working, use a FTP adapter to write the file. You'll need to use the decodeBase64ToReference function to set the FileReference.

    e.g.
    oraext:decodeBase64ToReference($getPaymentFile/ns0:GenericResponse/ns0:Service/ns0:Document/ns0:File/ns0:Contents)

    Hope this helps
    Ian (NoteToSelf.dev)

    ReplyDelete
    Replies
    1. Comments didn't like my XML, sorry :(

      Delete
    2. Hi Ian or Kabir,

      I have a scenario where the BI Report content is too large and it takes more than 240 secs to return a response for a RunReport call. Hence, it gets timed out.

      I found the SubmitReport operation as part of the ExternalReportWSS call, but when i submit the request - i get the response as 'Unknown method'. Is this operation supported ? If not how to handle this Time out scenario ?

      Thanks,
      Mukesh

      Delete

If you have any doubts, Please let me know.

close