APEX Report with checkboxes (advanced).

Note: This solution was updated to account for APEX's new features and APIs. See the updated post here: http://www.talkapex.com/2015/09/report-with-checkboxes-update.html

A colleague of mine wanted to build a report with check boxes. Once the user selected all the rows, they would click submit and the application would process the rows. Sounds pretty simple and straight forward however he did have some extra requirements:

• The report is an interactive report
• There may be up to 10,000 records in the report
• When the user "scrolls" through the report (i.e. uses pagination), if they checked off a box it should remain checked the entire time (i.e. if they check an row in the 1st 15 rows, then view rows 16~30, then go back to rows 1~15 it should remain checked)

So here's the example on how to do it. The working example can be found here: http://apex.oracle.com/pls/otn/f?p=20195:500

Create an application item F_EMPNO_LIST. Note you can use a page item as well...

Setup IR:

SELECT apex_item.checkbox (1,
                           empno,
                           'onchange="spCheckChange(this);"',
                           :f_empno_list,
                           ':'
                          ) checkbox,
       empno, ename, job
  FROM emp

Once the report is setup set the row display to 5 (you'll need it for this example)

• Add an HTML region and add the following code: (Note: the jQuery call is not needed... for now)

  
  
  CHECKBOX List:
  
  
  &F_EMPNO_LIST.
  

  Now create an application process (on Demand) called: CHECKBOX_CHANGE

  -- Application Process: CHECKBOX_CHANGE
  -- On Demand...
  
  DECLARE
    v_item_val NUMBER := apex_application.g_x01;
    v_checked_flag VARCHAR2 (1) := apex_application.g_x02;
  BEGIN
    IF v_checked_flag = 'Y' THEN
      -- Add to the list
      IF :f_empno_list IS NULL THEN
        :f_empno_list := ':' || v_item_val || ':';
      ELSE
        :f_empno_list := :f_empno_list || v_item_val || ':';
      END IF;
    ELSE
      -- Remove from the list
      :f_empno_list := REPLACE (:f_empno_list, ':' || v_item_val || ':', ':');
    END IF;
    -- Just for testing
    HTP.p (:f_empno_list);
  END;

  • On the post page create a query to view data (you can process how you need/want)

    select *
    from emp
    where instr(:F_EMPNO_LIST, ':' || empno || ':') > 0

    Please note there may be better/faster ways to implement the SQL code etc.

  Here are some links on the x01 code that I used: http://carlback.blogspot.com/2008/03/new-stuff-2-x01-and-friends.html and http://carlback.blogspot.com/2008/04/new-stuff-q.html