Oracle Apex how to dynamically add classic report rows that vary in content and type

Question

I am trying to create a 360 questionnaire dynamically using a classic report in Oracle Apex. Got the first part to work nicely using the following:

SELECT q.display_text,
       apex_item.radiogroup(rownum, 1, a.answer, null, null, null, null) "ineffective",
       apex_item.radiogroup(rownum, 2, a.answer, null, null, null, null) "sometimes"
FROM   xxpay_360_questions q,
       xxpay_360_answers   a
where  a.question_id (+) = q.question_id
and    a.user_name (+) = :APP_USER
order  by q.questionnaire_id, 
       q.display_sequence

This outputs 3 report columns. The first one is the question and the second two are the horizontal radio buttons to select answer 1 or 2. The 360 questionnaire also needs sections and sub sections and some textarea questions. For those I would like to merge the 3 report columns into 1 column (akin to colspan=1). I would probably need to output them using a union in the above select, but I'm not sure how to dynamically output a colspan and a single report column value.

Note that I am using theme 20 in order to get the Oracle Applications look and this uses table layout.

Anyone know how to output a single report column instead of 3 for some rows and then colspan=1 it? Changing the font for the section and sub-section would be a bonus.

Not sure whether css can do colspan when using table layout.

Answer 1

1. Add a column to your report query that will serve as a flag for the 3 in 1 column rows.
2. Create a new report template, make it a "named column" style.
3. Create the two different column formats you want using the #COLUMN_NAME# token for columns.
4. Set the condition for each of these two formats using the value of your new flag column.

---

The added benefit to this is you can now use HTML to do whatever formatting you end up needing later.

Answer 2

I have made following.
Test table:

create table tst as
select 1 a, 2 b, 3 c, 4 d, 5 e from dual
 union all
select 11, 12, 13, null, null from dual
 union all
select 21, 22, 23, 24, 25 from dual;

Region source:

select a, b, 
  case when d is null and e is null then
       '<td colspan="3">' || c || '</td>'
       else '<td>' || c || '</td><td>' || d || '</td><td>' || e || '</td>'
  end merged_column
 from tst

Report properties: Display as - Standard Display Column, Heading of a column merged_column:

<th>C</th><th>D</th><th>E</th>

Result looks like this:

Maybe it is not so cool and useful example, but cells in second row look merged (and they are really merged, of course). Also it is impossible to sort by columns 4 and 5, and you need manually align text there.
Sorry, can't give a link to the page, apex.oracle.com upgraded to version 5.0, version 4.2 is unavailable now.

Answer 3

I wish I had more time to work up a proper example, but you can use Oracle's LISTAGG function to group the answers into a single row per question and add some HTML tags for styling. Generally speaking, I generate something like this:

<SPAN TITLE="Some help text">Some question text?</SPAN>
 <UL>
    <LI>[RADIO group1 value1] radio_label1</LI>
    <LI>[RADIO group1 value2] radio_label2</LI>
 </UL>

Hopefully, you can use this example as a starting point to coding what you want.

As an aside, I should point out one potential issue with what you are trying to do. You may already be aware of it but, via this method, Apex is limited to displaying no more than 50 questions at a time because the value for p_idx has to be a whole number between 1 and 50. (Source: Apex documentation) You can work within that limitation, but being aware of the issue from the start is much easier than discovering it half-way through.

Good Luck!