A couple of years ago I
posted a method to remove nulls from a report using the
Value Attribute Paris - Column template.
Here's an example of how we might utilise the region, within the breadcrumb region position.
 |
| Note - some values may have been adjusted from this screenshot for their protection. |
Any nulls were shown as a tilde, then hunted down and eliminated with some jQuery that executes after refresh of the region, and/or on load of the page
 |
| Cool idea, can be improved. |
And when the JavaScript was placed on one line, it seems so innocuous. It works, so what?
What's the harm?
$('dd.t-AVPList-value').each(function(){if ($(this).text().indexOf('~')>0) $(this).hide().prev().hide()})
The trouble with my original method is that it's executing jQuery after the page is rendered. This represents extra work that could be eliminated. The same justification was present in Oracle Forms, as Post-Query triggers were not preferred.
And of course the applies to DML being applied to the database. Sure, you may need some conditional processing and apply a zillion single updates, or you could write some
elegant SQL to do it within one update.
The other problem is that the Universal Theme responds to this change with some content movement that can frustrate the user - the body bubbles up to meet the removed content.
New and Improved Solution
Side-note: how can it be
new and improved?
In this case I've been talking about columns from a relative simple query, as simple as selecting from scott.emp.
If we can transpose these results, like a
magic wand you can do in Excel, then we could just use the
Value Attribute Pairs - Row version of the template, and not worry about any jQuery that manipulates the page after it's rendered.
We tranpose columns by surrounding the existing query with a
simple UNPIVOT.
select * from (
select to_char(empno) empno
,ename
,job
,to_char(sal) sal
,to_char(comm) comm
from scott.emp
where empno = 7788
--where empno = 7654
)
unpivot
(val
for name in (
(empno) as 'Emp No'
,(ename) as 'Name'
,(job) as 'Job'
,(sal) as 'Sal'
,(comm) as 'Commission'
)
);
NAME VAL
---------- --------
Emp No 7788
Name SCOTT
Job ANALYST
Sal 3000
4 rows selected.
... query executed with other empno
NAME VAL
---------- ---------
Emp No 7654
Name MARTIN
Job SALESMAN
Sal 1250
Commission 1400
5 rows selected.
Notice the result using an emp with a commission shows more rows. The page will only render the data supplied so there no dynamic action required.
Default ordering seems to honour the order of elements in the FOR expression.
Datatypes of columns must match up, hence the to_char around the numeric columns.
Update - I saw Mike on the forums suggest that "transpose" implies a matrix, and offered a combined unpivot/pivot.
tl;dr steps
- surround the existing query with unpivot
- add any datatype conversions necessary to make columns the same
- change report region from pairs with column to pairs with row
- remove declarative column ordering
- remove the dynamic action that finds the tildes
If you want to modify how something presents itself on an APEX page, there are other options to explore before jQuery
- Template options
- Conditional SQL, manifesting as HTML expression in a column
- CSS solutions trump JavaScript.
Simplify, man.
