This blog posting describes how to add a chart region to a page containing the new Faceted Search feature. Chart display is always in sync with Faceted Search report display.
Since APEX does not support this out-of-the-box yet, we'll utilize a very interesting enhancement to the APEX_REGION PL/SQL package, which was introduced in the latest Application Express Release 19.2: APEX_REGION.OPEN_QUERY_CONTEXT function provides access to the data of another region on the page.
FUNCTION APEX_REGION.OPEN_QUERY_CONTEXT ( p_page_id IN NUMBER, p_region_id IN NUMBER, p_component_id IN NUMBER DEFAULT NULL, p_view_mode IN VARCHAR2 DEFAULT NULL, -- p_additional_filters IN APEX_EXEC.T_FILTERS DEFAULT APEX_EXEC.C_EMPTY_FILTERS, -- p_first_row IN NUMBER DEFAULT NULL, p_max_rows IN NUMBER DEFAULT NULL, p_total_row_count IN BOOLEAN DEFAULT FALSE, p_total_row_count_limit IN NUMBER DEFAULT NULL ) return apex_exec.t_context;
The function takes in the page and region IDs, as well as (for Interactive Reports and Interactive Grids) the report ID (as the p_component_id parameter). The returned APEX_EXEC.T_CONTEXT object is similar to a cursor: With APEX_EXEC procedures, the developer can loop through the returned rows (NEXT_ROW) and use getter functions (GET_VARCHAR2, GET_NUMBER etc.) to retrieve individual column values.
So let's start by creating a Faceted Search page on top of the well-known EMP table by using the Create Page wizard. If no EMP table exists on your schema yet or you're unsure, you can re-create the EMP table by navigating to SQL Workshop > Utilities > Sample Datasets > EMP / DEPT.
The result will look similar to the following screen shot.
Make sure to set the Static ID attribute of the Classic Report region to a known value (e.g. FC_EMP). This will be needed to reference the report region later on.
As the next step, we'll need access to the current result set of the Faceted Search results report. This is where APEX_REGION.OPEN_QUERY_CONTEXT comes into the picture: The following PL/SQL table function first looks up the ID of the region, for which the static ID has been passed in ("FC_EMP"). Then it uses the OPEN_QUERY_CONTEXT procedure to execute the region's current SQL Query. The resulting T_CONTEXT object is treated like a cursor: The loop walks through the result row-by-row and emits each row as the table function result using PIPE ROW.
Make sure that the data types of the returned columns and in the APEX_EXEC calls match the data types of the Search Results report. For instance, if the report column display is based on a list of values (LOV), you have to treat it as a VARCHAR2 and not as a NUMBER column.
Create the following PL/SQL table function in your workspace schema.
create or replace type t_emp_row as object( ename varchar2(50), job varchar2(50), mgr varchar2(50), hiredate date, sal number, comm number, -- the DEPTNO column is an LOV column. Thus we treat it as VARCHAR2 deptno varchar2(50) ); / create or replace type t_emp_table as table of t_emp_row / create or replace function get_faceted_search_data( p_page_id in number, p_region_static_id in varchar2 ) return t_emp_table 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( 'ENAME', 'JOB', 'MGR', 'HIREDATE', 'SAL', 'COMM', 'DEPTNO' ) ); while apex_exec.next_row( p_context => l_context ) loop pipe row( t_emp_row( apex_exec.get_varchar2( p_context => l_context, p_column_idx => l_col_index( 'ENAME' ) ), apex_exec.get_varchar2( p_context => l_context, p_column_idx => l_col_index( 'JOB' ) ), apex_exec.get_varchar2( p_context => l_context, p_column_idx => l_col_index( 'MGR' ) ), apex_exec.get_date ( p_context => l_context, p_column_idx => l_col_index( 'HIREDATE' ) ), apex_exec.get_number ( p_context => l_context, p_column_idx => l_col_index( 'SAL' ) ), apex_exec.get_number ( p_context => l_context, p_column_idx => l_col_index( 'COMM' ) ), apex_exec.get_varchar2( p_context => l_context, p_column_idx => l_col_index( 'DEPTNO' ) ) ) ); 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_data; / sho err
Then add a chart region below the faceted search report. Use the following SQL as the chart series query.
select ename, sal from table(get_faceted_search_data(:APP_PAGE_ID, 'FC_EMP'));
Make sure to use the classic report Static ID (FC_EMP) correctly - if it's wrong, your chart will simply display a No data found message without further information. Pick ENAME as the Label and SAL as the Value column. Then run the page again.
We can now already see the chart below the Faceted Search results region. However, when we apply changes to the facets, the chart does not refresh yet. To achieve this, we will use a Dynamic Action in order to refresh the chart after the Facets have been changed. Thus add a Dynamic Action to your page as follows:
To make sure that the chart always gets the latest state of a facet, navigate to the chart series data source and add all the facets as Page Items to Submit. Then run your page. The chart should now refresh as you make changes to the facets.
The resulting behavior should be as follows:
1. One Department facet has a value. Both the report and the chart display 6 rows.
2. After changes to the facets, both report and chart adjust.
We can even take this a step further: The chart is completely indepdent from the Classic Report and only depends on the Facets region. So, if you don't like the classic report and only want to have the chart on the page, simply navigate to the Classic Report region, then to Server Side Condition and choose Never.
But do not remove the classic report - it's still needed for the Faceted Search to work correctly. With the classic report being excluded, the page looks as follows:
Try it out and let us know what you think. APEX_REGION.OPEN_QUERY_CONTEXT can also be used to get data from other region types, like Interactive Reports or Interactive Grids.