creating sub zero dashboard plugin for apex with google

Creating Sub-zero Dashboard Plug-In for APEX with Google VisualizationsRoel HartmanOctober 2010

2

“Visualization is daydreaming with

a purpose”

- Bo Bennett

Intro

sub-zero ?

sub-zero !

© Logica 2010. All rights reserved

Who am I?

• Oracle since v5, Forms 2.3, Case*Designer etc

• Presenter at UKOUG (3x), OOW (3x), ODTUG (2x)

ROELHARTMAN.BLOGSPOT.COM

No. 6

item region dynamic

actionprocess

develop once

use often

A

No. 12© Logica 2010. All rights reserved

•Contains other cool charts

•Extend your options

• (Most) not Flash: http://apex.oracle.com/pls/apex/f?p=GVIS:STATIC

Why use Google Visualizations?

B

No. 13© Logica 2010. All rights reserved

What we are aiming for...

No. 14© Logica 2010. All rights reserved

End User

APEX Developer

Plug-in Developer

Three perspectives

Develop a

Plug-in

in 5 steps

CODE ALERT !!

© Logica 2010. All rights reserved

•3 parameters

function render_chart

( p_region in apex_plugin.t_region

, p_plugin in apex_plugin.t_plugin

, p_is_printer_friendly in boolean )

return apex_plugin.t_region_render_result

•Read the custom parameters l_width apex_application_page_regions.attribute_01%type := p_region.attribute_01;

•Switch on debug (apex_plugin_util.debug_region)

No. 18

Step 1 - Create a render function

C

No. 19

© Logica 2010. All rights reserved

•3 parameters

function render_chart

( p_region in apex_plugin.t_region

, p_plugin in apex_plugin.t_plugin

, p_is_printer_friendly in boolean )

return apex_plugin.t_region_render_result

•Read the custom parameters l_width apex_application_page_regions.attribute_01%type := p_region.attribute_01;

•Switch on debug (apex_plugin_util.debug_region)

•Render the HTML (javascript, css)

•Return null

type t_region_render_result is record

( dummy boolean /* not used yet */ );

No. 20

Step 1 - Create a render function

D

© Logica 2010. All rights reserved

•This is the ‘actual’ Plug-in code

•Render a placeholder DIV

No. 21

Step 1 – Render the HTML

