This example shows how to implement a matrix of toggleble values in an interactive report in Apex. 

In a use-case regarding the rights-roles-management it was neccesary to assign rights to roles in a very simple manner. The goal is to have an interactive report that looks like following screenshot, that has the ability to toggle the values by simply clicking on it.

For our example we want to assign an Employee from the EMP table to 0..n Departments from the DEPT table. Thats why, an additional mapping table is needed in the background that connects an employee to a department. The new table is called DEPT_EMP.

CREATE TABLE  "DEPT_EMP" 
   (	"DEPT_NO" NUMBER NOT NULL ENABLE, 
	"EMP_NO" NUMBER NOT NULL ENABLE, 
	 CONSTRAINT "DEPT_EMP_PK" PRIMARY KEY ("DEPT_NO", "EMP_NO")
  USING INDEX  ENABLE
   )

To display the correct icon within the interactive report, we need an additional function that returns the image with an specific ID for later use.

create or replace function "IR_MATRIX_CHECK" (p_dept_no in NUMBER, p_emp_no in NUMBER) 
return VARCHAR2
is
    l_count NUMBER;
begin
    select COUNT(1) into l_count
    from DEPT_EMP
    where DEPT_NO = p_dept_no and EMP_NO = p_emp_no
    ;
    if l_count > 0 then
        return '<img class="togglevalue" id="'||p_dept_no||'_'||p_emp_no
          ||'" height="16" src="/i/Fndokay1.gif" />';
    else
        return '<img class="togglevalue" id="'||p_dept_no||'_'||p_emp_no
          ||'" height="8" src="/i/FNDCANCE.gif" style="margin:4px;" />';
    end if;
    return null;
exception when OTHERS then
    return null;
end;

After that, we can define the query for our report with following SQL-statement.

select e.EMPNO, e.ENAME, e.JOB
, IR_MATRIX_CHECK(10, e.EMPNO) as "D10"
, IR_MATRIX_CHECK(20, e.EMPNO) as "D20"
, IR_MATRIX_CHECK(30, e.EMPNO) as "D30"
, IR_MATRIX_CHECK(40, e.EMPNO) as "D40"
from EMP e

Columns "D10" .. "D40" represent the departments IDs. Don't forget to change the column type for these columns to Standard Report Column and set the column alignment to Center.

The report will now look exactly like the first picture at the top. But thats only half the job done. Now we need a solution to toggle the value by simply clicking on an icon. To do that, a Dynamic Action is required.

The trigger for the new Dynamic Action should be configured in following way.

Some True Actions are now required to

1. get the Department and Employee IDs
2. toggle the Value itself
3. Refresh the Interactive Report

To store the value we need an additional hidden Item, called P21_TOGGLE.

1. Save the image-ID in the Item P21_TOGGLE. We need a SetValue-Action configured as follows

2. For toggling the Value some PL/SQL code Execution is required.

declare
  l_dept_no  NUMBER;
  l_emp_no   NUMBER;
  l_status   VARCHAR2(2) := 'N';
begin
  -- get the department and employee ids
  l_dept_no  := substr(:P21_TOGGLE, 1, instr(:P21_TOGGLE,'_',1,1) - 1);
  l_emp_no   := substr(:P21_TOGGLE, instr(:P21_TOGGLE,'_',1,1) + 1);

  begin
    -- try to find the combination in the DEPT_EMP table
    select 'Y' into l_status
    from DEPT_EMP
    where DEPT_NO = l_dept_no and EMP_NO = l_emp_no
    ;
    -- if found, delete the mapping (toggle)
    delete from DEPT_EMP
    where DEPT_NO = l_dept_no and EMP_NO = l_emp_no    
    ;
  exception when OTHERS then
    -- if not found, create the mapping (toggle)
    insert into DEPT_EMP (DEPT_NO, EMP_NO)
    values (l_dept_no, l_emp_no)
    ;
  end;

  commit;
exception when OTHERS then
  null;
end;

3. the final thing to do now, is to refresh the interactive report. A simple Refresh Action on the Report Region will do the job here.

DONE :-) You may go to the Live-Example now.