In this article, I’ll demonstrate adding dependent List of Values (LOV) Parameters to the BIP report and the steps involved in it. Sometimes end users request for dropdown values of parameters to be restricted based on the value selected in other parameters in the report.

Prerequisites:

  1. Oracle Fusion SaaS Instance with necessary roles to create reports.
  2. Basic knowledge of SQL and creating BIP reports with LOVs (Refer to my previous blog to learn, how to Add a List of Values to Oracle BIP Report).

In our previous blog, we have created a data model and report with 2 LOV parameters. Now let’s make the invoice number parameter dependent on the currency parameter so that based on the currency code selected in the currency parameter, the invoice numbers in the drop-down of the invoice parameter will be filtered based. Once this is implemented in the data model these changes will be reflected automatically in the report linked to the data model.

Steps to add dependent LOV parameter

  1. Navigate to the data model created in our previous blog. Select a list of value which has to be updated based on another parameter, in our use case it is the invoice number that has to be filtered based on the currency. As shown below in the screenshot we need to add the where condition in the SQL query to filter based on the currency parameter.
  1. Navigate to the parameter section. Select the invoice currency parameter and Enable “Refresh on other parameter change”. Enabling this option based on the value selected in invoice currency parameters other parameters will be reflected. Click on Save, as shown in the following screenshots.
  1. Now we have successfully added the dependent LOV parameter to the Data Model.

Testing Data Model

  1. Currently, in our fusion instance, there is one invoice number “TEST” for USD currency and “test90” for EUR currency
  1. Click on Data and then select the values for the parameters from LOVs as shown in the following screenshot: when a user selects the USD currency code Invoice number parameter fetches invoice numbers belonging to the USD currency code.
  1. Now we have successfully added and tested the dependent LOV parameter.
  1. Same changes will be automatically reflected and can be tested similarly at the report level.  

You can look at our YouTube channel for Oracle Integration-related videos. Also, don’t forget to subscribe to our channel.

Further Readings

How to create a BIP report in Oracle SaaS

How to create and test custom ESS job in Oracle SaaS

How to extract data from Oracle Fusion using REST APIs in OIC

ERP Integration using File Based Data Import: Oracle Integration

Import Suppliers using FBDI in Oracle Integration

How to call Oracle SaaS ESS job using Oracle Integration