Using Faceted Search in Additional Region Types
Interactive Reports, Interactive Grids, Charts...they all could benefit from faceted search.
Background
Faceted search only works for classic reports, maps, and card regions. But with just a little effort, you can extend this feature to any region type. I stumbled upon this method while working for a client. I’d love to give them credit, but I can’t always reveal my sources :). I do know that the client got the technique from a blog post that is no longer active:
https://blogs.oracle.com/apex/post/add-a-chart-to-your-faceted-search-page
I never had the pleasure to read the blog post, but I got a gist of what it must have said. I’ve written what I believe is a more generic utility to accomplish the task—perhaps an improvement on the original, but I don’t know for sure. (I tried using https://archive.org to look it up, but at the time of this writing the archive was temporarily offline.)
Overview
The overall approach is quite straightforward, but I’ll add some nuance later.
Create a faceted search based upon a classic report. Get it working exactly as you would like it to work. Get all the facets working as they should. Make sure the classic report returns exactly the data you are interested in. Give the report a static ID (e.g. EMP_R).
Create the kind of component you actually want, using the same query as the classic report. I’m going to call this a chart just to be able to refer to it, but it could be any region type that supports refresh.
Create a dynamic action that refreshes the chart after the classic report refreshes.
- At this point changing a facet will change the results of the classic report. That, in turn, will refresh the chart, but the results of the chart won’t change.
This is the hardest step, but the solution is below…so just get the idea. Create a function that returns a table type. The function will return this table type containing only the rows returned by the classic report. This table type needs to include one or more attributes that allow you to identify the rows that are returned. Generally, any row returned should be able to be uniquely identified by one or more columns. Usually they can be identified by a single primary key column. For example, if your data is about employees (the classic EMP table), you may only need to return the EMPNO. Even if the query is very complicated, a single primary key column may likely be all you need. You can review the code below to see how this works. This function will use APEX_EXEC. I’m going to call this function “ait_util.get_faceted_search_number_data”.
To the chart region query you will either add a join on get_faceted_search_number_data or you will add this to the where clause of the query. It will look something like this:
select e.empno,
e.ename,
...
d.dname
...
from emp e
join dept d on d.deptno = e.empno
join ait_util.get_faceted_search_number_data(...) facet_results on facet_results.column_value = e.empno
where ...
or (this method can also be used if you define the region directly on a table or view, not a SQL query)
select e.empno,
e.ename,
...
d.dname
...
from emp e
join dept d on d.deptno = e.empno
where empno in (select column_value from ait_util.get_faceted_search_number_data(...) )
and ...
This will limit the chart query to just the rows being displayed in the classic report.
Now you can “hide” the classic report (assuming you don’t want it to show). The classic report still needs to exist on the page, though. You can hide it using an page onload dynamic action or by adding an attribute to the region: style=”display:none” .
Note: ultimately, your classic report query only needs to have the columns in the select portion of the query that are used for facets, plus the primary key column used to limit the rows of the chart. If you have some large calculation or other potentially slow part of the query that is not used to reduce the rows returned, it does not need to be in the classic report.
The key to this, of course, is the function ait_util.get_faceted_search_number_data. I think the original post suggested creating an object type with multiple attributes. In the example above it would return a table type of an object, e.g. T_EMPLOYEE_TABLE returning object T_EMPLOYEE, with at least the EMPNO attribute. This is great and particularly important if you don’t have a way to identify the rows by a single column/attribute. In that case you will need to be able to return more than one attribute. But usually, it will be a primary key.
The Details
Steps 4 & 5 above are all about using the faceted search classic report to drive the results of the chart region (or any other region type). It’s funny because I thought we did an APEX Instant Tip on exactly this at some point in the past, but I don’t see one. In any case, I’ve made this super easy to do. The package below provides two functions, one for row identifiers that are varchar2 and the other for row identifiers that are numbers. With these functions you don’t need to create any object types. The package relies on APEX_T_VARCHAR2 and APEX_T_NUMBER. Just install the package spec and body below and you can follow the steps above to drive the results of any region type based upon a faceted search.
create or replace package "AIT_UTIL" as
--==============================================================================
-- comments about function
-- select column_value
-- from ait_util.get_faceted_search_vc2_data(
-- p_page_id => :APP_PAGE_ID,
-- p_region_static_id => 'EMP_R',
-- p_pk_column_name => 'EMPNO')
--==============================================================================
function get_faceted_search_vc2_data(
p_page_id in number,
p_region_static_id in varchar2,
p_pk_column_name in varchar2 )
return apex_t_varchar2 pipelined ;
--==============================================================================
-- comments about function
-- select column_value
-- from ait_util.get_faceted_search_number_data(
-- p_page_id => :APP_PAGE_ID,
-- p_region_static_id => 'EMP_R',
-- p_pk_column_name => 'EMPNO')
--==============================================================================
function get_faceted_search_number_data(
p_page_id in number,
p_region_static_id in varchar2,
p_pk_column_name in varchar2 )
return apex_t_number pipelined ;
end "AIT_UTIL";
/
create or replace package body "AIT_UTIL" as
--==============================================================================
-- Public API, see specification
--==============================================================================
function get_faceted_search_vc2_data(
p_page_id in number,
p_region_static_id in varchar2,
p_pk_column_name in varchar2 )
return apex_t_varchar2 pipelined
is
l_region_id number;
l_context apex_exec.t_context;
type t_col_index is table of pls_integer index by varchar2(255);
l_col_index t_col_index;
---------------------------------------------------------------------------
procedure get_column_indexes( p_columns wwv_flow_t_varchar2 ) is
begin
for i in 1 .. p_columns.count loop
l_col_index( p_columns( i ) ) := apex_exec.get_column_position(
p_context => l_context,
p_column_name => p_columns( i ) );
end loop;
end get_column_indexes;
begin
-- 1. get the region ID of the Faceted Search region
select region_id
into l_region_id
from apex_application_page_regions
where application_id = v('APP_ID')
and page_id = p_page_id
and static_id = p_region_static_id;
-- 2. Get a cursor (apex_exec.t_context) for the current region data
l_context := apex_region.open_query_context(
p_page_id => p_page_id,
p_region_id => l_region_id );
get_column_indexes( wwv_flow_t_varchar2( p_pk_column_name ) );
while apex_exec.next_row( p_context => l_context ) loop
pipe row(apex_exec.get_varchar2( p_context => l_context, p_column_idx => l_col_index( p_pk_column_name ) ) );
end loop;
apex_exec.close( l_context );
return;
exception
when no_data_needed then
apex_exec.close( l_context );
return;
when others then
apex_exec.close( l_context );
raise;
end get_faceted_search_vc2_data;
--==============================================================================
-- Public API, see specification
--==============================================================================
function get_faceted_search_number_data(
p_page_id in number,
p_region_static_id in varchar2,
p_pk_column_name in varchar2 )
return apex_t_number pipelined
is
l_region_id number;
l_context apex_exec.t_context;
type t_col_index is table of pls_integer index by varchar2(255);
l_col_index t_col_index;
---------------------------------------------------------------------------
procedure get_column_indexes( p_columns wwv_flow_t_varchar2 ) is
begin
for i in 1 .. p_columns.count loop
l_col_index( p_columns( i ) ) := apex_exec.get_column_position(
p_context => l_context,
p_column_name => p_columns( i ) );
end loop;
end get_column_indexes;
begin
-- 1. get the region ID of the Faceted Search region
select region_id
into l_region_id
from apex_application_page_regions
where application_id = v('APP_ID')
and page_id = p_page_id
and static_id = p_region_static_id;
-- 2. Get a cursor (apex_exec.t_context) for the current region data
l_context := apex_region.open_query_context(
p_page_id => p_page_id,
p_region_id => l_region_id );
get_column_indexes( wwv_flow_t_varchar2( p_pk_column_name ) );
while apex_exec.next_row( p_context => l_context ) loop
pipe row(apex_exec.get_number( p_context => l_context, p_column_idx => l_col_index( p_pk_column_name ) ) );
end loop;
apex_exec.close( l_context );
return;
exception
when no_data_needed then
apex_exec.close( l_context );
return;
when others then
apex_exec.close( l_context );
raise;
end get_faceted_search_number_data;
end "AIT_UTIL";
/
Some Nuance
Keep in mind that your classic report is going to run—even though you may hide the results. Hence, you want it to run as quickly as possible. The classic report and the chart, interactive report, interactive grid, etc. don’t have to have the exact same query. The classic report must have the columns associated with the facets in the select clause, and it should only return the primary key values that you ultimately want in the the target region (e.g. the chart). That means it’s predicate (where clause) should have everything you want in it, but it’s possible you don’t need every join, especially outer joins. Additionally, you may not need every column in the select clause. Take this interactive report as an example:
select e.empno,
e.ename,
e.mgr,
my_slow_function(p_empno => e.empno) credit_score, -- scalar subquery not helpful here
(select my_other_slow_function(p_dept_mgr_id => d.mgr_id) from dual) dept_mgr_info,
e.deptno
from emp e
join dept d on d.deptno = e.deptno
you can use shared LOVs for e.mgr and e.deptno to display the content. You may not want to have facets on credit_score and dept_mgr_info. In this case, your classic report could simply be
select empno,
mgr,
deptno
from emp
That will give you the ability to have facets on Manager and Department (using shared LOVs), and it won’t have the overhead of the join or the expensive slow functions. In the end, your IR query would be
select e.empno,
e.ename,
e.mgr,
my_slow_function(p_empno => e.empno) credit_score, -- scalar subquery not helpful here
(select my_other_slow_function(p_dept_mgr_id => d.mgr_id) from dual) dept_mgr_info,
e.deptno
from emp e
join dept d on d.deptno = e.deptno
join ait_util.get_faceted_search_number_data(
p_page_id => :APP_PAGE_ID,
p_region_static_id => 'EMP_R',
p_pk_column_name => 'EMPNO') fs_data
on fs_data.column_value = e.empno
If you are basing the region on a table or view, not a SQL query, you can just add this to the where clause of the region:
e.empno in (select column_value from ait_util.get_faceted_search_number_data(
p_page_id => :APP_PAGE_ID,
p_region_static_id => 'EMP_R',
p_pk_column_name => 'EMPNO') )