APEX - Tips and Tricks

Great news I have been told APEX 5.1.3 will permanently resolve this graph issue.

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;

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

-- 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;

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;

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

The Apex classic report is a lot of wonderful features but a simple enhancement can have reports running faster. The problem comes in that classic report has a wild card search on selected columns. This can slow down the report if the user wants everything

A simple example
I have a classic report with 4 columns COL1 through COL4

The default sql generated is

select * from (

select rowid,

col1,

col2,

col3,

col4

from table_x

) where

instr(upper("COL1"),upper(nvl(:P25_REPORT_SEARCH,"COL1"))) > 0 or

instr(upper("COL2"),upper(nvl(:P25_REPORT_SEARCH,"COL2"))) > 0 or

instr(upper("COL3"),upper(nvl(:P25_REPORT_SEARCH,"COL3"))) > 0 or

instr(upper("COL4"),upper(nvl(:P25_REPORT_SEARCH,"COL4"))) > 0

)

The problem is this syntax requires Oracle to do string compares on every row for every column even if the user doesn't key anything in. The SOLUTION: simply add one line

or length(trim(nvl(:p25_report_search,''))) < 1

Yep paste that line at the end of the SQL and your reports will run faster. So the final SQL would look like

select * from (

select rowid,

col1,

col2,

col3,

col4

from table_x

) where

instr(upper("COL1"),upper(nvl(:P25_REPORT_SEARCH,"COL1"))) > 0 or

instr(upper("COL2"),upper(nvl(:P25_REPORT_SEARCH,"COL2"))) > 0 or

instr(upper("COL3"),upper(nvl(:P25_REPORT_SEARCH,"COL3"))) > 0 or

instr(upper("COL4"),upper(nvl(:P25_REPORT_SEARCH,"COL4"))) > 0

or length(trim(nvl(:p25_report_search,''))) < 1

)

So my 1st blog post... Go easy on me.

Let me know what you think :)

Hope to see everyone at my 2nd ODTUG KScope16 in Chicago!

Jerry

APEX - Tips and Tricks

Tuesday, January 23, 2018

Running apex (ORDS) on aws EC2 communicating with an RDS Database

Thursday, March 23, 2017

APEX 5.1 Series Line Graph opportunity and its simple fix!

Wednesday, June 1, 2016

APEX Classic Report Performance enhancement