Pre‑Filter an APEX Interactive Grid Without Page Items

You know the drill, there is a need to redirect user to specific page and pre-filter a report with a value in a link.

So you make a page item filter, that will just take place on the page. Or possibly a hidden page item. But user is not able to save the report with the filter and if it is hidden, he may ask you why he cant see the rest of the data.

But there is a better solution how to apply filters. And it can add filters to any column in your Interactive Grid. Let's see how it works.

Solution - APEX_IG Package

There is a package APEX_IG, that has an ADD_FILTER procedure. It enables you to use a PLSQL procedure and set correct filters.

APEX_IG.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 IN BOOLEAN  DEFAULT FALSE,
    p_report_name       IN VARCHAR2 DEFAULT NULL );

The procedure is using internal region_id, so it is important to get it first. To do it, you can use the query below.

 -- Get the region ID for the IG region with static ID 'employees'
    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      = 'employees';

The procedure offers a lot of condition types for filtering:

EQ = Equals

NEQ = Not Equals

LT = Less than

LTE = Less than or equal to

GT = Greater Than

GTE = Greater than or equal to

N = Null

NN = Not Null

C = Contains

NC = Not Contains

IN = SQL In Operator

NIN = SQL Not In Operator

It is also possible to combine multiple conditions.

What is also important, the package can reset report or clear the report from previously used filters.

--Reset
APEX_IG.RESET_REPORT (
    p_page_id   IN NUMBER,
    p_region_id IN NUMBER,
    p_report_id IN NUMBER DEFAULT NULL ); 
--Clear
APEX_IG.CLEAR_REPORT (
    p_page_id   IN NUMBER,
    p_region_id IN NUMBER,
    p_report_id IN NUMBER DEFAULT NULL ); 

Demo application

I created demo application Employees, with 3 main pages: Departments, Employees and Jobs.

To show how the PLSQL filters can be used, I created redirect from Departments and Jobs to Employees, that will filter the Employees report for specific department or job.

Employees page

Employees page is standard Interactive grid.

In a page breadcrumb, I created 2 hidden page items: P4_FILTER_NAME and P4_FILTER_VALUE.

These are my placeholders for incoming filter values from the link. Then I created also Set Filters process, it is in the Pre-Rendering/Before Header section of the page.

The process PLSQL code looks like below

DECLARE
    l_region_id   apex_application_page_regions.region_id%TYPE;
    l_filter_value VARCHAR2(1000); 
BEGIN
    -- Get the region ID for the IG region with static ID 'employees'
    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      = 'employees';
    -- Clear existing filters for the IG region
    APEX_IG.CLEAR_REPORT(
        p_page_id   => :APP_PAGE_ID,
        p_region_id => l_region_id,
        p_report_id => NULL
    );
    -- IF there is filter value, add a new filter to the IG region based on the specified column 
    IF :P4_FILTER_VALUE IS NOT NULL THEN
        -- Department
        IF :P4_FILTER_NAME = 'DEPARTMENT_ID' THEN
            select department_name into l_filter_value from EBA_GRAPHVIZ_DEPARTMENTS 
            where department_id = :P4_FILTER_VALUE;
        -- Job
        ELSIF :P4_FILTER_NAME = 'JOB_ID' THEN
            select job_title into l_filter_value from EBA_GRAPHVIZ_JOBS 
            where job_id = :P4_FILTER_VALUE;
        ELSE
            l_filter_value := :P4_FILTER_VALUE;
        END IF;
        apex_ig.add_filter(
            p_page_id        => :APP_PAGE_ID,
            p_region_id      => l_region_id,
            p_filter_value   => l_filter_value,
            p_column_name    => :P4_FILTER_NAME,
            p_operator_abbr  => 'EQ',
            p_report_id      => NULL
        );
    END IF;
END;

It got a little bit more complicated, because I used a Select list columns for Job and Department. And I want to show job and department name, not just IDs.

First I get the real region_id from apex_application_page_regions, then I clear the report. After that, there are 2 queries, that get the job title or department name, based on the filter name. This will not be necessary if you use a direct column without a dropdown - or if you pass the name. And in the end the add_filter procedure is applied.

The Interactive Grid Employees has no modifications. I don't have to incorporate the hidden page items into a query or a Page Items to Submit section.

Jobs and Departments pages

I used same principle on both pages. The departments page has a column with count of employees, that is clickable. It does not have to be count, it can be any link you want.

The Link attribute in EMPLOYEES_COUNT column is set to redirect to page 4 (Employees) and it pass the values for filter name and filter value.

Jobs page looks the same, the link just pass different values.

Result

Now if I click on the link in Departments page, it will redirect me to Employees and filter out specific department. Same for job.

I hope that this feature will help you to code better user experience without complicated page items and other methods. It enables you to filter any number of columns, without adding specific page items and incorporating them into a report query.

The demo application is available here. Thank you for reading till the end!