Importance of Using Parameters (Multiple) in Oracle BI Publisher
In this article, I try to explain How to use Parameter for reports and some tricks (Multiple Parameter Value) about the all searching values.
Yeap ! Our series keep going too fast. We started complicated issue but it’s necessary. Because I don’t want to lose this kind information and my willingness to serve you.
We will use parameter like ‘ :ParameterName ‘ in SQL code. Parameter name must start colon. It defines to use as parameter in SQL Code.
So firstly, we will write basic a SQL Code for Purchase Orders. Example SQL;
Select * From PO_HEADERS_ALL tbl_Orders
With this sql you can list all Purchase Order headers information like status, number etc. But We want to list according to Business Unit or Number or Creation Date. For searching of Business unit, we need change sql code like this;
Select tbl_Orders.*, tbl_org.Name From PO_HEADERS_ALL tbl_Orders
Inner Join HR_ORGANIZATION_UNITS tbl_org
ON tbl_org.Organization_Id = tbl_Orders.BILLTO_BU_ID
We used HR_ORGANIZATION_UNITS table to get name of business units and add inside our sql codes.
Select tbl_Orders.*, tbl_org.Name From PO_HEADERS_ALL tbl_Orders
Inner Join HR_ORGANIZATION_UNITS tbl_org
ON tbl_org.Organization_Id = tbl_Orders.BILLTO_BU_ID
Where tbl_org.Name = :BusinessUnit
We created SQL code and we are ready to create Data Model with this code.
After you click OK. New window open about creating parameters. Then, you click checkbox and click OK again. So, we created an parameter inside our Data Model.
You can change name, type, data type, parameter type etc. We wrote display name and size when we added.
When we go to Data section. Parameter is ready to search. But we know exact name of Business Unit to works for searching. I suppose that it’s too difficult to know all business units in your company. For example, you work for a group company maybe company have 20 companies. You cannot get all business unit information.
If you put prefix or suffix into the business unit name, I can prefer one more way. Using wildcard character ‘%’. For example; We have three company. USA01 XYZ Company, USA02 ERN Company, TR01 DARK Company. We want to search business unit starting ‘USA’ prefix. You have to change SQL code Like this;
Select tbl_Orders.*, tbl_org.Name From PO_HEADERS_ALL tbl_Orders
Inner Join HR_ORGANIZATION_UNITS tbl_org
ON tbl_org.Organization_Id = tbl_Orders.BILLTO_BU_ID
Where tbl_org.Name like ‘%’ || :BusinessUnit || ‘%’
This code is searching ‘USA’ inside of business unit name. If you remove first ‘%’ character. You can search starting with ‘USD’. So, if you remove second ‘%’ character. You can search finishing with ‘USD’. But this way is not effective for us in the end. Users must select Business Unit Name in a list. We need to create List of Values. (You can use the first version of the SQL code without ‘%’ character.)
Of course, you should write SQL code for this too. You can get all Business Unit from HR_ORGANIZATION_UNITS table using Name value but it’s not necessary. We want to get list of Business Units have Purchase Order. So, I need to write similar code with Orders Data Set but it has two different things inside code. One of them Distinct, the other one is we are using one value for getting just names of Business Unit. (By the way we will remove Where section in code)
Select Distinct tbl_org.Name From PO_HEADERS_ALL tbl_Orders
Inner Join HR_ORGANIZATION_UNITS tbl_org
ON tbl_org.Organization_Id = tbl_Orders.BILLTO_BU_ID
Order By tbl_org.Name
We have to change business unit Parameter settings for using BusinessUnit List of Values.
I can select one by one but users want to select more than one business unit. You can select Multiple Selection inside Parameters Options. But you need to change SQL Code inside Orders Data Set according to multiple selection. You have to use ‘IN’ clause in Where section. Like this;
Select tbl_Orders.*, tbl_org.Name From PO_HEADERS_ALL tbl_Orders
Inner Join HR_ORGANIZATION_UNITS tbl_org
ON tbl_org.Organization_Id = tbl_Orders.BILLTO_BU_ID
Where tbl_org.Name IN (:BusinessUnit)
You can check Can Select All by the way. When you click it, two option appear. Null Values Passed, All Values Passed. Null values passed means when you choose All selection, parameter takes null value. The other option means when you choose All, parameter takes all values.
‘IN’ clause can have 1000 values. If your list has more than 1000 values, you faced an error like in the below.
Actually, this example is not about business unit. Obviously, business unit list has few than 1000 values. We can think for supplier list. One business unit can have more than 1000 supplier values. So, this is a huge problem but we have trick again for this kind of searching. Firstly, Parameter ‘Can Select All’ option must be Null Value Passed then, change where clause like this;
Select tbl_Orders.*, tbl_org.Name From PO_HEADERS_ALL tbl_Orders
Inner Join HR_ORGANIZATION_UNITS tbl_org
ON tbl_org.Organization_Id = tbl_Orders.BILLTO_BU_ID
Where tbl_org.Name IN (:BusinessUnit) OR ‘All’ IN (:BusinessUnit || ‘All’))
I showed you this trick on Business Unit but You can use on supplier for exceeding 1000 values or any value
After saving, all process done by you. You created Data Model perfectly.
I will tell third option of parameter (Refresh other parameters on change) in another article.
We will meet again… Thanks a lot.
