Have you ever needed a Line graph with missing Data points.. The standard line graph included in APEX 5.1 does not handle the missing data in a nice way. Examples are worth a thousand words so..
We have a table
create table temptbl
( s
varchar2(20), l varchar2(20), n number ) tablespace db_info_ts;
insert into temptbl values ('L1','a',5);
insert into temptbl values ('l1','c',10);
insert into temptbl values ('L1','e',15);
insert into temptbl values ('L2','b',5);
insert into temptbl values (' l2 ','d',10);
insert into temptbl values ('L3','B',3);
insert into temptbl values ('L3','c',3);
insert into temptbl values ('L3','d',3);
insert into temptbl values ('l3','e',3);
insert into temptbl values ('L4','a',7);
insert into temptbl values ('L4','f',7);
commit;
The S column here stands for my series, L stands for labels (x values) and n stand for the values.
Notice Series are missing some of the labels I want a report showing labels a-f
Also the series and labels must have been hand entered as there are spaces and upper/lower values.. In short we have a mess.
If I create a series line graph using this data with
select
s,l,n from temptbl
The results are disasterous!
After an initial attempt at fixing this problem, see below original solution. I went to KSCOPE 17 and talked with Patrick Wolf from Oracle about the issue. He provide this excellent javascript that needs to go into the graph -> Atrribute -> Javascript
function( options ){
function sortArray( values ) {
values.sort( function( a, b ) {
if ( a.name < b.name ) {
return -1;
} else if ( a.name > b.name ) {
return 1;
}
return 0;
});
}
function fillGaps( groups, items ) {
// data points do have to be in the same order as the groups array
sortArray( items );
for ( var groupIdx = 0; groupIdx < groups.length;
groupIdx++ ) {
// each group entry must have a corresponding entry in the items array, because JET will
// perform a simple lookup by array index
if ( !items[ groupIdx ] || items[ groupIdx ].name !== groups[ groupIdx ].name ) {
// add an undefined entry for a missing data point
items.splice( groupIdx, 0, undefined );
} else if ( items[ groupIdx ].id !== groupIdx ) {
// correct the id if we have added new data points
items[ groupIdx ].id = groupIdx;
}
}
}
// Setup a
callback function which gets called when data is retrieved, it allows to manipulate the series
options.dataFilter
= function( data ) {
//
filling the gaps is only needed if we are dealing with multiple series
if (
data.series.length > 1 ) {
// it is not guaranteed that groups are sorted, get them in order
sortArray( data.groups );
for ( var seriesIdx = 0; seriesIdx < data.series.length; seriesIdx++ ) {
fillGaps( data.groups, data.series[ seriesIdx ].items );
}
}
return data;
};
return options;
}
------------------------------------------ Original Solution-----------------------------------------------See
But there is an easy way to fix it, using function returning sql
declare
t_id number;
begin
t_id := fill_graph_gaps('select s,l,n from temp','I');
return 'select s,l,v from graph_data where graph_data_id = '||t_id||'order by l';
end;
And Poof! you have a beautiful graphs
The magic is in using a combination of a Global temporary table
create global
temporary table tmp_graph_align
( series_col
varchar2(200), label_col varchar2(200), graph_value number) on commit delete
rows;
and a permanent table
CREATE TABLE GRAPH_DATA
(
GRAPH_DATA_ID
NUMBER,
BUILD_DT
TIMESTAMP(6)
DEFAULT systimestamp,
S
VARCHAR2(200 BYTE),
L
VARCHAR2(200 BYTE),
V
NUMBER
);
create sequence graph_data_seq start with 1 nocache;
create sequence graph_data_seq start with 1 nocache;
Finally, the function
CREATE OR REPLACE function fill_graph_gaps
( p_sql_stmt varchar2, p_format varchar2 default 'I') return varchar2 is
-- Fill_graph_gaps
-- Created by Jerry Sidler 3/23/2017
-- All rights reserved
-- Free to use as long as this notice is maintained.
-------------------------------------------
-- Parameters
-- p_sql_stmt is the select you wish to graph It must contain 3 columns
-- Column 1 Series
-- Column 2 Labels
-- Column 3 a number item
--
-- p_format attempts to cleanup inconsistent label data
-- N - No cleanup
-- L - Lower case Labels
-- U - Upper case labels
-- I - Initcap labels
t_cnt number;
t_seq number;
begin
delete from graph_data where build_dt < sysdate-1;
execute immediate 'insert into tmp_graph_align '||p_sql_stmt;
if upper(p_format) <> 'N' then
update tmp_graph_align set label_col = trim(label_col),
series_col=trim(series_col);
if upper(p_format) = 'I' then
update tmp_graph_align
set label_col = initcap(label_col),
series_col = initcap(series_col);
elsif upper(p_format) = 'U' then
update tmp_graph_align
set label_col = upper(label_col),
series_col = upper(series_col);
elsif upper(p_format) = 'L' then
update tmp_graph_align
set label_col = lower(label_col),
series_col = lower(series_col);
end if;
end if;
for c1Rec in (select distinct label_col from tmp_graph_align ) loop
for c2Rec in (select distinct series_col from tmp_graph_align ) loop
select count(*) into t_cnt from tmp_graph_align where label_col = c1Rec.label_col and series_col = c2Rec.series_col;
if t_cnt = 0 then
insert into tmp_graph_align
values (c2Rec.series_col, c1Rec.label_col, 0);
end if;
end loop;
end loop;
select graph_data_seq.nextval into t_seq from dual;
insert into graph_data
( graph_data_id, s, l, v )
select t_seq, series_col, label_col, graph_value
from tmp_graph_align;
commit;
return t_seq;
end;
/
Notice I welcome comments and hope to present this at KScope 17. Send me comments/suggestions so when I present it will have many new features to make our lives easier!
Enjoy
Jerry
Or use this query:
ReplyDeleteselect gen.series
,gen.lbl
,coalesce (t.n, 0)
from (select series
,lbl
from (select distinct s series from temp)
,(select distinct l lbl from temp)) gen
left outer join temp t
on t.s = gen.series
and t.l = gen.lbl
order by gen.series, gen.lbl
Note: I did clean up your sample data (should have been constraint anyway; uppercase and trimmed :) ), but this could easily be done in the query as well .. see you at Kscope17
Delete