The question of how to add checkboxes to interactive reports comes up often. Usually the problem is not getting the checkbox to display in a row but in how to do something useful with the checked state. There are many forum or blog posts on this topic but they all seem to be rather dated. Even when the general idea is still applicable they use outdated practices including:
- Inline event handlers using onchange or onclick attributes. These should be dynamic actions.
- HTML regions with script tags in them or script tags in the HTML Header page attribute. There are much better ways to get JavaScript on a page now. Consider JavaScript File URLs, Function and Global Variable Declaration, and Execute when Page Loads attributes as well as dynamic actions.
- Making ajax requests using the
htmldb_Get
API. This should be replaced with dynamic actions orapex.server.process
API calls.
So I thought I would take a stab at solving this problem.
There are two possible things one may be trying to achieve with checkboxes. One is selecting one or more report rows for the purpose of taking some action on that row. For example deleting, moving, copying, bulk editing, etc. all the selected rows. The other use is to actually edit the underlying report data. For example the table may contain a COMPLETED column and when a checkbox is checked the value is updated with a ‘Y’.
Here I will only be considering the first use case where the checkbox is used for selecting rows. The intent is to select a reasonably small number of rows from an Interactive Report so that those rows can be processed or acted on in some way. The end result is a list of the row ids (primary keys). The selection can span multiple pages of the report and should not be lost when paging. It is also desirable to have a checkbox in the column header to select or deselect all rows on the current page.
One question to ask is why a checkbox at all? There are many examples of UI that allow selecting things/rows etc. that don’t use checkboxs. It should be possible to support selecting with both mouse and keyboard without using a checkbox. However with Interactive Reports, which don’t have the concept of selected rows or even table navigation built in, it would be difficult to implement the necessary behaviors and visual cues. So the checkbox remains the simplest thing to do and it is common in web apps for this same reason. It provides a visual cue that the row can be selected and shows the current selected state, supports tab for navigation and can be activated with mouse or keyboard. In addition I think that you should be able to click anywhere in the row to select it.
Most examples you see that put a checkbox in an interactive report make use of the APEX_UTIL.CHECKBOX2
function. This results in one of the Fnn
arrays being populated. But we don’t really need that for selection and more importantly the Fnn
arrays are not sent to the server during report pagination. This means that anything you check is lost when you go to the next page of report data.
To get the checkbox value sent to the server before the pagination request is tricky. The most common thing I have seen recommended is to use ajax to send the check state on each checkbox click or change event. This results in many little ajax requests which is not great. But the bigger problem is that you shouldn’t use synchronous ajax because it is deprecated and creates a bad user experience. This means that asynchronous requests should be used and they could be processed by the server after the pagination request. If you are currently using a dynamic action it may work now but it won’t work in the future; you must not rely on dynamic actions being synchronous. Even trying to use the Before Refresh event won’t guarantee that the server gets the selection states before the pagination event. And even if it did or if you were happy with it working most of the time you still made two ajax requests where one should have been sufficient.
There is a better way. Make use of the Page Items to Submit attribute of the Interactive Report. The value of these page items is included with pagination and all other ajax requests. The down side is that a single page item is limited in the amount of data it can hold. This means there is a limit to the number of rows you can select. With this background out of the way I’ll walk through the steps needed to create a selectable Interactive Report.
To start create a new application (or use an existing one). I called my app “Selectable Interactive Report”. (try it here.) Open the home page in page designer. Add a new Interactive Report region to the home page. Call the region Employees. The report should be on the EMP
table and include all columns. If you run the page now you will see the same old report you have probably seen a thousand times before.
Back in page designer click the Employees > Attributes in the rendering tree. Set Link Column to Exclude Link Column
. Set Maximum Rows Per Page to 5
to make it easier to test that selection works across pages.
Add the page item that will hold the list of selected employees. Its value will be a string of bar (|) separated EMPNO
values. From the item gallery drag a hidden item to the Employees region. Notice that the hidden item is not shown. This is to be expected because hidden items don’t take up any space in the layout grid. Look in the rendering tree and you will see it there. Select it and change the name to P1_SELECTED
. Then add P1_SELECTED
to the Interactive Report Page Items to Submit.
Next change the Interactive Report SQL Query to include a selected column. This will be used to determine if the checkbox should be checked initially.
select EMPNO,
case when instr(:P1_SELECTED, '|' || EMPNO || '|') > 0 then
'checked'
else
''
end as selected,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
from EMP
Expand the Columns node in the rendering tree and select the SELECTED column. Set the Type to Hidden Column
.
We will use the EMPNO
column as the checkbox column. Select it in the rendering tree. For the Heading property enter:
<input type="checkbox" value="all">
Set the Column Alignment to center. For the HTML Expression property enter:
<input type="checkbox" #SELECTED# value="#EMPNO#">
Notice that the checkboxs are rendered using markup rather than APEX_UTIL.CHECKBOX2
. Like I said there is no need to use the Fnn
arrays.
Under the Enable User To group set everything to No
. Feel free to give the other columns nicer Heading labels. If you run the report now you will see that it has a checkbox column but the checkboxes don’t do anything useful yet.
The next step is to implement all the dynamic client behavior. This is done with two dynamic actions. The main one is a click handler on the Employee report region. By handling the click at the region level we get notified about all clicks no matter where they happen within the region. This can be very useful and is more efficient than registering handlers on every checkbox. The key is that the JavaScript action needs to look at the event target to know where the click happened and take appropriate action. The other dynamic action fixes up the selection states when the page first loads and after the report refreshes.
The JavaScript code needs to select the report so give the report region a Static id. Select the Employees report node and under Advanced enter myreport
for Static ID.
Select the Employees node in the rendering tree, right click and select Create Dynamic Action. Change the Name to select
. Set When: Event to Click
. Select the action under true and change the Action to Execute JavaScritp Code
. Change the Selection Type to - Select -
. This means there is no affected element. Change Fire on Page Load to No
. Then enter the following Code:
var cb$, checked, allRows$,
sel$ = $("#P1_SELECTED"),
event = this.browserEvent,
target$ = $(event.target),
th$ = target$.closest("th"),
tr$ = target$.closest("tr");
if (th$.length) {
// the click was on the "select all"
// checkbox or checkbox cell
cb$ = th$.find("input");
if (cb$.length && cb$.val() === "all") {
checked = cb$[0].checked;
if (target$[0].nodeName !== 'INPUT') {
// only do this if the click was not on the checkbox
// because the checkbox will check itself
checked = cb$[0].checked = !checked;
}
if (sel$.val() === "") {
sel$.val("|");
}
$("#myreport").find("td input").each(function() {
this.checked = checked;
// give a visual style to the [un]selected row
$(this).closest("tr").toggleClass("selected", checked);
// update the hidden selected item
sel$.val(sel$.val().replace("|" + this.value + "|", "|"));
if (checked) {
sel$.val(sel$.val() + this.value + "|");
}
});
}
} else if (tr$.length) {
// the click was on some other data row
cb$ = tr$.find("td").first().find("input");
checked = cb$[0].checked;
if (target$[0].nodeName !== 'INPUT') {
// only do this if the click was not on the checkbox
// because the checkbox will check itself
checked = cb$[0].checked = !checked;
}
// give a visual style to the [un]selected row
tr$.toggleClass("selected", checked);
// update the hidden selected item
if (checked) {
if (sel$.val() === "") {
sel$.val("|");
}
sel$.val(sel$.val() + cb$.val() + "|");
} else {
sel$.val(sel$.val().replace("|" + cb$.val() + "|", "|"));
}
// update the select all checkbox state
allRows$ = $("#myreport").find("td input");
checked = (allRows$.length === allRows$.filter(":checked").length);
$("#myreport").find("th input")[0].checked = checked;
}
Now create another dynamic action on the report region. Name this one after refresh
and make sure the After Refresh
event is selected. Change the Show Action to Execute JavaScript Code
. Change the Selection Type to - Select -
. Leave Fire on Page Load set to Yes
. Enter the following code:
var checked,
allRows$ = $("#myreport").find("td input");
allRows$.filter(":checked").closest("tr").addClass("selected");
checked = (allRows$.length === allRows$.filter(":checked").length);
$("#myreport").find("th input")[0].checked = checked;
You may have noticed that the above code in addition to checking the checkbox also adds a selected class to the row. This is to give the whole row a visual indication that it is selected. Add the following Inline CSS to the page to complete the style effect.
.a-IRR-table tr.selected td {
background-color: #2d7bbb;
color: #fff;
}
.a-IRR-table tr.selected:hover td {
background-color: #399bea;
color: #fff;
}
Now save and run the page. The checkboxes should be fully functional. The nice thing about using a page item to save the selected rows is that you can page through the report and even filter without loosing which rows are selected. You have full control over when you clear out the selection. You do this by setting P1_SELECTED
to null/empty string.
Here are some cautions and limitations of this technique.
- If the length of the list of selected row ids becomes larger than 4000 characters then the SQL expression gives an error. Someone with more SQL experience than me might find a solution to this. The code could be improved to not allow the P1_SELECTED value to get that long.
- The selected rows are not sent to the server unless the page is submitted or the report refreshes for any reason. This may not be a problem. If it is session storage may be a solution or you could use the beforeunload event to warn if the selection has changed but the page is not being submitted.
- The JavaScript code was not tested to make sure it doesn’t cause any problems if the report goes into different modes such as icon view, piviot, or chart. Also the code will need to be adjusted if the report contains any other columns with form fields or buttons.
This has shown a way to implement selectable rows. It has not discussed what to do with the selected rows. It should be very easy to work with the list of values in P1_SELECTED. You could combine this technique with the Custom Menus I previously wrote about so that the context menu actions could act on the current selection. There are probably many was that this code could be improved. Share your thoughts in the comments. Someone could also probably turn this into a plugin.
How do I tick all check boxes via a button
Hi
I have a form with the default boxes are clear.
I would like to place a button on the page to tick all check boxes. Any idea how I go about this??
The table on which this report is being generated has a composite primary key, so when the user selects multiple checkboxes, I’m not able to figure out how to identify which all rows were selected by user. This is because according to my knowledge, through p_value in apex_item.
I have an Interactive Report which I’m generating using a collection. The table on which this report is being generated has a composite primary key, so when the user selects multiple checkboxes, I’m not able to figure out how to identify which all rows were selected by user.
A nice alternate approach, works clean for my simple cases. I’ll give it a go in a case w more complex logic and js on the page – will report back Thank you!
Have you considered narrating your blog post and/or making a video tutorial?
This is a great wealth of information, and for visual learners, it may help to have it in on Youtube for others to see.
Thanks Filip. I can’t give specifics about future versions but our attention as turned this way.
Interesting stuff! At our shop I did something similar in one of our APEX apps a while ago.
It’s a bit of a hassle that as an APEX programmer you have to take care of saving/restoring input elements (checkboxes, pulldown values, …) when paginating in an Interactive Report.
Would be really nice if in a future APEX version this somehow could be handled out-of-the box (in a declarative way).
After all, not all of us are Javascript programmers 😉