Transcript Meaningful Maps, Gantts and Charts

Meaningful Maps, Great Gantts
and Charts to Knock your Sox Off
Karen Cannell
[email protected]
TH Technology
[email protected]
http://www.thtechnology.com
TH Technology
About Me …
Karen Cannell ~ Consultant, TH Technology
Analyzed, designed developed, converted, upgraded,
enhanced and otherwise worked on database
applications for 25+ years, focused on Oracle since
1994.
Building APEX applications for government, medical,
engineering industries.
Leveraging the Oracle 10g & 11g suite of tools. Lately
APEX.
Beginning Application Express, Apress, 2011
Editor, ODTUG Technical Journal
Volunteer to author ODTUG Journal Articles!
Using APEX since the HTMLDB beginning
APEX Maps, Gantts, Charts
TH Technology
About You … (Audience Background)
New to APEX?
APEX Experience?
Previous Tools?
APEX Training?
Version 2.0? 3.1 ? 3.2 ? 4.0?
APEX Maps, Gantts, Charts
TH Technology
Agenda
Meaningful Maps
Great Gantt Charts
Charts to Knock your Sox Off
APEX Maps, Gantts, Charts
TH Technology
APEX 4.0 Maps
AnyChart AnyMap Interactive Maps
Flash-based
300 Different Maps
Tree View for Map Selection
Option of Multiple Chart Series Queries
APEX Maps, Gantts, Charts
Meaningful Maps
TH Technology
[email protected]
TH Technology
APEX 4.0 Maps
AnyChart 5.1
Need Address or Location Data
Map Wizards
Choose from Map Catalog
Can Add Custom Maps
APEX Maps, Gantts, Charts
TH Technology
Maps
Make it Meaningful
Visually Say What Data Alone Cannot
… or Don’t
APEX Maps, Gantts, Charts
TH Technology
Create Map
APEX Maps, Gantts, Charts
TH Technology
Queries for Maps
•
•
SELECT link, label, value
FROM …
link – URL
label – Point on map to associate data with
The Region Name or Region Id of the map
•
value = Numeric column that defines the data
APEX Maps, Gantts, Charts
TH Technology
Query for Map
Example:
SELECT null link,
region label,
population value
FROM
(SELECT 'Florida' region, 18328340 population FROM
dual UNION ALL
SELECT 'Alaska' region, 686293 population FROM
dual)
Follow the Link-Label-Value Map Query Format
APEX Maps, Gantts, Charts
TH Technology
Map Catalog
APEX Maps, Gantts, Charts
TH Technology
Map Region Information
APEX Maps, Gantts, Charts
TH Technology
Referencing Geographical Data
If referencing geographical data from a database
table, the data must correspond with the Region
information of the map source.
See the AnyChart User’s Guide:
http://anychart.com/products/anymap/docs/
APEX Maps, Gantts, Charts
TH Technology
Query for Map (Series) – Critter Distrib.
SELECT null,
agency state,
SUM(yr_landed_lbs) yr
FROM ( SELECT
agency,
area_code,
year,
MAX ( CASE WHEN miy = '01' THEN sum_landed_lbs ELSE NULL END) jan_landed_lbs,
MAX ( CASE WHEN miy = '02' THEN sum_landed_lbs ELSE NULL END) feb_landed_lbs,
MAX ( CASE WHEN miy = '03' THEN sum_landed_lbs ELSE NULL END) mar_landed_lbs,
MAX ( CASE WHEN miy = '04' THEN sum_landed_lbs ELSE NULL END) apr_landed_lbs,
MAX ( CASE WHEN miy = '05' THEN sum_landed_lbs ELSE NULL END) may_landed_lbs,
MAX ( CASE WHEN miy = '06' THEN sum_landed_lbs ELSE NULL END) jun_landed_lbs,
MAX ( CASE WHEN miy = '07' THEN sum_landed_lbs ELSE NULL END) jul_landed_lbs,
MAX ( CASE WHEN miy = '08' THEN sum_landed_lbs ELSE NULL END) aug_landed_lbs,
MAX ( CASE WHEN miy = '09' THEN sum_landed_lbs ELSE NULL END) sep_landed_lbs,
MAX ( CASE WHEN miy = '10' THEN sum_landed_lbs ELSE NULL END) oct_landed_lbs,
MAX ( CASE WHEN miy = '11' THEN sum_landed_lbs ELSE NULL END) nov_landed_lbs,
MAX ( CASE WHEN miy = '12' THEN sum_landed_lbs ELSE NULL END) dec_landed_lbs,
MAX ( CASE WHEN miy = '00' THEN sum_landed_lbs ELSE NULL END) unk_landed_lbs,
MAX( sum_landed_lbs) yr_landed_lbs
FROM
(SELECT
partner.partner_name agency,
dep_landing.area_code,
time_period.year,
DECODE( time_period.month_in_year,'00','99', time_period.month_in_year) miy,
time_period.month,
SUM( dep_landing.landed_pounds) sum_landed_lbs
FROM
partner,
time_period,
dep_landing
WHERE
APEX Maps, Gantts, Charts
TH Technology
Northeast Critter Distribution
APEX Maps, Gantts, Charts
TH Technology
Map Options
Map Query
Series
Display Settings
Color Scheme
Legend
Font
XML
Refresh
APEX Maps, Gantts, Charts
TH Technology
Map Region Source
The
AnyChart
Source
FYI –
Usually
Don’t
Touch
APEX Maps, Gantts, Charts
TH Technology
Map Attributes – Map Settings
APEX Maps, Gantts, Charts
TH Technology
Map Attributes – Display Settings
APEX Maps, Gantts, Charts
TH Technology
Map Attributes – Color Scheme
APEX Maps, Gantts, Charts
TH Technology
Map Attributes – Legend Settings
APEX Maps, Gantts, Charts
TH Technology
Map Attributes – XML and Refresh
APEX Maps, Gantts, Charts
TH Technology
Map Attributes – Custom XML
Use Custom XML  Yes
Edit the XML
See Online Map XML Reference at
http://www.anychart.com/products/anymap/docs
APEX Maps, Gantts, Charts
TH Technology
AnyChart XML Reference map_series
APEX Maps, Gantts, Charts
TH Technology
Asynchronous Updates
Map Updates to Reflect Changes in Data
Refresh  Yes
APEX Maps, Gantts, Charts
TH Technology
Map Examples/Demo
Sample
Critters
APEX Maps, Gantts, Charts
TH Technology
Custom Maps
Can Make/Use Your Own Map
See AnyChart Documentation
Create Map … Convert to .amap
Must Follow Id, Name, Reference Conventions
Convertors to .amap Format
APEX Maps, Gantts, Charts
Great Gantts
TH Technology
[email protected]
TH Technology
Hold On! … Let’s go in order …
Gantt Charts – Normal APEX Chart Type
Resource - Personpower
Project - Tasks
APEX Maps, Gantts, Charts
Charts to Knock your Sox Off
TH Technology
[email protected]
TH Technology
APEX 4.0 Charts
AnyChart 5.1
Flash Chart Regions
Animations Built In
Interactive Reports Charts Updated
(Subset)
AnyChart 3 and SVG Charts Deprecated
Can Still Edit SVG
APEX Maps, Gantts, Charts
TH Technology
Chart Types
HTML
Flash
SVG
Edits Only
APEX Maps, Gantts, Charts
TH Technology
HTML Charts
Older
Not as
Flashy
No Flash
Player
Required
APEX Maps, Gantts, Charts
TH Technology
Flash Charts
AnyChart Flash Chart
http://www.anychart.com for more info
Animated
Interactive
Compact
Flash 9 or later Required
Flash Chart Types …
APEX Maps, Gantts, Charts
TH Technology
Supported Chart Types
Column
Horizontal Bar
Pie and Doughnut
Scatter
Line
Candlestick
Gauges
Gantt
Not All AnyChart Chart Types
APEX Maps, Gantts, Charts
TH Technology
Flash Chart - Upgrading
Flash 3 to Flash 5
Single
Tasks  Update to New Flash Chart
All
Utilities  Upgrade Application
Upgrade Flash Charts to Flash Chart 5
Option
APEX Maps, Gantts, Charts
TH Technology
SVG Charts
Created in Previous APEX/HTMLDB Releases
Still Supported
Edit
Deploy
Cannot Create New SVG Charts
Upgrade to Flash w/Restrictions
APEX Maps, Gantts, Charts
TH Technology
SVG Upgrade Restrictions
Only Number Formats in Axis – Dates/Time
Ignored
0,9,D,G, comma, period, $,C,L,M Formats
Label is from Column Alias (not Series Name)
Dial Charts show Actual Values (not %)
X-Axis Labels for all Series (not just 1st)
APEX Maps, Gantts, Charts
TH Technology
SVG Chart Stylesheets
SVG Charts use CSS of the Current Theme
text{font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;fill:#000000;} tspan{fontfamily:Verdana, Geneva, Arial, Helvetica, sans-serif;fill:#000000;} text.title{fontweight:bold;font-size:14;fill:#000000;} text.moredatafound{font-size:12;}
rect.legend{fill:#EEEEEE;stroke:#000000;stroke-width:1;}
text.legend{font-size:10;} #background{fill:#FFFFFF;stroke:none;}
rect.chartholderbackground{fill:#ffffff;stroke:#000000;stroke-width:1;}
#timestamp{text-anchor:start;font-size:9;}
text.tic{stroke:none;fill:#000000;font-size:12}
line.tic{stroke:#000000;stroke-width:1px;fill:none;}
#dial{stroke:#336699;stroke-width:2px;fill:#336699;fill-opacity:.5;}
#dial.alert{fill:#FF0000;fill-opacity:.5;} #dialbackground{stroke:#000000;strokewidth:none;fill:none;filter:url(#MyFilter);}
#dialcenter{stroke:none;fill:#111111;filter:url(#MyFilter);}
#dialbackground-border{stroke:#DDDDDD;stroke-width:2px;fill:none;filter:url (#MyFilter);}
#low{stroke-width:3;stroke:#336699;} #high{stroke-width:3;stroke:#FF0000;}
#XAxisTitle{letter-spacing:2;kerning:auto;font-size:14;fill:#000000;text-anchor:middle;}
#YAxisTitle{letter-spacing:2;kerning:auto;font-size:14;fill:#000000;text-anchor:middle;writingmode:tb;}
.XAxisValue{font-size:8;fill:#000000;}
.YAxisValue{font-size:8;fill:#000000;text-anchor:end;}
.nodatafound{stroke:#000000;stroke-width:1;font-size:12;}
.AxisLine{stroke:#000000;stroke-width:2;fill:#FFFFFF;}
.GridLine{stroke:#000000;stroke-width:0.3;stroke-dasharray:2,4;fill:none;}
g.dataholder rect{stroke:#000000;stroke-width:0.5;}
.legenditem rect{stroke:#000000;stroke-width:0.5;}
APEX Maps, Gantts, Charts
TH Technology
text{font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;fill:#000000;}
tspan{font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;fill:#000000;}
text.title{font-weight:bold;font-size:14;fill:#000000;}
text.moredatafound{font-size:12;}
rect.legend{fill:#EEEEEE;stroke:#000000;stroke-width:1;} text.legend{font-size:10;}
#background{fill:#FFFFFF;stroke:none;}
rect.chartholderbackground{fill:#ffffff;stroke:#000000;stroke-width:1;}
#timestamp{text-anchor:start;font-size:9;}
text.tic{stroke:none;fill:#000000;font-size:12}
line.tic{stroke:#000000;stroke-width:1px;fill:none;}
#dial{stroke:#336699;stroke-width:2px;fill:#336699;fill-opacity:.5;}
#dial.alert{fill:#FF0000;fill-opacity:.5;}
#dialbackground{stroke:#000000;stroke-width:none;fill:none;filter:url(#MyFilter);}
#dialcenter{stroke:none;fill:#111111;filter:url(#MyFilter);}
#dialbackground-border{stroke:#DDDDDD;stroke-width:2px;fill:none;filter:url (#MyFilter);}
#low{stroke-width:3;stroke:#336699;} #high{stroke-width:3;stroke:#FF0000;}
#XAxisTitle{letter-spacing:2;kerning:auto;font-size:14;fill:#000000;text-anchor:middle;}
#YAxisTitle{letter-spacing:2;kerning:auto;font-size:14;fill:#000000;textanchor:middle;writing-mode:tb;}
.XAxisValue{font-size:8;fill:#000000;}
.YAxisValue{font-size:8;fill:#000000;text-anchor:end;}
.nodatafound{stroke:#000000;stroke-width:1;font-size:12;}
.AxisLine{stroke:#000000;stroke-width:2;fill:#FFFFFF;}
.GridLine{stroke:#000000;stroke-width:0.3;stroke-dasharray:2,4;fill:none;}
g.dataholder rect{stroke:#000000;stroke-width:0.5;}
.legenditem rect{stroke:#000000;stroke-width:0.5;}
APEX Maps, Gantts, Charts
TH Technology
Custom SVG Chart Stylesheets
Chart Attributes  CSS
Use Custom  Yes
Custom CSS, Link:
#IMAGE_PREFIX#themes/theme_4/svg.css
Custom CSS, Inline:
/**/ - Use Custom CSS for All
List CSS Styles – Entered Styles Apply
APEX Maps, Gantts, Charts
TH Technology
HTML Chart
APEX Maps, Gantts, Charts
TH Technology
HTML Chart
Horizontal
APEX Maps, Gantts, Charts
TH Technology
HTML Chart – Chart Attributes
That’s
it!
APEX Maps, Gantts, Charts
TH Technology
HTML Chart
Vertical
APEX Maps, Gantts, Charts
TH Technology
HTML Chart
Quick HTML Chart Demo
• Landings A: 10, 701
APEX Maps, Gantts, Charts
TH Technology
Flash Charts
APEX Maps, Gantts, Charts
TH Technology
Vertical Bar, Horizontal Bar,
Doughnut
APEX Maps, Gantts, Charts
TH Technology
Line, Scatter, Candlestick
APEX Maps, Gantts, Charts
TH Technology
Gantt Charts, Gauges, Maps
APEX Maps, Gantts, Charts
TH Technology
Create Flash Charts
Create Page and Create Region Wizards
Chart Gallery
Several Sub-Types for Each Chart Type
Not All AnyChart Options!
More Chart Attributes - Less XML Customization
Chart Settings
Chart Series
Display Settings
Axis Settings
Legend Settings
APEX Maps, Gantts, Charts
TH Technology
Chart Settings
Chart Type
Chart Title
Width, Height
Margins
Color Scheme
Hatch Pattern
Custom Colors
APEX Maps, Gantts, Charts
TH Technology
Chart Display Settings
Animation
Marker
Style
Background
Includes
Scrollbars
Label, Values
Rotation
APEX Maps, Gantts, Charts
TH Technology
Query for Flash Charts
•
•
•
SELECT link, label, value
FROM …
link – URL
label – Text that Displays in the bar/slice
value = Numeric column that defines the
bar/slice size
…with Slight Variations for Chart Type
APEX Maps, Gantts, Charts
TH Technology
Query for Charts - Example
SELECT null,
last_name,
salary
FROM employees
WHERE DEPARTMENT_ID = :P101_DEPARTMENT_ID
APEX Maps, Gantts, Charts
TH Technology
Query for Charts – Real Example
APEX Maps, Gantts, Charts
TH Technology
3D Stacked Bar – As Created
APEX Maps, Gantts, Charts
TH Technology
3D Stacked Bar - Edited
APEX Maps, Gantts, Charts
TH Technology
Dial Chart Syntax
SELECT value,
maximum_value,
[ ,low_value [ ,high_value] ]
FROM ...
value is the starting point on the dial.
maximum_value is the possible highest point on the
dial.
low_value and high_value are the historical low
and high values.
APEX Maps, Gantts, Charts
TH Technology
Multiple Series Syntax (Flash Only)
Series Names are Column Aliases
Can do Multi Series at Once:
SELECT link,
label,
series_1_value [,
series_2_value [, ...]]
FROM ...
APEX Maps, Gantts, Charts
TH Technology
Multiple Series Chart
APEX Maps, Gantts, Charts
TH Technology
Range Chart Syntax
Requires Two Values for Each Bar
SELECT link,
label,
low_value,
high_value
FROM ...
APEX Maps, Gantts, Charts
TH Technology
Range Chart
LowHigh
Values
APEX Maps, Gantts, Charts
TH Technology
Scatter Chart Syntax
Requires X-Value and Y-Value for each Point
SELECT link,
label,
x_value,
y_value
FROM ...
APEX Maps, Gantts, Charts
TH Technology
Scatter Chart
X-Y
Plotting
APEX Maps, Gantts, Charts
TH Technology
Candlestick Chart Syntax
Requires Open, Low, High and Close Values
SELECT link,
label,
open,
low,
high,
close
FROM ...
APEX Maps, Gantts, Charts
TH Technology
Candlestick Chart - Example
Financial
Ex: Stock
Open/Close
for given
day
APEX Maps, Gantts, Charts
Great Gantts
TH Technology
[email protected]
TH Technology
Gantt Query Syntax == Important!
Error Message incorrect in APEX 4.0,
Follow Syntax in Create Wizard, User Guide
SELECT 'f?p=4000:2:'||:APP_SESSION||':::P2_ID:'||ID
LINK
RESOURCE_ID ID,
RESOURCE_NAME NAME,
PARENT_ID PARENT_ID,
START_DATE ACTUAL_START,
END_DATE ACTUAL_END
FROM TASKS
APEX Maps, Gantts, Charts
TH Technology
Project Gantt Query – Syntax
SELECT NULL LINK,
TASK_NAME NAME,
TASK_ID ID,
PARENT_ID PARENT_ID,
START_DATE ACTUAL_START,
END_DATE ACTUAL_END,
NULL STATUS
FROM
gantt_table
APEX Maps, Gantts, Charts
TH Technology
Project Gantt Chart Syntax
Require Task Name, Task Id, Parent Task Id,
Actual Start Date, Actual End Date, Progress
SELECT link,
task_name,
task_id, parent_id,
actual_start,
actual_end,
status
FROM ...
Column Aliases Must Match the Examples!
APEX Maps, Gantts, Charts
TH Technology
Project Gantt - Example
SELECT NULL LINK,
TASK_NAME NAME,
TASK_ID ID,
PARENT_ID PARENT_ID,
START_DATE ACTUAL_START,
END_DATE ACTUAL_END,
NULL STATUS
FROM
gantt_test
APEX Maps, Gantts, Charts
TH Technology
Project Gantt – Real Example
SELECT NULL LINK,
aa.assessment_name NAME,
aa.assessment_id ID,
NULL PARENT_ID,
aa.date_started ACTUAL_START,
aa.date_completed ACTUAL_END,
100 STATUS
FROM
assessment aa
APEX Maps, Gantts, Charts
TH Technology
Resource Gantt Chart Syntax
Requires Resource Id, Resource Name, Parent
Resource Id, Actual Start Date, Actual End
Date
SELECT link,
resource_id,
resource_name name,
resource_parent_id parent_id,
actual_start_date actual_start,
actual_end_date actual_end,
status
FROM ...
Column Aliases Must Match the Examples!
APEX Maps, Gantts, Charts
TH Technology
Resource Gantt Chart Example
Column Aliases Must Match the Examples!
APEX Maps, Gantts, Charts
TH Technology
Resource Gantt – Sample Query
select null link,
a.emp_id resource_id,
(select ename from emp where empno =
a.emp_id) name,
parent_id parent_id,
a.START_DATE actual_start,
a.END_DATE actual_end,
a.status
from gantt_info a
Column Aliases Must Match the Examples!
APEX Maps, Gantts, Charts
TH Technology
Resource Gantt - Example
APEX Maps, Gantts, Charts
TH Technology
Asynchronous Updates
SVG and Flash Charts
Chart Attributes  Asynchronous Update 
Yes
Timestamp Displays by Default
To Disable Timestamp
Use Custom CSS  Yes
Custom CSS, Inline
#timestamp{display:none}
APEX Maps, Gantts, Charts
TH Technology
Custom XML in Flash Charts
Chart  Chart XML
Use Custom XML  Yes
Edit As Needed
APEX Maps, Gantts, Charts
TH Technology
Charts in Interactive Reports
Limited Chart Types
H-Bar, V-Bar, Pie, Line
Limited Options
Animations - WYGIWYG
One Series Only
Less Control  Ease of Use for End
User
APEX Maps, Gantts, Charts
TH Technology
Interactive Report w/Chart
APEX Maps, Gantts, Charts
TH Technology
Wrap-up
Maps
Charts
Gantts
Make it Meaningful
Have Fun!
APEX Maps, Gantts, Charts
TH Technology
References
AnyChart Documentation
http://www.anychart.com/docs
APEX Blogs
APEX Documentation and Examples
http://apex.oracle.com
APEX Maps, Gantts, Charts
TH Technology
K’Scope 2011!!
APEX Maps, Gantts, Charts
TH Technology
Share Your Knowledge!
Call for Articles/Reviewers
ODTUG Technical Journal
ALWAYS Looking for Content!
[email protected]
APEX Maps, Gantts, Charts
Questions?
Maps, Gantts,
Charts …
http://tunahuntress.com/apex
http://www.thtechnology.com
/apex-papers-andpresentations
TH Technology
[email protected]
Thank You ~
Maps, Gantts,
Charts …
http://www.thtechnology.com/
apex-papers-andpresentations
TH Technology
[email protected]