Skip to main content

Handling information overload: Showing data on demand in an IR

By
Using APEX Interactive Reports you can let the user decide which columns to show or hide in a report. But even then, reports with over 20 columns are still being used. So I tried to come up with a solution where you can hide less important (detail) column information and still reveal that information with minimal effort - like a mouse-over event. In this tiny example the list of employees of a department pops up when the user moves his mouse over the little question mark icon.
By adding some HTML and Javascript in the select statement (I know, that is not a "best practice"...but if you've got a better idea, please drop a comment) and some CSS you can use this technique - even for more complex "detail" reports!
The select statement used in this example is:
select dept.deptno
,      dname
,      loc
,      'onmouseover="javascript:$(''.overlay'').hide();$x_Toggle('''||dept.DEPTNO||''')">'||
''  enames
from dept
,  ( select deptno, listagg( ename, ', ') within group (order by sal) enames 
     from emp
     group by deptno
   ) emps
where emps.deptno (+) = dept.deptno
The CSS used in that statement is defined by:
One remark: You should disable sorting, filtering etc. on the "enames" column, because that makes no sense...

Edit: Thanks to Alex (see comments) the solution can be changed to something more elegant:
Just select the ENAMES column, and set these properties
- Link Text = <img src="#IMAGE_PREFIX#apps_info.gif" alt=""></a><div class="overlay" id="#DEPTNO#" style="display:none">#ENAMES#</div>
- Link Attributes = onmouseover="javascript:$('.overlay').hide();$x_Toggle('#DEPTNO#');"
- Target (URL) = javascript:void(0);

So the SQL is "clean" now and you even can enable the sorting etc again!
Labels:

Comments

Jon Barwell said…
LISTAGG - Now have a reason to move to 11gR2
5:10 pm
Anonymous said…
Somewhat unrelated to the original post, but I wanted to point out that Tom Kyte has a 'stragg' and 'clobagg' function available for download (from somewhere...probably asktom) that provides this functionality.

Paul Duncan
5:31 pm
Roel said…
Paul, Jon,
Of course you can use any function you want. The listagg is just used as an example.
Thanks for commenting though.
Roel
9:06 am
suPPLer said…
Hi, Roel!

>> By adding some HTML and Javascript in the select statement (I know, that is not a "best practice"...but if you've got a better idea, please drop a comment)

How about this: Report Attributes->Column Attributes->Edit Column->Column Formatting->HTML Expression? You can make a hidden column ENAMES and use it values in HTML Expression for other column.

Alex.
8:34 pm
Roel said…
Hi Alex,
That would be a good solution...if it was a 'regular' report. With an Interactive Report you don't have those options.
Roel
9:15 am
suPPLer said…
Roel,

Yes, but with an IR you still have a link section in the column attributes, don't you? It lets you specify text or HTML for a link, and you can use its Link Attributes field for assigning a class, that you can use as a jQuery selector in a Page HTML Header script. And you can put directly there all this JavaScript handler for the mouseover event (since it will be hard to read and to edit) or only a call to the function, that's defined in other place (e.g. Region Header).

PS: Sorry for my bad English, I have a little practice.
10:10 am
Roel said…
Hi Alex,
That is a good idea (why didn't I think of that?)!
Now I just selected my ENAMES column, set these properties
- Link Text = <img src="#IMAGE_PREFIX#apps_info.gif" alt=""></a><div class="overlay" id="#DEPTNO#" style="display:none">#ENAMES#</div>
- Link Attributes = onmouseover="javascript:$('.overlay').hide();$x_Toggle('#DEPTNO#');"
- Target (URL) = javascript:void(0);
1:37 pm
Post a Comment