' oicbasics: Schedule BI Publisher Report through OIC | Oracle Integration Cloud
April 24, 2022

Schedule BI Publisher Report through OIC | Oracle Integration Cloud




In this article I have mentioned the steps to develop an Integration that can invoke "Schedule Report" operation and can check the status of submitted schedule report operation i.e. Job Status.

When to use Schedule Report & when to use run report operation ❓🤔   

  • In laymen term , use 'Run Report' operation if report output size  is <10 MB and use 'Schedule Report' if report content size is >10 MB. Run Report operation can provide the report output in the webservice response but Schedule Report will generate the report output in FTP , UCM etc. ( know as report bursting )



Lets develop the  integration in two parts :

In Part 1 : we will see the development steps to invoke 'Schedule Report' operation and in Part 2 : we will see the development steps to check the Status of 'Schedule Report' operation.



Prerequisite : 'Schedule Service SOAP WSDL' Connection. Please access this link and create the connection. We will use the same connection while developing the integration below.

- Advertisement -




PART - 1 : Invoke Schedule Report Operation


Step 1 : Login to OIC Instance, click on Navigation Menu (top left) then click Integrations and then again click Integrations. Click on Create (top right) and select Scheduled Orchestration. Enter any meaningful name to the Integration.


Step 2 : Search for the Schedule Service Connection which we have created above in prerequisite section ( if you have not created it yet, you can access this link to create this connection ) and select it. Adapter endpoint configuration wizard will get opened :


  • Enter any meaningful name (ex - ScheduleBIReport ) for the endpoint and click Next

- Advertisement -



  • Select ScheduleReport operation from drop down list and then click Next

  • Header configuration not require for this POC , so leave it as it is , just click Next

Knowledge :  😇
Headers are optional elements that pass extra information about your application requirements. For Example, the header element can be used to specify digital signature for password protected services.


  • Adapter configuration completed. You can see the connection summary. Now click Done to close the configuration window.


- Advertisement -



Step 3 : Now open the mapper. Here we will pass the parameters name and its values that will be require to Schedule the Report.


  • Below Columns Mappings are require to invoke Schedule BI Report operation


  • Once the mapping completed, Click Validate and Close the mapping window.


- Advertisement -




PART - 2 : Schedule Report Job Status Check 


Step 4 :  Add Assign action to declare some variables.


  • Click '+' to declare Variable to store count (i.e. while loop count ). Enter any meaningful name to the variable then click Edit 🖉 and hardcode value '1' for the variable

  • Similar declare one more variable and initialize its value to '' (i.e. empty string) . You can take reference from below image. In this variable we will save 'GetSchedeuledReportStatus' operation response.



- Advertisement -




Step 5 : Add While Action. Enter any meaningful name.



  • Click 'Expression Mode' and write the below expression. The while loop will keep running till the time below expression is true. ( Take reference from below image )

not(contains($GetScheduleReportStatus,"Success"))  and not( contains($GetScheduleReportStatus, "Error"))  and not( contains($GetScheduleReportStatus, "Failed")) and  xsd:integer($CounterVariable) <  xsd:integer('30')




- Advertisement -




Step 6 : In this step we will invoke 'getScheduledReportStatus' operation to get the status of the 'Schedule Report' operation (which we configured in PART 1, above). 
  • Again search for the Schedule Report Service connection which we have created above in prerequisite section ( if you have not created it yet, you can access this link to create this connection ) and Select it.


  • Adapter configuration window will get opened, enter any meaningful name and click Next
  • Select 'getScheduledReportStatus' and click Next
  • Header configuration is not require for this POC, just click Next.
  • Adapter configuration completed. Click Done.



- Advertisement -



Step 7 : Open the mapper and map the 'ScheduleReportResponse' to 'ScheduledJobId' along with SaaS application credentials. ( take reference from below image )



Step 8 : Now add one more assign activity in which we will Increase the counter variable value and assign the 'GetScheduledReportStatus' operation response to the variable.


  • Select the counter variable from drop down list, click Edit🖉 and increase the variable value by '1'.  Note : Make sure to use Integer functionTake reference from below image .
  • Similarly select the another variable, click Edit🖉  and the map the *jobStatus element of 'GetScheduledReportStatus' Response.

  • Click Validate and then click Close



- Advertisement -



Step 9 : Add Wait activity for 30 seconds inside While Loop. So that status check call happen after certain interval of time only otherwise While loop keep invoking status check  operation continuously, which is not a correct practice.



Step 10 :  Enable Tracking for the Integration.



Step 11 : Save and close the Integration window. Activate the integration. Your have successfully develop an Integration to Schedule BI Report and can check its status.



Thank you ! 😊 , TADA !! 👋. 

12 comments:

  1. Thanks Kabir for such a wonderful series of articles!! One question on this one: You said if report response > 10 MB, we should use schedule report operation and the output comes in FTP/UCM. So, want to know how we can map the FTP/UCM server in this integration where we want to get the output from BI.

    ReplyDelete
    Replies
    1. You need to configure 'REPORT BUSRTING' option ( which will contain contain FTP details ) while creating DATA MODEL for the REPORT in BI Publisher.
      Any one who have worked in BI REPORT can help you REGARDING 'BURSTING IN REPORT'.

      Delete
  2. Thanks kabir! I have one question while scheduling the BI report I got error as Fault Code : soapenv:MustUnderstand
    Fault String : MustUnderstand headers:
    ]]>
    :Application Error Error Summary:
    Could you please help on this

    ReplyDelete
    Replies
    1. You may have done some mistake while configuring connection of 'Schedule Service SOAP WSDL Connection' ( present in prerequisite section of above article ) In security section of connection select NO SECURITYT POLICY because we are passing credentials in MAPPING while invoking schedule service operation

      Delete
  3. Thank you Kabir , Issue got resolved

    ReplyDelete
  4. Hi Kabir, Once the file is available in UCM. I want to read that file and do some transformation. How to identify our file in UCM.

    ReplyDelete
    Replies
    1. dear this question is not related with the Above Article, to get your personal query answered, you can connect me at 'thekabiryadav@outlook.com'

      Delete
  5. Hi Kabir, thank you for your POST. I am trying to get the schedule status and I dont get any value in status. I have all data of schedule in the "father" node but if i try to get the node status for the "child" node I dont get any value.

    ReplyDelete
  6. What is workaround if don't want to hardocde userid and password.

    ReplyDelete
    Replies
    1. you can store it in LOOKUP or any database , and if you don't want to store it anywhere (just for security reason) then I think you need to find some different API which don't need credentials in mapping

      Delete
  7. Hi Kabir, thanks for article, how can I retrive document form UCM?

    ReplyDelete

If you have any doubts, Please let me know.

Top