APEX 4.2 - Dynamic list of values in interactive report

I have an interactive report with 2 columns. Report source

SELECT APEX_ITEM.CHECKBOX2(1,a.USR_CODIGO,DECODE(NVL((SELECT 1 FROM   PROYECTOS_ROLES_USUARIOS pru WHERE  pru.USR_CODIGO = a.USR_CODIGO AND pru.PRY_ID = :P92_PRY_ID),0),0,'UNCHECKED',1,'CHECKED')) as Incluir,
 a.USR_CODIGO as Usuario FROM USUARIOS a;

I want to add a third column, a dynamic list of value that should be populated by this code

SELECT ROL_DESCRIPCION AS display_value, ROL_CODIGO as return_value FROM ROLES WHERE EXISTS (SELECT 1
           FROM ROLES_USUARIOS
           WHERE ROL_CODIGO = ROLES.ROL_CODIGO
           AND USR_CODIGO = a.USR_CODIGO)) as Rol

a.USR_CODIGO should be the pk of each row of the report.

Is there any way to do this?

Solution

Yes there is, you have to use the APEX_ITEM package, specifically the select_list_from_lov function, For this to work, you need to previously create an application list of values (LOV) with your second query, and then adding the apex_item call to your first query. Noticing that you've already used the APEX_ITEM package in your first query you shouldn't have any problem adding this function:

APEX_ITEM.SELECT_LIST_FROM_LOV(
    p_idx           IN   NUMBER,
    p_value         IN   VARCHAR2 DEFAULT NULL,
    p_lov           IN   VARCHAR2,
    p_attributes    IN   VARCHAR2 DEFAULT NULL,
    p_show_null     IN   VARCHAR2 DEFAULT 'YES',
    p_null_value    IN   VARCHAR2 DEFAULT '%NULL%',
    p_null_text     IN   VARCHAR2 DEFAULT '%',
    p_item_id       IN   VARCHAR2 DEFAULT NULL,
    p_item_label    IN   VARCHAR2 DEFAULT NULL,
    p_show_extra    IN   VARCHAR2 DEFAULT 'YES')

here the p_lov parameter will be the name of the application list of values that you generated previously.

But if you don't want to generate a LOV you could use the select_list_from_query function:

APEX_ITEM.SELECT_LIST_FROM_QUERY(
    p_idx           IN    NUMBER,
    p_value         IN    VARCHAR2 DEFAULT NULL,
    p_query         IN    VARCHAR2,
    p_attributes    IN    VARCHAR2 DEFAULT NULL,
    p_show_null     IN    VARCHAR2 DEFAULT 'YES',
    p_null_value    IN    VARCHAR2 DEFAULT '%NULL%',
    p_null_text     IN    VARCHAR2 DEFAULT '%',
    p_item_id       IN    VARCHAR2 DEFAULT NULL,
    p_item_label    IN    VARCHAR2 DEFAULT NULL,
    p_show_extra    IN    VARCHAR2 DEFAULT 'YES')

Where p_query will be the second query that you posted.