Skip to main content

Filtering Interactive Grid with/without APEX_IG


I used to like Interactive Reports, but not anymore. They look different then Interactive Grids and if you have reports and grids on same page it doesn't look good. They sometimes behave differently and I dont like that. My main issue is that switching from report to grid is painful and time consuming. Everytime I do that I loose a lot of setup, pretty much everything you set on column level. For some time I am creating every report as Interactive Grid (mostly with edit off). I save plenty of time when users decide they would like to edit that report.

Grids also offers some features missing on Reports like Column groups and Master-Detail feature.

But one thing I always missed was the ability to set grid filters from PL/SQL. Grids did't have that feature, but since APEX 20.1 they have thru APEX_IG package.

Previously I had to setup dynamic action and filter grid after it was visible to user. This grid refresh was unwanted and noticed by users.

var region_id       = 'LOGS';
var column_name     = 'TODAY';
var filter_value    = $v2('P901_TODAY');
var operator_       = 'EQ';
apex.region(region_id).widget().interactiveGrid('addFilter', {
    type            : 'column',
    columnType      : 'column',
    columnName      : column_name,
    operator        : operator_,
    value           : match_value,
    isCaseSensitive : false

Now I can setup process before page loads and user has seamless experience.

    in_static_id            CONSTANT VARCHAR2(30)   := 'LOGS';
    in_column_name          CONSTANT VARCHAR2(30)   := 'TODAY';
    in_filter_value         CONSTANT VARCHAR2(30)   := TO_CHAR(SYSDATE, 'YYYY-MM-DD');
    in_operator             CONSTANT VARCHAR2(30)   := 'EQ';
    region_id               apex_application_page_regions.region_id%TYPE;
    -- convert static_id to region_id
    SELECT region_id INTO region_id
    FROM apex_application_page_regions
    WHERE application_id    = sess.get_app_id()
        AND page_id         = sess.get_page_id()
        AND static_id       = in_static_id;
        p_page_id           => sess.get_page_id(),
        p_region_id         => region_id,
        p_report_id         => NULL
        p_page_id           => sess.get_page_id(),
        p_region_id         => region_id,
        p_column_name       => in_column_name,
        p_filter_value      => in_filter_value,
        p_operator_abbr     => in_operator,
        p_is_case_sensitive => FALSE,
        p_report_id         => NULL

Be aware that stacking multiple filters might not work as expected.