Supposed you have a report with checkboxes. Once the user selects all the rows, they can submit the page and the application would process the rows. Sounds pretty simple and straight forward however they’re some additional 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 115 it should remain checked). Same applies if a user uses the IR filters.
If you follow this blog and the above sounds familiar, that’s because it is. I wrote about this problem a very long time ago. A lot has changed since 2009 and an update on the original post is long overdue. Here’s the updated (and simplified) solution.
Create Item to Hold List of IDs
Create a hidden APEX item which will contain a comma delimited list of all the IDs that are to be checked off (in this example it will be
P1_EMPNO_LIST). Be sure to modify the
Value Protected attribute to
No. This is critical as this item will be updated via AJAX and can not have any hashing/security applied to it.
If you are loading this from a cross reference table you can use the following query in a Before Header process. This query will load all the employees from the Accounting department.
select listagg(e.empno, ',') within group (order by e.empno)
Create IR Report with Checkboxes
Create an IR with the query below. Note the
p_attributes value. This is critical as we need to identify the checkboxes that should be monitored.
In the report attributes set the
Page Items to Submit to
P1_EMPNO_LIST. Each time the report is refreshed (pagination, filters, sorting, etc) the active list of selected values will be submitted.
Create Dynamic Action (DA
Create a new DA with the attributes in the below image. This DA will append/remove the comma delimited list of IDs in
P1_EMPNO_LIST. Note the
jQuery Selector value must match what was used in the IR query above.
Configure a True action as shown below (JS code follows).
That’s all that’s required. Each time a checkbox is checked/unchecked
P1_EMPNO_LIST will be updated to reflect these changes. The checkboxes will persist each time the report is refreshed. You can see the checkbox implantation in this demo.
This solution is fairly simple to create an manage however it does have one small caveat. If the list of checked items is very large (more than 4000 characters) you may run into some
varchar2 issues. In most cases this shouldn’t be an issue but if it is you should test first.
To process the list of comma delimited list you can use the
apex_util.string_to_table function and loop over the table values. If you want to use the comma delimited list in a query the following example should work (again it does have
varchar2 size limitations).
select regexp_substr(:p1_empno_list,'[^,]+', 1, level) empno