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:

	select
	"EMPNO",
	"ENAME",
	"JOB",
	"MGR",
	"HIREDATE",
	"SAL",
	"COMM"
	, apex_item.radiogroup( p_idx => rownum
	, p_value => 10
	, p_selected_value => deptno
	, p_display => 'Accounting'
	, p_attributes => 'class="empRadio deptAcc" '
	) as ACCOUNTING
	, apex_item.radiogroup( p_idx => rownum
	, p_value => 20
	, p_selected_value => deptno
	, p_display => 'Research'
	, p_attributes => 'class="empRadio deptRes" '
	) as RESEARCH
	, apex_item.radiogroup( p_idx => rownum
	, p_value => 30
	, p_selected_value => deptno
	, p_display => 'Sales'
	, p_attributes => 'class="empRadio deptSal" '
	) as SALES
	, apex_item.radiogroup( p_idx => rownum
	, p_value => 50
	, p_selected_value => deptno
	, p_display => 'Finance'
	, p_attributes => 'class="empRadio deptFin" '
	) as FINANCE
	from #OWNER#.EMP

view raw radioButtonsQuery hosted with ❤ by GitHub

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:

	// loop over the column a radio button column and put all related EMPNOs in a colon delimitted string.
	// Do this loop for all departments.
	var accounting = $('#radioReport .uReportStandard tbody tr td[headers="ACCOUNTING"] input:checked').map(function(){
	return $(this).parent().parent().find('td[headers="EMPNO"]').html()
	}).get().join(':')
	var research = $('#radioReport .uReportStandard tbody tr td[headers="RESEARCH"] input:checked').map(function(){
	return $(this).parent().parent().find('td[headers="EMPNO"]').html()
	}).get().join(':')
	var sales = $('#radioReport .uReportStandard tbody tr td[headers="SALES"] input:checked').map(function(){
	return $(this).parent().parent().find('td[headers="EMPNO"]').html()
	}).get().join(':')
	var finance = $('#radioReport .uReportStandard tbody tr td[headers="FINANCE"] input:checked').map(function(){
	return $(this).parent().parent().find('td[headers="EMPNO"]').html()
	}).get().join(':')
	// Send the delimited EMPNO's to hidden page items to submit.
	$s('P25_ACCOUNTING',accounting)
	$s('P25_RESEARCH',research)
	$s('P25_SALES',sales)
	$s('P25_FINANCE',finance)

view raw mapRadioButtons hosted with ❤ by GitHub

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.

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.

	declare
	p_acc APEX_APPLICATION_GLOBAL.VC_ARR2 := APEX_UTIL.STRING_TO_TABLE(v('P25_ACCOUNTING'));
	p_res APEX_APPLICATION_GLOBAL.VC_ARR2 := APEX_UTIL.STRING_TO_TABLE(v('P25_RESEARCH'));
	p_sal APEX_APPLICATION_GLOBAL.VC_ARR2 := APEX_UTIL.STRING_TO_TABLE(v('P25_SALES'));
	p_fin APEX_APPLICATION_GLOBAL.VC_ARR2 := APEX_UTIL.STRING_TO_TABLE(v('P25_FINANCE'));
	begin
	for i in 1 ..p_acc.count
	loop
	update emp
	set deptno = 10
	where empno = p_acc(i)
	;
	end loop;
	for i in 1 ..p_res.count
	loop
	update emp
	set deptno = 20
	where empno = p_res(i)
	;
	end loop;
	for i in 1 ..p_sal.count
	loop
	update emp
	set deptno = 30
	where empno = p_sal(i)
	;
	end loop;
	for i in 1 ..p_fin.count
	loop
	update emp
	set deptno = 50
	where empno = p_fin(i)
	;
	end loop;
	end;

view raw updateEmployees hosted with ❤ by GitHub

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!