In some cases, we want to use multiple values page item to filtering report records. In this post I will try to explain how can we do this in a simple way. You can filter Classic Report/Interactive Report/Interactive Grid. In this post I will use IR with "EMP" dataset and we will filter records based on EMPNO column using multiple values page item. I'm using APEX 24.1.3 release. To do this follow the steps below :
Create Page Item "P4_EMPNO" :
Type = Popup LOV
Multiple Values => Type = Delimited List
Separator = :
List of Values => Type = SQL Query
SQL Query :
select
ename,
empno
from
emp
The Popup LOV above will return EMPNO and display ENAME column. So, we actually we will filtering records using EMPNO column.
If we choose CLARK and JONES, the Multi Values item will store values like "7782:7566" (because we return EMPNO in the Popup LOV) we can use $v() JavaScript function to get page item value e.g. $v("P4_EMPNO")
Create IR (or CR/IG) :
Table Name = EMP (our your own table)
Page Items to Submit = P4_EMPNO
Where Clause :
(:P4_EMPNO is null or instr(':' || :P4_EMPNO || ':', ':' || empno || ':') > 0)I used above instr() function. This function determine if the page item match EMPNO column value. If matched, the value position will return, unless it will return zero.
Create Button "Search" :
Create DA on Click on Search button and add action Refresh:
Set Region = IR region
The action above will refresh our IR.
Create Reset Button "Reset".
Create DA on Click on Reset button and add two actions:
- Set Value : This action will set P4_EMPNO value to
null
- Refresh : The same refresh action for Search button :
Now, we will choose two values or more and click Search :
And on click on Reset Button it will reset the IR report :
And That's it!
Live demo :