Skip to main content

Interactive Grid - Process Filtered Data on Server Side

Recently one of the APEX developers has reached out to me and asked if it's possible to capture filtered rows data of the Interactive Grid on the server-side and do some processing. In APEX 20.1, there is a new API APEX_IG, using which we can achieve this.

Photo by Jakub Kapusnak on Unsplash

The approach is very simple and straightforward.
  • Get the internal region id based on the Static ID given for the IG region
  • Get the last viewed report id based on region id
  • Open query context for the region and report using region id and report id
  • Fetch and loop through the rows using the query context
  • Do something with fetched rows
  • And finally, close the query context

If you have already done this for interactive reports, then you should be already aware of these steps. The only difference here is, we use APEX_IG APIs instead of APEX_IR APIs.

For the demo purpose, let's

  • Build an Interactive Grid on EMP table and let's give it a Static ID as emp
  • Create a Textarea page item with the name P41_FILTERED_ROWS. We will use this page item to store filtered employees empno data
  • Create a button to submit the page
  • Create a page process as below
    • Name: Process Filtered Data (or any other proper name)
    • Type: Execute Code
    • Execution Options > Point: Processing
    • PL/SQL Code: As follows
declare
    l_report_id     number;
    l_region_id     apex_application_page_regions.region_id%type;
    l_context       apex_exec.t_context;
    l_empno         emp.empno%type;
    l_idx_empno     pls_integer;
    l_filtered_rows varchar2( 4000 );
begin
    -- Get the internal region id based on the "Static ID" given for the IG region
    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      = 'emp';
    -- Get the last viewed report id based on region id
    -- this will have all the filters applied etc.
    l_report_id := apex_ig.get_last_viewed_report_id (
                       p_page_id   => :APP_PAGE_ID,
                       p_region_id => l_region_id );
    -- Open query context for the region and report using region id and report id
    l_context := apex_region.open_query_context (
                     p_page_id      => :APP_PAGE_ID,
                     p_region_id    => l_region_id,
                     p_component_id => l_report_id );
    -- get column position based on column name
    l_idx_empno := apex_exec.get_column_position( l_context, 'EMPNO' );
    -- Fetch and loop through the rows using the query context
    while apex_exec.next_row( l_context )
    loop
        -- get empno using index we have derived above
        -- you can use similar logic to get other columns data
        l_empno := apex_exec.get_number (
                       p_context     => l_context,
                       p_column_idx  => l_idx_empno );
        -- once you get all columns data you need, you can write your business logic below
        -- code specific to your business logic
        -- for demo purpose, just append l_empno to some APEX item
        l_filtered_rows := l_filtered_rows || ', ' || l_empno;
    end loop;
    -- And finally, close the query context
    apex_exec.close( l_context );
    :P41_FILTERED_ROWS := ltrim( l_filtered_rows,', ' );
exception
    when others then
        -- your general exception handing code
        apex_exec.close( l_context );
        raise;
end;

Please go through the inline comments and correlate them with the steps specified above for explanation. For the demo purpose, we are just reading data from one column empno and appending it to page item P41_FILTERED_ROWS.

That's it and here is the link for the demo.

Thank you.

Comments

Manfred Gahr said…
Very helpful! You made my day, thanks!

Popular posts from this blog

Interactive Grid - Conditional Enable/Disable

In this blogpost, I am going to discuss few approaches using which we can conditionally enable/disable Interactive Grid (IG) column(s) based on other column(s) values. Note:    There is a bug  30801170  in APEX 19.2/20.1 with respect to "enable/disable" dynamic actions for IG columns. Workaround for this bug is provided at the end of this blogpost . This bug has been fixed in APEX version 20.2. Client Side Only Conditions If conditions to enable/disable are simple, then we can check those conditions easily on the client side. For e.g. let's consider IG on EMP table with following SQL Query. SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP Let's consider the requirement as - Enable "Commission" column only when JOB is equals to 'SALESMAN' and disable "Commission" column in all other cases. This can be done declaratively using dynamic actions (DA) DA "Enable/Disable Commission - 1" Create DA and give it a prope

Interactive Grid - Bulk Operation on Selected Rows

What's the Problem? Let's say you have an IG on employee table and you want to update selected employees commission based on user's input. Ideally, it should be very simple, where you can write UPDATE statement in page process and select IG region as "Editable Region" under "Execution Options" of the page process. But, when you select rows and submit page, you can see that, this process won't get executed! The reason is  Selection of 'Row Selector' check-boxes is not considered as row-change. Thus selected rows are not submitted to server. So, is there any work around?  Yes! Luckily there are lot of JavaScript (JS) APIs available to work with IG. If you are not already aware, you can refer "APEX IG Cookbook"  or  JavaScript API Reference documentation. If we continue with above Employee IG example, when user selects IG rows, enters "Commission %" and clicks on "Update Commission" button, then we can writ