sys.htp.p(‘ <input id="GVrowIndex'||p_region.static_id||'" type="hidden" size="10"/> <div id="GV'||p_region.static_id||'"></div>');

© Logica 2010. All rights reserved

•This is the ‘actual’ Plug-in code

•Render a placeholder DIV

•Add the Google jsapi library

No. 22

Step 1 – Render the HTML

apex_javascript.add_library ( p_name => 'jsapi', p_directory => 'http://www.google.com/', p_version => null, p_skip_extension => true );

© Logica 2010. All rights reserved

•This is the ‘actual’ Plug-in code

•Render a placeholder DIV

•Add the Google jsapi library

•Load the Google Visualization API

No. 23

Step 1 – Render the HTML

apex_javascript.add_inline_code ( p_code => 'google.load("visualization", "1", {packages:["'||c_chart_type||'"]});', p_key => 'visualization_'||c_chart_type );

© Logica 2010. All rights reserved

•This is the ‘actual’ Plug-in code

•Render a placeholder DIV

•Add the Google jsapi library

•Load the Google Visualization API

•Register your javascript library

No. 24

Step 1 – Render the HTML

apex_javascript.add_library ( p_name => 'com_logica_apex_googlevis_piechart', p_directory => p_plugin.file_prefix, p_version => null );

© Logica 2010. All rights reserved

•This is the ‘actual’ Plug-in code

•Render a placeholder DIV

•Add the Google jsapi library

•Load the Google Visualization API

•Register your javascript library

• Initialize the chart

No. 25

Step 1 – Render the HTML

sys.htp.p(‘<input id="GVrowIndex'||p_region.static_id||'" type="hidden" size="10"/><div id="GV'||p_region.static_id||'"></div>');

apex_javascript.add_library ( p_name => 'jsapi', p_directory => 'http://www.google.com/', p_version => null, p_skip_extension => true );

apex_javascript.add_inline_code ( p_code => 'google.load("visualization", "1", {packages:["'||c_chart_type||'"]});', p_key => 'visualization_'||c_chart_type );

apex_javascript.add_library ( p_name => 'com_logica_apex_googlevis_piechart', p_directory => p_plugin.file_prefix, p_version => null );

apex_javascript.add_onload_code ( p_code =>'GV'||p_region.id||' = '|| 'new com_logica_apex_googlevis_piechart();'|| 'GV'||p_region.id||'.render('|| apex_javascript.add_value(p_region.static_id)|| apex_javascript.add_value(c_chart_type)|| '{'|| -- add chart attributes apex_javascript.add_attribute('width' , l_width )|| apex_javascript.add_attribute('height' , l_height )|| apex_javascript.add_attribute('is3D' , l_is3D )|| '},'||l_data|| '); ' );

E

© Logica 2010. All rights reserved

•Execute the query

•apex_plugin_util.get_data2 returns t_column_value_list2

table of (name, data_type, valuelist)

No. 26

Step 1 – Getting the data

-- Column 1 is a string, column2 a number. l_data_type_list(1) := apex_plugin_util.c_data_type_varchar2; l_data_type_list(2) := apex_plugin_util.c_data_type_number; -- get the chart data l_col_val_list := apex_plugin_util.get_data2 ( p_sql_statement => p_region.source, p_min_columns => 2, p_max_columns => 2, p_data_type_list => l_data_type_list, p_component_name => p_region.static_id, p_max_rows => 1000 );

© Logica 2010. All rights reserved

•Execute the query

•apex_plugin_util.get_data2 returns t_column_value_list2

table of (name, data_type, valuelist)

•Google JSON Format

No. 27

Step 1 – Getting the data

{ cols: [{id: 'A', label: 'NEW A‘ , type: 'string'}, {id: 'B', label: 'B-label', type: 'number'}, {id: 'C', label: 'C-label', type: 'date‘ } ], rows: [{c:[{v: 'a'}, {v: 1.0 , f: 'One'} ,{v: new Date(2008, 1, 28)}]}, {c:[{v: 'b'}, {v: 2.0, f: 'Two'} ,{v: new Date(2008, 2, 30)}]}, {c:[{v: 'c'}, {v: 3.0, f: 'Three'},{v: new Date(2008, 3, 30)}]} ] }

F

© Logica 2010. All rights reserved

•Execute the query

•apex_plugin_util.get_data2 returns t_column_value_list2

table of (name, data_type, valuelist)

•Google JSON Format

•Constructing G-JSON

No. 28

Step 1 – Getting the data

loop l_data := l_data|| '{'|| apex_javascript.add_attribute('id' , 'col'||l_column)|| apex_javascript.add_attribute('label', l_col_val_list(l_column).name)|| apex_javascript.add_attribute('type' , case l_col_val_list(l_column).data_type when c_data_type_varchar2 then 'string‘ when c_data_type_number then 'number' end, false, false )|| '}'; end loop;

© Logica 2010. All rights reserved

loop l_data := l_data|| '{'|| apex_javascript.add_attribute('id' , 'col'||l_column)|| apex_javascript.add_attribute('label', l_col_val_list(l_column).name)|| apex_javascript.add_attribute('type' , case l_col_val_list(l_column).data_type when c_data_type_varchar2 then 'string‘ when c_data_type_number then 'number' end, false, false )|| '}'; end loop;

•Execute the query

•apex_plugin_util.get_data2 returns t_column_value_list2

table of (name, data_type, valuelist)

•Google JSON Format

•Constructing G-JSON

No. 29

Step 1 – Getting the data

G

© Logica 2010. All rights reserved

•Execute the query

•apex_plugin_util.get_data2 returns t_column_value_list2

table of (name, data_type, valuelist)

•Google JSON Format

•Constructing G-JSON

No. 30

Step 1 – Getting the data

loop l_data := l_data|| '{'|| apex_javascript.add_attribute('id' , 'col'||l_column)|| apex_javascript.add_attribute('label', l_col_val_list(l_column).name)|| apex_javascript.add_attribute('type' , case l_col_val_list(l_column).data_type when c_data_type_varchar2 then 'string‘ when c_data_type_number then 'number' end, false, false )|| '}'; end loop;

© Logica 2010. All rights reserved No. 31

Step 2 – Add standard attributes

H

© Logica 2010. All rights reserved No. 32

Step 3 – Add custom attributes

•Dependent on !

•Select List, but only Static

I

© Logica 2010. All rights reserved No. 33

Step 4 – Add files

apex_javascript.add_onload_code ( p_code =>'GV'||p_region.id||' = '|| 'new com_logica_apex_googlevis_piechart();'|| 'GV'||p_region.id||'.render('|| apex_javascript.add_value(p_region.static_id)|| apex_javascript.add_value(c_chart_type)|| '{'|| -- add chart attributes apex_javascript.add_attribute('width' , l_width )|| apex_javascript.add_attribute('height' , l_height )|| apex_javascript.add_attribute('is3D' , l_is3D )|| '},'||l_data|| '); ' );

J

© Logica 2010. All rights reserved

•Propagate events to APEX

No. 34

Step 5 – Add events

K

No. 35© Logica 2010. All rights reserved

APEX Developer

L

No. 36© Logica 2010. All rights reserved

End User

M

No. 37© Logica 2010. All rights reserved

Question Break

That was quite cool

now the sub-zero stuff!

No. 39© Logica 2010. All rights reserved

Combine the power of:•GV Plug-in

•Dynamic Action (Plug-ins)

•Google Visualizations

• Update (DA)

• Filter (DA)

Now the sub-zero stuff...

N

No. 40© Logica 2010. All rights reserved

Now the sub-zero stuff...

No. 41© Logica 2010. All rights reserved

•Plug-ins are sooo powerful

•Really extend APEX

•Need (some) R&D

•Get to know your building blocks

•Good Plug-ins are easy to use

•And fun too!

•Amaze your end users....

Summary and Conclusions

Logica is a business and technology service company, employing 39,000 people. It provides business consulting, systems integration and outsourcing to clients around the world, including many of Europe's largest businesses. Logica creates value for clients by successfully integrating people, business and technology. It is committed to long term collaboration, applying insight to create innovative answers to clients’ business needs. Logica is listed on both the London Stock Exchange and Euronext (Amsterdam) (LSE: LOG; Euronext: LOG). More information is available at www.logica.com

Thank youRoel Hartman

Logica | Meander 901 P.O. Box 7015 | 6801 HA Arnhem | The Netherlands | www.logica.comContact: Roel Hartman - Lead Technical Architect Oracle: +31 (0) 26 3765 000 M: +31 (0) 6 2954 3729 E: roel.hartman@logica.com