Column Actions

An extension of the standard Apex Reports column formatting functionality "Graphical formatting for percentages, whole numbers between 0 and 100" (i.e. percentage bars). More info by Martin D'Souza on the standard Apex functionality: http://www.talkapex.com/2009/06/displaying-percentage-bar-in-apex/

The package used here adds the option to show more than 1 percentage per bar in a column, and also adds options for custom CSS formatting.
At the suggestion of Nguyen Thanh Tu, I have added the option to add a text label inside the bar.

In the example above, the columns are formatted as follows:

• N 10 P : Standard number formatting for percentage bars (PCT_GRAPH:cccccc:ff0000:150)
• N 20 P Fmt : Uses the new function to mimick the standard formatting
• N 30 P Fmt : Uses the new function to showcase some fancy (and rather hideous, in this case...) formatting, and shows the text label option
• N P Fmt: Uses the new function to combine the other 3 peecentages into 1 bar, and shows the text label option per percentage

Note:
This works fine for Apex version 4.2 through 19.2. Older/newer versions may use slightly different classes and css to format the divs. Study the html produced by Apex and change the package accordingly.

Page

Identification
	Page ID	1069
	Name	Apex - Reports - Bar Charts

Region

Identification
	Sequence	10
	Title	IR
	Type	Interactive Report
Source
	Source Type	DYNAMIC_QUERY
	Region Source	with a as ( select to_char(e.hiredate, 'YYYY') as year , count(*) as n , count(case when e.deptno = 10 then 1 else null end) as n_10 , count(case when e.deptno = 20 then 1 else null end) as n_20 , count(case when e.deptno = 30 then 1 else null end) as n_30 from emp e group by to_char(e.hiredate, 'YYYY') ) , b as ( select a.* , 100 * a.n / a.n as n_p , 100 * a.n_10 / a.n as n_10_p , 100 * a.n_20 / a.n as n_20_p , 100 * a.n_30 / a.n as n_30_p from a ) select b.* , ted_p1069.div(p_width => '150px', p01_perc => b.n_10_p, p01_color_f => 'coral') as n_10_p_fmt , ted_p1069.div(p_width => '150px', p01_perc => b.n_20_p, p01_color_f => 'lawngreen') as n_20_p_fmt , ted_p1069.div ( p_width => '150px' , p_height => '25px' , p_style_b => 'background:repeating-radial-gradient(white, #ccc 10%, yellow 15%);' , p01_perc => b.n_30_p , p01_color_f => null , p01_style => 'background:linear-gradient(to top right, cyan, blue);' , p01_text => trim(b.n_30_p) || '%' , p01_title => 'Nice blue bar on rather hideous background' ) as n_30_p_fmt , ted_p1069.div ( p_width => '150px' , p01_perc => b.n_10_p , p01_color_f => 'coral' , p01_text => trim(b.n_10_p) , p02_perc => b.n_20_p , p02_color_f => 'lawngreen' , p02_text => trim(b.n_20_p) , p03_perc => b.n_30_p , p03_color_f => 'cyan' , p03_text => trim(b.n_30_p) ) as n_p_fmt from b order by b.year

Package

Package
	Name	TED_P1069
	Source	package ted_p1069 is c_style_apex_42 constant varchar2(1000) := 'border-top:1px solid #aaaaaa;border-left:1px solid #aaaaaa;border-bottom:1px solid #ffffff;border-right:1px solid #ffffff;'; -- To imitate the default bar style of Apex 4.2 (and earlier, probably) use this value for div.p_style function div ( p_width in varchar2 := '150px' -- width of the entire bar; note you are not limited to pixels here, percentages etc. will also work , p_height in varchar2 := '20px' -- height of the entire bar; note you are not limited to pixels here, percentages etc. will also work , p_style in varchar2 := null -- additional style settings for the entire bar (remember to close it properly with ;) - to imitate Apex 4.2, use the value of constant c_style_apex_42 , p_color_b in varchar2 := '#CCCCCC' -- background color (see notes below) , p_style_b in varchar2 := null -- additional style settings for the background of the entire bar (remember to close it properly with ;) , p01_perc in number := 0 -- width (in percentage) of the (1st) bar; note that this is a number, not a varchar2 like p_width , p01_color_f in varchar2 := '#777777' -- foreground color of the (1st) bar (see notes below) , p01_style in varchar2 := null -- additional style settings for this part of the (1st) bar (remember to close it properly with a ;) , p01_text in varchar2 := null -- text to show on this part of the (1st) bar , p01_title in varchar2 := null -- title to show when hovering over this part of the (1st) bar , p02_perc in number := null , p02_color_f in varchar2 := null , p02_style in varchar2 := null , p02_text in varchar2 := null , p02_title in varchar2 := null , p03_perc in number := null , p03_color_f in varchar2 := null , p03_style in varchar2 := null , p03_text in varchar2 := null , p03_title in varchar2 := null ) return varchar2 ; end ted_p1069;

Package Body

