I came across an APEX forum post yesterday where the author was wanting to know how they could achieve displaying blocks of 4 report rows into 4 report columns. Basically a 4 column version of the “One Column Unordered List” template. A number of years ago I posted on using a 2 column report as it was straight forward using the builtin condition of “Odd & Even numbered rows”, and this made me think why can’t I use a PLSQL expression to cater for the required 4 columns, or any variable number of columns.
Note: I’ve used the “Standard” report template for this exercise.
I like a challenge and armed with only the #ROWNUM# substitution string to use in the PLSQL expression I came up with the idea of performing BASE conversion of the #ROWNUM# value to base 4 and looking only at the far right digit to work out whether we should start or end the HTML table. Here’s a screenshot of the report template which hopefully will it explain it for me…
And here’s the supprting PLSQL function for base encoding which is a modified version from Ask Tom
FUNCTION baseX
( p_number IN NUMBER
, p_base IN NUMBER
) RETURN NUMBER AS
v_result NUMBER;
BEGIN
SELECT nvl(substr(base,length(base),length(base)),1) result
INTO v_result
FROM ( SELECT x
, ( SELECT sum(power(10,rownum-1)*trunc(mod(x,power(p_base,rownum))/power(p_base,rownum-1)))
FROM ( SELECT null
FROM dual
GROUP BY CUBE(1,2,3,4,5,6,7)
)
WHERE rownum < ln(x)/ln(p_base)+1
) BASE
FROM ( SELECT p_number x
FROM dual
)
GROUP BY x
);
RETURN v_result;
END baseX;
Here’s a screenshot from our development environment which shows the table layout in firebug, ingore that we’re using Ext JS, as the actual comments report is still an APEX report with PPR enabled.
Here’s the solution for the “Value Attribute” template:
and here’s the screenshot for the report display:
The end result is that we can display X number of report rows as columns, which gives us greater reporting flexibility.
P.S. we have also developed a flexible PLSQL solution for Matrix reports which will dynamically PIVOT your data which is used in the report region source if you’re looking for Oracle Forms like functionality whilst still using the builtin APEX reporting wizard(s).
Hi,
Can you provide me an example of the plsql solution for matrix reports, with PIVOT functionality
thanxs and regards
Applying this solution in my application …
1st column is working fine for me but 2nd,3rd row is not displayed in required form.
again 4th column is ok.
Please give me any suggestion.
Hi Raj
It would really help if you could provide an example say on apex.oracle.com.
Which template did you decide to use/copy? iDid you ensure that you left the column heading section blank? Does your baseX function compile and work correctly?
Cheers
Matt
Hi Claudio
Unfortunately I can’t disclose company code, however we use a solution based on PIVOTIMPL, do a google search on it and it should bring backa number of links that should help you on your mission.
Cheers
Matt
In my case My problem is that I have to display the row values in column format
and 4 rows will be displayed as 4 block in a report
example :
Actual table–
1st row—— sl_1 name1 grade1 location1
2nd row—— sl_2 name2 grade2 location2
3rd row—– sl_3 name3 grade3 location3
Display required in order—
col1 col2 col3 col4
————————————————————-
1 2 3 4
Name1 Name2 Name3 Name4
grade1 grade2 grade3 grade4
location1 location2 location3 location4
Please give me any suggestion because I have used your technique but for more than one value display is not exactly what I want.
Display required in order—
col1—col2—col3—col4
————————————————————-
sl_1——-sl_2——sl_3——-sl_4
Name1–Name2–Name3—Name4
grade1–grade2—-grade3—grade4
loc1——loc2——–loc3——–loc4
Please give me any suggestion because I have used your technique but for more than one value display is not exactly what I want.
Hi Raj
For this purpose I would the the first template above to get your rows as columns and within your SQL query put in the HTML markup to get the 3 columns on different rows. e.g.
Note: I had problems embedding HTML tags so only workaround was to space them.
The templates are restrictive so it will only ever work for one column, so there’s not a lot we can do, but putting HTML markup in the SQL means we can work around this as the data is preformatted before the tamplate gets it. The template just see’s it as one column.
Hope that helps.
Cheers
Matt