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:<\/p>\n
htmldb_Get<\/code> API. This should be replaced with dynamic actions or apex.server.process<\/code> API calls.<\/li>\n<\/ul>\nSo I thought I would take a stab at solving<\/a> this problem.<\/p>\n<\/p>\n
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’.<\/p>\n
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.<\/p>\n
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.<\/p>\n
Most examples you see that put a checkbox in an interactive report make use of the APEX_UTIL.CHECKBOX2<\/code> function. This results in one of the Fnn<\/code> arrays being populated. But we don’t really need that for selection and more importantly the Fnn<\/code> 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.<\/p>\nTo 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<\/a>. 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.<\/p>\nThere is a better way. Make use of the Page Items to Submit<\/strong> 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.<\/p>\nTo start create a new application (or use an existing one). I called my app \u201cSelectable Interactive Report\u201d. (try it here<\/a>.) 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<\/code> 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.<\/p>\nBack in page designer click the Employees > Attributes in the rendering tree. Set Link Column<\/strong> to Exclude Link Column<\/code>. Set Maximum Rows Per Page<\/strong> to 5<\/code> to make it easier to test that selection works across pages.<\/p>\nAdd the page item that will hold the list of selected employees. Its value will be a string of bar (|) separated EMPNO<\/code> 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<\/code>. Then add P1_SELECTED<\/code> to the Interactive Report Page Items to Submit<\/strong>.<\/p>\nNext change the Interactive Report SQL Query<\/strong> to include a selected column. This will be used to determine if the checkbox should be checked initially.<\/p>\nselect EMPNO,\r\n case when instr(:P1_SELECTED, '|' || EMPNO || '|') > 0 then \r\n 'checked' \r\n else \r\n '' \r\n end as selected,\r\n ENAME,\r\n JOB,\r\n MGR,\r\n HIREDATE,\r\n SAL,\r\n COMM,\r\n DEPTNO\r\n from EMP\r\n<\/code><\/pre>\nExpand the Columns node in the rendering tree and select the SELECTED column. Set the Type<\/strong> to Hidden Column<\/code>. <\/p>\nWe will use the EMPNO<\/code> column as the checkbox column. Select it in the rendering tree. For the Heading<\/strong> property enter:<\/p>\n\r\n<input type=\"checkbox\" value=\"all\">\r\n<\/code><\/pre>\nSet the Column Alignment to center. For the HTML Expression property enter:<\/p>\n
\r\n<input type=\"checkbox\" #SELECTED# value=\"#EMPNO#\">\r\n<\/code><\/pre>\nNotice that the checkboxs are rendered using markup rather than APEX_UTIL.CHECKBOX2<\/code>. Like I said there is no need to use the Fnn<\/code> arrays.<\/p>\nUnder the Enable User To<\/strong> group set everything to No<\/code>. 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. <\/p>\nThe 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.<\/p>\n
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<\/code> for Static ID<\/strong>.<\/p>\nSelect the Employees node in the rendering tree, right click and select Create Dynamic Action. Change the Name<\/strong> to select<\/code>. Set When: Event<\/strong> to Click<\/code>. Select the action under true and change the Action<\/strong> to Execute JavaScritp Code<\/code>. Change the Selection Type<\/strong> to - Select -<\/code>. This means there is no affected element. Change Fire on Page Load<\/strong> to No<\/code>. Then enter the following Code:<\/p>\nvar cb$, checked, allRows$,\r\n sel$ = $(\"#P1_SELECTED\"),\r\n event = this.browserEvent,\r\n target$ = $(event.target),\r\n th$ = target$.closest(\"th\"),\r\n tr$ = target$.closest(\"tr\");\r\n\r\nif (th$.length) {\r\n \/\/ the click was on the \"select all\"\r\n \/\/ checkbox or checkbox cell\r\n cb$ = th$.find(\"input\");\r\n if (cb$.length && cb$.val() === \"all\") {\r\n checked = cb$[0].checked;\r\n if (target$[0].nodeName !== 'INPUT') {\r\n \/\/ only do this if the click was not on the checkbox\r\n \/\/ because the checkbox will check itself\r\n checked = cb$[0].checked = !checked;\r\n }\r\n if (sel$.val() === \"\") {\r\n sel$.val(\"|\");\r\n }\r\n $(\"#myreport\").find(\"td input\").each(function() {\r\n this.checked = checked;\r\n \/\/ give a visual style to the [un]selected row\r\n $(this).closest(\"tr\").toggleClass(\"selected\", checked);\r\n \/\/ update the hidden selected item\r\n sel$.val(sel$.val().replace(\"|\" + this.value + \"|\", \"|\"));\r\n if (checked) {\r\n sel$.val(sel$.val() + this.value + \"|\");\r\n }\r\n });\r\n }\r\n} else if (tr$.length) {\r\n \/\/ the click was on some other data row\r\n cb$ = tr$.find(\"td\").first().find(\"input\");\r\n checked = cb$[0].checked;\r\n if (target$[0].nodeName !== 'INPUT') {\r\n \/\/ only do this if the click was not on the checkbox\r\n \/\/ because the checkbox will check itself\r\n checked = cb$[0].checked = !checked;\r\n }\r\n \/\/ give a visual style to the [un]selected row\r\n tr$.toggleClass(\"selected\", checked);\r\n \/\/ update the hidden selected item\r\n if (checked) {\r\n if (sel$.val() === \"\") {\r\n sel$.val(\"|\");\r\n }\r\n sel$.val(sel$.val() + cb$.val() + \"|\");\r\n } else {\r\n sel$.val(sel$.val().replace(\"|\" + cb$.val() + \"|\", \"|\")); \r\n }\r\n \/\/ update the select all checkbox state\r\n allRows$ = $(\"#myreport\").find(\"td input\");\r\n checked = (allRows$.length === allRows$.filter(\":checked\").length);\r\n $(\"#myreport\").find(\"th input\")[0].checked = checked;\r\n}\r\n<\/code><\/pre>\nNow create another dynamic action on the report region. Name this one after refresh<\/code> and make sure the After Refresh<\/code> event is selected. Change the Show Action<\/strong> to Execute JavaScript Code<\/code>. Change the Selection Type<\/strong> to - Select -<\/code>. Leave Fire on Page Load<\/strong> set to Yes<\/code>. Enter the following code:<\/p>\nvar checked,\r\n allRows$ = $(\"#myreport\").find(\"td input\");\r\nallRows$.filter(\":checked\").closest(\"tr\").addClass(\"selected\");\r\nchecked = (allRows$.length === allRows$.filter(\":checked\").length);\r\n$(\"#myreport\").find(\"th input\")[0].checked = checked;\r\n<\/code><\/pre>\nYou 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<\/strong> to the page to complete the style effect.<\/p>\n.a-IRR-table tr.selected td {\r\n background-color: #2d7bbb;\r\n color: #fff;\r\n}\r\n.a-IRR-table tr.selected:hover td {\r\n background-color: #399bea;\r\n color: #fff;\r\n}\r\n<\/code><\/pre>\nNow 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<\/code> to null\/empty string.<\/p>\nHere are some cautions and limitations of this technique.<\/p>\n
\n- 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.<\/li>\n
- 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.<\/li>\n
- 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.<\/li>\n<\/ul>\n
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<\/a> 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.<\/p>\n","protected":false},"excerpt":{"rendered":"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<\/p>\n