APEX Report with checkboxes (advanced)

There will be many requirements 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 but there might be some extra requirements:

- 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).

Below are the detailed steps on how to do it.

Step 1:

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

Setup your report:

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)

Step 2:

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

<script src="http://www.google.com/jsapi"></script>
<script>
  // Load jQuery
  google.load("jquery", "1.2.6", {uncompressed:true});

  function spCheckChange(pThis){
    var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=CHECKBOX_CHANGE',$v('pFlowStepId'));
    get.addParam('x01',pThis.value); //Value that was checked
    get.addParam('x02',pThis.checked ? 'Y':'N'); // Checked Flag
    gReturn = get.get();
    
    $x('checkListDisp').innerHTML=gReturn;
  }
  
</script>
CHECKBOX List:

<div id="checkListDisp">&F_EMPNO_LIST.</div>

Step 3:
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;

Step 4:
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

Then you can try out in your report checking the check boxes and paginating. Your previously checked ones in the report will still be checked once you paginate back.