TLDR: play with it here
The long desired APEX_IG package for programically manipulating the Interactive Grid is finally here.
While not feature packed, it brings some vital functionality users have been asking for for a long time, mainly the adding and removing of filters.
The most interesting procedure is
procedure add_filter ( p_page_id in number , p_region_id in number , p_filter_value in varchar2 , p_column_name in varchar2 default null , p_operator_abbr in varchar2 default null , p_is_case_sensitive boolean default false , p_report_id in number default null );
Let’s see how to use it
p_page_id is easy to get.
p_region_id we might have to do something like this:
select region_id from apex_application_page_regions where application_id = :APP_ID and page_id = :APP_PAGE_ID and static_id = 'emp';
p_filter_value is the actual value for our filter.
p_column_name is the name of the column we wish filter on. Note that if we leave this null, the filter will apply to the whole row.
p_operator_abbr represents the filter operation. Note that in case of a row filter, the operation will always be “contains” and this parameter will be ignored. The following options are available:
|LTE||Less than or equal to|
|GTE||Greater than or equal to|
|LIKE||SQL Like operator|
|IN||SQL In Operator|
|NIN||SQL Not In Operator|
p_is_case_sensitive, a boolean, determines if the filter should be case sensitive. Note this is only valid for row based filters.
p_report_id is the ID of the Interactive Grid report we wish to target. We can query the
apex_appl_page_ig_rpts view to find it out. More often than not however, we will only want to affect the most recently viewed (or the current) report, in which case we can leave this attribute null.
Our call will end up looking something like this:
declare l_region_id number; begin select region_id into l_region_id from apex_application_page_regions where application_id = :APP_ID and page_id = :APP_PAGE_ID and static_id = 'emp'; apex_ig.add_filter ( p_page_id => :APP_PAGE_ID , p_region_id => l_region_id , p_filter_value => 'PRESIDENT' , p_column_name => 'JOB' , p_operator_abbr => 'EQ' , p_report_id => null ); end;
Where does this code go?
You will probably want to call this procedure in the Pre Rendering or Processing section of your page, depending on your business requirement.
Calling this function from an AJAX context (Execute PL/SQL Dynamic Action) also works, but as of now, even if you refresh the Interactive Grid after, although the data will be filtered, the filter will not appear in the header section of the Grid. A page refresh will be needed to achieve visual consistency.
What else is in there?
Others procedures include:
- The self explanatory
validate_report_idwill validate whether the report_id belongs to a specific region
reset_reportwill reset all changes of the report, be it hidden/rearranged columns, filters, sorting, etc and bring it back to its initial state.
clear_reportwill remove all filters. Currently there is no way to target a specific filter.
delete_reportdeletes a non-primary saved report.
change_report_ownercan be used to change the owner of a non-primary saved report.
How to pass filter values for IN/NIN operator. I tried passing colon/comma separated values. But seems to be APEX is considering entire colon/comma separated values as single string resulting no rows in IG.
Very same problem here.