Package Body
	Name	TED_P1069
	Source	package body ted_p1069 is /* Example original formatting generated by Apex 4.2: <div style="width:250px;height:14px;background:#CCCCCC;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="168" alt="168" height="14" border="0" style="background:#15BCF3;"> </div> */ /* Example original formatting generated by Apex 5.0: <div class="a-Report-percentChart" style="background-color:#cccccc;width:150px;"> <div class="a-Report-percentChart-fill" style="width:0%; background-color:#ff0000;"></div> <span class="u-VisuallyHidden">0%</span> </div> */ function div_f ( p_height in varchar2 , p_perc in number , p_color_f in varchar2 , p_style in varchar2 , p_text in varchar2 , p_title in varchar2 ) return varchar2 is v_result varchar2(4000); begin if p_perc is not null then v_result := '<div' || ' style="' || 'display: inline-block;' || ' width: ' || trim(round(p_perc)) || '%;' -- round used here to prevent decimal errors (dot vs comma) || ( case when p_height is not null then ' height: ' || trim(p_height) || ';' else '' end ) || ( case when p_color_f is not null then ' background-color: ' || p_color_f || ';' else '' end ) || ( case when p_style is not null then ' ' || p_style else '' end ) || '"' || ( case when p_title is not null then ' title="' || p_title || '"' else '' end ) || '>' || ( case when p_text is not null then '<div style="vertical-align: middle; padding-left: 3px; height: ' || trim(p_height) || '; display: table-cell; font-weight: bold;">' || p_text || '</div>' else '' end ) || '</div>' ; end if; return v_result; end div_f; function div ( p_width in varchar2 := '150px' -- width of the entire bar; note you are not limited to pixels here, percentages etc. will also work , p_height in varchar2 := '20px' -- height of the entire bar; note you are not limited to pixels here, percentages etc. will also work , p_style in varchar2 := null -- additional style settings for the entire bar (remember to close it properly with ;) - to imitate Apex 4.2, use the value of constant c_style_apex_42 , p_color_b in varchar2 := '#CCCCCC' -- background color (see notes below) , p_style_b in varchar2 := null -- additional style settings for the background of the entire bar (remember to close it properly with ;) , p01_perc in number := 0 -- width (in percentage) of the (1st) bar; note that this is a number, not a varchar2 like p_width , p01_color_f in varchar2 := '#777777' -- foreground color of the (1st) bar (see notes below) , p01_style in varchar2 := null -- additional style settings for this part of the (1st) bar (remember to close it properly with a ;) , p01_text in varchar2 := null -- text to show on this part of the (1st) bar , p01_title in varchar2 := null -- title to show when hovering over this part of the (1st) bar , p02_perc in number := null , p02_color_f in varchar2 := null , p02_style in varchar2 := null , p02_text in varchar2 := null , p02_title in varchar2 := null , p03_perc in number := null , p03_color_f in varchar2 := null , p03_style in varchar2 := null , p03_text in varchar2 := null , p03_title in varchar2 := null ) return varchar2 is -- Notes: -- Since all colors are pure CSS, you are not limited to the "#cccccc" hex notation here, all CSS variations (like 'blue' etc.) work as well. -- Likewise, widths and heights are CSS, so instead of pixels you can use percentages etc. -- To imitate the default bar style of Apex 4.2 (and earlier, probably) use the value of constant c_style_apex_42 as the value for p_style. v_result varchar2(4000); v_apex_version_1 pls_integer; v_apex_version_2 pls_integer; begin -- determine the Apex (sub)versions select to_number( substr(version_no, 1 , i1 ) ) , to_number( substr(version_no, i1+2, i2-i1-1) ) into v_apex_version_1 , v_apex_version_2 from ( select version_no , instr(version_no, '.', 1, 1)-1 as i1 , instr(version_no, '.', 1, 2)-1 as i2 from apex_release ) a ; -- build the div v_result := '<div' || ( case when v_apex_version_1 >= 5 then ' class="a-Report-percentChart"' else '' end ) || ' style="' || ' width:' || trim(p_width) || ';' || ( case when p_height is not null then ' height:' || trim(p_height) || ';' else '' end ) || ( case when p_style is not null then ' ' || p_style else '' end ) || '">' || '<div' || ( case when v_apex_version_1 >= 5 then ' class="a-Report-percentChart-fill"' else '' end ) || ' style="' || ( case when p_color_b is not null then 'background-color:' || p_color_b || ';' else '' end ) || ( case when p_height is not null then ' height:' || trim(p_height) || ';' else '' end ) || ( case when p_style_b is not null then ' ' || p_style_b else '' end ) || '">' || div_f( p_height => p_height, p_perc => p01_perc, p_color_f => p01_color_f, p_style => p01_style, p_text => p01_text, p_title => p01_title ) || div_f( p_height => p_height, p_perc => p02_perc, p_color_f => p02_color_f, p_style => p02_style, p_text => p02_text, p_title => p02_title ) || div_f( p_height => p_height, p_perc => p03_perc, p_color_f => p03_color_f, p_style => p03_style, p_text => p03_text, p_title => p03_title ) || '</div>' || '</div>' ; return v_result; end div; end ted_p1069;