This Article describes an easy way to create small indicator graphics. In a recent project an interactive report was implemented showing 8 numerical values and some metadata for each dataset. It was extremely difficult for the enduser to decide whether this dataset was ok or needs some further attention. After translating these values into some sort of graphical indicator, the semantic interpretation of the dataset could now take place immediately. The result of the described example below will look like:
In this example, I will only use two values so it is much more understandable. The data comes straight from the demo EMP table - so the first step is to create an interactive report showing all data from this table.
Now, we need a new function, which is responsible to create our SVG containing the indicator graphic. Lets have a look at the function-header first:
create or replace function "DRAW_INDICATOR_SVG" ( p_svg_id in NUMBER , p_value_top in NUMBER , p_value_bottom in NUMBER , p_width in NUMBER default 80 , p_height in NUMBER default 12 , p_percent_span in NUMBER default 110) return CLOB
- p_svg_id is any ID that the SVG will get - this might be useful later (e.g. for Dynamic Actions )
- p_value_top is a value between -1 and 1 and will be shown on the top half of the indicator
- p_value_bottom is a value between -1 and 1 and will be shown on the bottom half of the indicator
- p_width is half the width - the size of the resulting SVG will be p_width * 2
- p_height is half the height
- p_percent_span - means the indicator goes from -110% to 110% - or a value of -1.1 to 1.1 - so it can be used for scaling
The functions main code is shown next (sorry for the long lines, but i tend to fully use the available space on the screen :-) ):
create or replace function "DRAW_INDICATOR_SVG" (
p_svg_id in NUMBER
, p_value_top in NUMBER
, p_value_bottom in NUMBER
, p_width in NUMBER default 80
, p_height in NUMBER default 12
, p_percent_span in NUMBER default 110)
return CLOB
is
l_svg CLOB;
l_pos NUMBER;
l_color VARCHAR2(100);
l_triagle_width NUMBER := round(p_height/2.5);
begin
-- open SVG
l_svg := l_svg || '<svg id="scg_indicator_'
|| p_svg_id
|| '" width="'
|| (p_width*2)
|| '" height="'
|| (p_height*2)
|| '">';
-- indicator top
l_pos := round(p_width + (p_width * p_value_top * 100 / p_percent_span));
if p_value_top < 0
then l_color := 'red';
else if p_value_top > 0
then l_color := 'green';
else l_color := 'gray';
end if;
end if;
l_svg := l_svg || '<path d="M '
||l_pos||' '||p_height
||' L '||(l_pos-l_triagle_width)||' 0'
||' L '||(l_pos+l_triagle_width)||' 0'
||' Z" stroke="'||l_color
||'" fill="'||l_color
||'" class="line_bar"'
||' title="Top: '||round(p_value_top*100)||'%" />';
if p_height > 15
then l_svg := l_svg || '<text x="'||(l_pos-4)||'" y="'||(10)
||'" font-family="Arial" font-size="11"'
||' fill="white" title="Top: '||round(p_value_top*100)
||'%" >T</text>';
end if;
-- indicator bottom
l_pos := round(p_width + (p_width * p_value_bottom * 100 / p_percent_span));
if p_value_bottom < 0
then l_color := 'red';
else if p_value_bottom > 0
then l_color := 'green';
else l_color := 'gray';
end if;
end if;
l_svg := l_svg || '<path d="M '
||l_pos||' '||p_height
||' L '||(l_pos-l_triagle_width)||' '||(p_height*2)
||' L '||(l_pos+l_triagle_width)||' '||(p_height*2)
||' Z" stroke="'||l_color
||'" fill="'||l_color
||'" class="line_bar"'
||' title="Bottom: '||round(p_value_bottom*100)||'%" />';
if p_height > 15
then l_svg := l_svg || '<text x="'||(l_pos-4)||'" y="'||(p_height*2-2)
||'" font-family="Arial" font-size="11"'
||' fill="white" title="Bottom: '||round(p_value_bottom*100)
||'%" >B</text>';
end if;
-- close SVG
l_svg := l_svg || '<path d="M 0 '||p_height
||' L '||(p_width*2)||' '||p_height
||'" stroke="#AAAAAA" stroke-width="1" class="line_bar"/>';
l_svg := l_svg || '<path d="M '||p_width||' 0'
||' L '||p_width||' '||(p_height*2)
||'" stroke="#AAAAAA" stroke-width="1" class="line_bar"/>';
if p_height <= 15 then
l_svg := l_svg || '<text x="0" y="'||(p_height-2)
||'" font-family="Arial" font-size="9"'
||' fill="black" >T</text>';
l_svg := l_svg || '<text x="0" y="'||(p_height+9)
||'" font-family="Arial" font-size="9"'
||' fill="black" >B</text>';
else
l_svg := l_svg || '<text x="0" y="'||(p_height-2)
||'" font-family="Arial" font-size="9"'
||' fill="black" >-'||p_percent_span||'%</text>';
l_svg := l_svg || '<text x="'||(p_width*2-18)||'" y="'||(p_height-2)
||'" font-family="Arial" font-size="9" fill="black"'
||' >'||p_percent_span||'%</text>';
end if;
l_svg := l_svg || '</svg>';
return l_svg;
end;
Thats it for the function. We may head back to our interactive report containing the EMP table. There, we will now add an additional column:
select e.*
, DRAW_INDICATOR_SVG(
e.EMPNO
, (e.SAL / 2500) - 1
, (-e.DEPTNO / 20) + 1
) INDICATOR
from EMP e
- e.EMPNO is our ID for the indicator
- (e.SAL / 2500) - 1 will scale the value for our top indicator between -1 and 1
- (-e.DEPTNO / 20) + 1 scales the department number of the employee to a value between -1 and 1
The very last step is to set the column to "Standard Report Column".
Done. The Result will probably look like:
You may have a look on the live-exmaple here.
