Saturday, 24 August 2013

Using radio buttons in an APEX report

In my previous post I used the apex_item package to create a report with html input fields and checkboxes. In this blog I want to show a way to implement radio buttons in your report.

Radio buttons are very useful when you want to hand your users a limited number of options and you want to ensure that only one option can be selected. From a user point of view, a radio button is very intuitive. To implement them in an APEX report however, is a little less intuitive. To explain the implementation we will create a report on the “EMP” table where we use radio buttons to assign employees to a department.

To get started we’ll create a report on the “EMP” table, but instead of the “DEPTNO” column we will select a set of radio buttons, one for each department number. The query looks as follows:

Note that we use rownum as index parameter. This ensures that all radio buttons on the same table row, have the same index, so only one radio button per table row can be selected. This provides a challenge later on, because we have to assess each row to determine what button is selected. This is a bit different than, for example the checkboxes in the first column of a tabular form, because they are assessed on column level, so you can refer to the f_array of that column to get each selected value.

Since we can’t use an f_array to determine department numbers, we will use jQuery to loop over the table rows and see which department is selected. To do that we need to hand each radio button an html class, this is handed in the “attributes” parameter.

In this query a display name is also handed, that can also be discarded since that name will also appear as column header, in practice you can use either one, which ever suits you best.

Also we hand our report a static id. Here it’s called “radioReport” and last we’ll create a hidden item for each department that’s in our query.

Now that the report is set up, we’ll create the jQuery function that loops over the report and check the radio buttons. In the end we want to update the "EMP" table, and assign department numbers to employees, so we’ll create a function that looks at each table row, sees what department is selected and store the employee number of that table row in the appropriate page item. For example:

This function will be handled by a dynamic action. The dynamic action must be triggered by a click on either radio button, so we use the radio button class that we added in the query as the trigger of the function.

dynamic action triggered by radio button jQuery function mapping employees to departments

Now that we have our report and we can check which employee belongs that each department, we have to write an update procedure. For simplicity we’ll use a page process that is triggered on submit of the page, for this we need a submit button and a page submit process. The update on the “EMP” table can be done in a number of ways; here we’ll use the “STRING_TO_TABLE” function to map each employee number of a page item into an associative array, the “APEX_APPLICATION_GLOBAL.VC_ARR2”. For the update we loop over each array and update the department for each employee.

And now we have a working report with radio buttons. You can check a working demo on my demo application. Of course don’t forget to check the Oracle docs for more info on apex_items, or string_to_table.

Good luck!