SmartFiltering Multiple Regions

A fellow Oracle employee wielding the power of APEX to solve an internal business problem for her team asked an interesting question on our internal discussion forum this week. Her app shows resources partitioned into three categories in side-by-side cards regions. She wanted to use a single SmartFilter region to filter the results showing up in these three regions, each based on the same underlying table with a slightly different WHERE clause.

Wherever possible, my brain tends to map questions onto the familiar EMP and DEPT tables to simmer a question down to its essence. So I imagined side-by-side cards regions with employees in three different departments as shown below.

Setting Up the Page

I started by setting up the three cards regions based on the EMP table, and including an appropriate DEPTNO=10 where clause in the “Accounting” region, DEPTNO=20 predicate in the “Research” region, and DEPTNO=30 in the “Sales” region. Then I added a SmartFilter region and positioned it in the Before Navigation Bar slot with the w100p class to make to stretch to 100% width and fill up the space left in the center of the navigation bar.

A Twist on The Gift That Keeps on Giving

By default, a SmartFilter region filters the results of only one other region on the page. However, my colleague Carsten’s article Add a Chart to your Faceted Search Page is the gift that keeps on giving, because it inspired me with the idea I used to achieve the result our fellow APEX user desired. Carsten explains therein how to use the open_query_context() function in the apex_region package in a pipelined table function that returns the results from another region’s data source, automatically taking into account the search filters currently applied by the end user in that region. The implementation uses functions in the apex_exec package to retrieve the data.

My twist on his idea was to create a pipelined table function get_region_results_pk_values()to return only the primary key values of a given region’s filtered results. Assuming a numeric primary key column, the get_region_results_pk_values() function can accept three parameters:

1. Page id
2. Static ID of a region on that page
3. Name of the primary key column whose value we should return

The function’s pipelined result rows will each contain a NUMBER column named ID containing the primary key values identifying the rows that the filtered region is returning. For our employee-based example here, it will return ID values representing the EMPNO values of the filtered region’s query results. With this function in place, we can update the WHERE clause of the three employees cards regions to reference get_region_results_pk_values() in an additional predicate like this:

• “Accounting”
  • deptno=10 and empno in (select id from get_region_results_pk_values(:APP_PAGE_ID,'RESULTS','EMPNO'))
• “Research”
  • deptno=20 and empno in (select id from get_region_results_pk_values(:APP_PAGE_ID,'RESULTS','EMPNO'))
• “Sales”
  • deptno=30 and empno in (select id from get_region_results_pk_values(:APP_PAGE_ID,'RESULTS','EMPNO'))

Configuring the Filtered Region

To play the role of the SmartFilter region’s Filtered Region, we need an additional region based on the EMP table with no WHERE clause of its own. This is because using any of the Accounting, Research, or Sales cards regions would only filter the employee rows of one particular department and consequently its list of filtered EMPNO primary key values would not correspond to any employees in the other cards regions who, by design, belong to a different department.

So, I added an EmployeesResults classic report region based on the EMP table and configured this classic report region to be the SmartFilter region’s Filtered Region. I set the static ID of this region to RESULTS as referenced in the three WHERE clause function calls above.

A Chain Reaction of Filtering

When the end-user changes the filters on a SmartFilter, it automatically refreshes its filtered region. So in our example, the EmployeesResults classic report region will be refreshed. When this happens, we need the other three cards regions on the page to also get refreshed. Luckily, this is simple to achieve with a dynamic action event handler on the EmployeesResults region’s AfterRefresh event. I included three dynamic action steps in the TRUE branch of the event handler. Each one uses the built-in Refresh action to refresh one of the cards regions.

At this point, I had a working page with a SmartFilter at the top, whose search filters would restrict the rows in the filtered EmployeesResults region at the bottom, and whose filtered list of primary keys were correctly filtering the three separate Accounting, Research, and Sales cards regions as desired. However, seeing the classic report at the bottom was ruining the magic effect, so I sought out a way to hide it.

Hiding the Filtered Region

My first experiment was to set the Server-side Condition on the filtered EmployeesResults region to Never. This succeeded in hiding the classic report, but it also broke the chained filtering functionality being implemented by the dynamic action. I guess a region that’s not rendered on the server can’t be used to trigger events on the client, which I could understand.

For my second attempt, I went looking for properties of the classic report region that I could adjust to limit the data that it was retrieving. After all, I primarily cared about the filters applied to its data source, not seeing the results themselves in the classic report. These applied filters are important to correctly produce the filtered list of primary keys to filter the Accounting, Research, and Sales cards regions appropriately.

With the classic report EmployeesResults region selected, I studied the Attributes tab of the property palette and noticed a Number of Rows property which I tried setting to zero (0). I figured the classic report functionality related to pagination might be producing some content on the page, so I also set the Pagination > Type to the value No Pagination (Show All Rows). Since I knew that the SmartFilter region uses partial page refresh to update the filtered region, I kept the Partial Page Refresh switch in the ON position.

These changes got me very close to the desired result, but as shown below the column headings were still showing.

I managed to resolve this final issue by hiding the classic report’s column headings using the CSS property display:none. I accomplished this by adding a CSS class called hidden to the page-level CSS > Inline settings:

hidden {
   display: none;
}

After doing this, I configured the EmployeesResults classic report region’s Column CSS Classes to be the name of my hidden CSS class. After doing that, I had a working SmartFilter that was filtering the three separate cards regions, without showing the classic report region involved in triggering the chain reaction of filtering.

Filtering Code In the Demo

In practice, the APEX 21.2 demo app that you can download using the link below is based on an EMP-like table called EBA_DEMO_WORKLIST_EMP and the filtering code for the function is in a package named EBA_DEMO_WORKLIST whose specification looks like this. The package body looks very similar to Carsten’s blog article, but it’s simplified to only return the single primary key column of results in each row.

create or replace package eba_demo_worklist as 
    type eba_demo_worklist_pk_t_row is record (
        id number
    );

    type eba_demo_worklist_pk_t_tab is table 
        of eba_demo_worklist_pk_t_row;

    function get_region_results_pk_values( 
        p_page_id          in number,
        p_region_static_id in varchar2,
        p_pk_column_name in varchar2 )  
    return eba_demo_worklist_pk_t_tab pipelined;
end;
 

You can download the demo app from here, and install it into an APEX 21.2 (or later).