CR/IR/IG: How to filter records depending on multiple values page item

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:

1. Set Value : This action will set P4_EMPNO value to null

2. 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 :

https://apex.oracle.com/pls/apex/r/alabbasi/hamza-s-apex-blog-examples/filtering-records-using-multi-values-item