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

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

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


What we are aiming for...


End User

APEX Developer

Plug-in Developer

Three perspectives

Develop a

Plug-in

in 5 steps

CODE ALERT !!


•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


•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


•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>');




•Add the Google jsapi library

No. 22


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





•Load the Google Visualization API

No. 23


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






•Register your javascript library

No. 24


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






•Register your javascript library

• Initialize the chart

No. 25


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


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





•Google JSON Format

No. 27


{ 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






•Constructing G-JSON

No. 28


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;








No. 29


G







No. 30




Step 3 – Add custom attributes

•Dependent on !

•Select List, but only Static

I


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


•Propagate events to APEX

No. 34

Step 5 – Add events

K


APEX Developer

L


End User

M


Question Break

That was quite cool

now the sub-zero stuff!


Combine the power of:•GV Plug-in

•Dynamic Action (Plug-ins)

•Google Visualizations

• Update (DA)

• Filter (DA)

Now the sub-zero stuff...

N


Now the sub-zero stuff...


•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: [email protected]

creating sub zero dashboard plugin for apex with google

Technology

code p

library p

chart p

data2 p

chart data

render function c

null type t

debug apex