Refreshing Parameters in Oracle OTBI Reports

Hi Dear All,

I’m here after a long time, I couldn’t write because I was so busy actually I am still :) but I need to continue writing articles like these with determination for my development and you. So here we go.

Today’s topic is refreshing parameters in OTBI reports. I know, you can handle it with the main SQL query using parameters but It will be great to use refreshing parameters for you and especially all users. It prevents to confuse about choosing parameters. It provides the convenience of selecting all interconnected parameters. I will try to explain with examples.

Let’s think about our structure. We are using Business Unit as a Company and we have 10 or 15 company in many countries. We want to get a purchase order list for a vendor. We will use two filters for this report that one of them is Business Unit (Company) the other one is Vendor. The vendor filter will be filtered according to the company. Let’s check!

Select
poh.Segment1 as PONumber, org.Name as Company, sup.VENDOR_NAME Vendor From PO_HEADERS_ALL poh

— For Business Unit Name
Inner Join HR_ORGANIZATION_UNITS_F_TL org
On poh.BILLTO_BU_ID = org.ORGANIZATION_ID and org.Language = ‘US’

— For Vendor Name
Inner Join POZ_SUPPLIERS_V sup
On sup.VENDOR_ID = poh.VENDOR_ID

Where org.Name IN (:Company)
and (sup.VENDOR_NAME IN (:Vendor) OR ‘All’ IN (:Vendor || ‘All’))

Creating Data Set

We created two parameters with this main SQL Code. So we need to prepare a list of values before making setting parameters.

We can add the first list of values for the Company. You can reach company names in the filter using the codes below. You can get a company list that has a purchase order document not listed all company values. Maybe you have a company but haven’t got a purchase order. It’s not necessary listed in our list.

Select Distinct org.Name as Company From PO_HEADERS_ALL poh
— For Business Unit Name
Inner Join HR_ORGANIZATION_UNITS_F_TL org On poh.BILLTO_BU_ID = org.ORGANIZATION_ID and org.Language = ‘US’
Order By org.Name

Basically, we have two rules for refreshing parameters. The first rule is You must use the name of the other parameter in the query of the parameter you want to refresh. It’s (:Company) for our SQL query.

Select Distinct sup.VENDOR_NAME From PO_HEADERS_ALL poh

— For Business Unit Name
Inner Join HR_ORGANIZATION_UNITS_F_TL org
On poh.BILLTO_BU_ID = org.ORGANIZATION_ID and org.Language = ‘US’

— For Vendor Name
Inner Join POZ_SUPPLIERS_V sup On sup.VENDOR_ID = poh.VENDOR_ID

Where org.Name IN (:Company)
Order By sup.VENDOR_NAME

We list suppliers that created a purchase order for the company with this code.

List of Values

After we prepared our values, we can arrange the settings of parameters like screenshot in the below. The second important rule is You must check the ‘Refresh other parameters on change’ option for the Company parameters. Because If there is a changing event on Company, the system creates a new list of vendors according to company parameter.

Checking parameter options

That’s it! I said it’s a simple topic for you but it’s useful and practical. You can create a report after all. When you see the change company in the report. You will face a window about refreshing parameters. It means you did well.

Loading parameters

We will meet again… Thanks a lot.

Software Developer