Quick and Dirty Inline Charts and RATIO_TO_REPORT

Another post from the archives...

HTML Expressions is a feature in the Application Express reporting engine that lets you apply an HTML mask to a column value in a report. In the HTML Expression, you refer to the column value using #COLUMN_NAME#. Recently, I saw this used to create neat inline bar charts like the one below:

rep_barchart.png

To create this example, I used the following query based on the Issue Tracker data model:

select pname, 
       cnt_issues,
       ratio_to_report (cnt_issues) over () * 100 issues_chart,
       round (ratio_to_report (cnt_issues) over () * 100, 1) issues_ratio
  from (
select p.person_name pname, count(*) cnt_issues 
  from ht_issues i, ht_people p
 where i.assigned_to = p.person_id
 group by p.person_name
  )

I then edited the column attritbues for the ISSUES_CHART column and placed the following HTML in the the HTML Expression field:

<div style="width:100px;height:14px;background:#dddddd;
border-top:1px solid #aaaaaa;border-left:1px solid #aaaaaa;
border-bottom:1px solid #ffffff;border-right:1px solid #ffffff;">
<img src="/i/1px_trans.gif" width="#ISSUES_CHART#" height="14" border="0" style="background:#3f863f;"></div>

In this HTML with CSS style declarations, the width:100px in the beginning controls the maximum length in pixels of each bar in the chart and background:#dddddd makes the background grey. The width="#ISSUES_CHART#" controls the actual length of the green part of the bar by using the value of issues_chart from the result set.

Comments:

Hi Sergio, Your earlier blog postings on APEX were really useful and valuable. I hope you are able to re-post all your earlier blog postings. Thanks, Ramesh

Posted by Ramesh on October 24, 2008 at 05:51 AM PDT #

Hi Sergio, While looking for something else I found the following procedure in APEX 3.1.2 . The HTMLDB_UTIL package has a procedure html_pct_graph_mask which implements an inline chart. Instead of modifying HTML expression field, place the following text in the Number / Date format field:
PCT_GRAPH:DDDDDD:3f863f:100
. This results in the same inline chart in a more declarative way. With kind regards, Jornica. P.S.: Nevertheless a good article, keep re-posting.

Posted by Jornica on November 01, 2008 at 07:01 AM PDT #

@Jornica: Thanks for pointing out a feature in APEX I wasn't aware of. This shows how old the original post is (and how relatively out of touch I am with APEX since leaving the team). The new name for the HTMLDB_UTIL package is APEX_UTIL and it appears the function apex_util.html_pct_graph_mask is not documented: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b32258/api.htm#CHDBDCIE Regards, Sergio

Posted by Sergio on November 05, 2008 at 10:46 PM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

bocadmin_ww

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today