X

The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

Build, test and deploy apps on Oracle Cloud. Start Now

Add a Chart to your Faceted Search Page

Carsten Czarski
Consulting Member of technical Staff

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:

  • When
    • Event: Custom
    • Custom Event: facetschange
    • Selection Type: Region
    • Region: Search (the facets region)
  • True Event
    • Refresh
    • Selection Type: Region
    • Region: Chart 

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.

Recent Content