Presenting data in Oracle APEX, either graphically or in text format, is as easy as creating the input forms. The built-in wizards make it so easy to create flexible and powerful charts in Oracle Application Express. In this post, you will learn how to create a bar chart using demo_customers, demo_orders, demo_order_items, and demo_product_info tables that come with Oracle APEX.
The chart created in this post is based on Oracle JET Charts to reveal the number of orders placed by each customer by product categories. Oracle JET Charts is a new feature incorporated in Oracle APEX 5.1. Charts in Oracle APEX have been completely revamped. Now Oracle APEX has integrated charting based on Oracle JavaScript Extension Toolkit (JET) charting library. Oracle JET Charts is a component of the Oracle JavaScript Extension Toolkit (JET), an open-source toolkit based on modern JavaScript, CSS3, and HTML5 design and development principles. These charts are fully HTML5 capable and work on any modern browser regardless of platform, screen size, or features.
Each bar in the chart has multiple slices representing amounts of different orders. When you move your mouse over these slices a tooltip (A) displays the corresponding amount. The chart will be created with drill-down functionality. That is, when you click a bar, you'll be taken to Page 7 where you will see the profile of the selected customer. You will also make provision to change the chart’s orientation (B) and will provide options to present it as either stacked or un-stacked (C).
1.
Create
a Blank Page and set the following properties
for it:
|
Property
|
Value
|
|
Page
Number
|
17
|
|
Name
|
Customer Orders
|
|
Page
Mode
|
Normal
|
|
Breadcrumb
|
don’t use breadcrumb on
page
|
|
Navigation
Preference
|
Identify an existing navigation menu entry for this page
|
|
Existing
Navigation Menu Entry
|
Reports
|
2. Right-click
the Content Body folder and select Create Region. Set the following properties
for the new region. Immediately after switching the region’s Type, a new folder named Series along with a child node (New) is added under the region. Each
product in this app is associated with one of the three categories:
Accessories, Men, and Women. The query below fetches summarized order figures
by customers for each category.
|
Property
|
Value
|
|
Title
|
Customer Orders
|
|
Type
|
Chart
|
|
SQL
Query
|
select c.customer_id, c.cust_last_name||', '||c.cust_first_name
Customer_Name,
sum
(decode(p.category,'Accessories',oi.quantity * oi.unit_price,0))
"Accessories",
sum (decode(p.category,'Mens',oi.quantity
* oi.unit_price,0)) "Men",
sum (decode(p.category,'Womens',oi.quantity
* oi.unit_price,0)) "Women"
from demo_customers c, demo_orders o,
demo_order_items oi, demo_product_info p
where c.customer_id = o.customer_id and o.order_id
= oi.order_id and
oi.product_id = p.product_id
group by c.customer_id, c.cust_last_name,
c.cust_first_name
order by c.cust_last_name
|
3.
Click
the Attributes folder under the Customer Orders chart region and set the
following properties. The Stack
property specifies whether the data items are stacked. We defined the Automatic animation setting for the
chart, which applies Oracle JET’s default animation settings. It specifies
whether an animation is shown when data is changed on the chart. A data change can
occur if the chart gets automatically refreshed. In the current scenario, the
animation takes place when you click one of the four buttons (B & C):
Horizontal, Vertical, Stack, or Unstack. These buttons will be created in
subsequent steps. The Hide and Show
Behavior is performed when you click a legend item (G). For example,
deselecting a legend item will hide its associated data series on the chart.
With the value set to Rescale for
this property, the chart rescales as you select or de-select a legend. This is
useful for series with largely varying values.
|
Property
|
Value
|
|
Type
|
Bar
|
|
Title
|
Leave
it blank
|
|
Orientation
|
Vertical
|
|
Stack
|
Yes
|
|
Maximum
Width
|
800
|
|
Height
|
500
|
|
On
Data Change (under Animation)
|
Automatic
|
|
Position
(under
Legend)
|
Top
|
|
Hide
and Show Behavior
|
Rescale
|
4.
Click
the New node (under Series) and set the following properties.
Each series you create for your chart appears in a unique color to represent the product category and displays sales figures for each category (using the Value property) that is derived from the
SELECT statement specified in step 2. You set Source Type (on row 2) to Region
Source, which specifies that the data of this series is to be extracted
from the SQL query defined for the Customer
Orders region (in step 2). In the Label
attribute, you select a column name that is used for defining the label(s) of
the x-axis (D) on the chart, while the Accessories column selected for the Value property is used for defining the ordered
value (E) on this chart. When you click a chart bar
(representing Accessories), you’re
drilled down to Page 7 to browse customer details.
|
Property
|
Value
|
|
Name
|
Accessories
|
|
Type
(under
Source)
|
Region Source
|
|
Label
(under
Column Mapping)
|
CUSTOMER_NAME
|
|
Value
|
Accessories
|
|
Type
(under
Link)
|
Redirect to Page in this Application
|
|
Target
|
Type = Page in this Application
Page = 7
Name = P7_CUSTOMER_ID
Value = &CUSTOMER_ID.
Clear Cache = 7
|
5. Right-click
the Series folder and select Create Series from the context menu to
add another series. Set the following properties for the new series. Use the
same values as defined for the Target
property in Step 4 to transform this series into a link to access Page 7.
|
Property
|
Value
|
|
Name
|
Men
|
|
Type
(under
Source)
|
Region Source
|
|
Label
(under
Column Mapping)
|
CUSTOMER_NAME
|
|
Value
|
Men
|
6. Create
one more series and set the following properties. Create a link as you did for
the previous two steps.
|
Property
|
Value
|
|
Name
|
Women
|
|
Type
(under
Source)
|
Region Source
|
|
Label
(under
Column Mapping)
|
CUSTOMER_NAME
|
|
Value
|
Women
|
7. Click
the x-axis node (under Axes) and
enter Customers for the Title attribute. The title will appear
at the bottom of the chart (F).
8. Click
the y-axis node and set the following properties. When you format a number as
currency, the Currency property is
required to be set to specify the currency that will be used when formatting
the number. You enter a currency that is used when formatting the value on the
chart. The value should be an ISO 4217 alphabetic currency code. If the format
type is set to Currency, it is required that the Currency property also be specified. Visit http://www.xe.com/iso4217.php to
see a list of standard currency codes.
|
Property
|
Value
|
|
Title
|
Order Total
|
|
Format
Type
|
Currency
|
|
Decimal
Places
|
0
|
|
Currency
|
USD
|
9. In
this step, you will add two buttons (B) to the Customer Orders region. When clicked, these buttons will change the
chart’s orientation using the default animation set in step 3. Right-click the Customer Orders region and select Create Button from the context menu. A
new folder named Region Buttons will
be added with a button labeled New.
Set the following properties for this button. The Action attribute says that this button is associated with a dynamic
action (step 10), which fires when the button is clicked.
|
Property
|
Value
|
|
Button
Name
|
Horizontal
|
|
Label
|
Horizontal
|
|
Button
Position
|
Previous
|
|
Button
Template
|
Icon
|
|
Icon
CSS Classes
|
fa-bars
|
|
Action
|
Defined by Dynamic Action
|
Right-click Region
Button and select Create Button
to add a new button. Set the following properties for the new button.
|
Property
|
Value
|
|
Button
Name
|
Vertical
|
|
Label
|
Vertical
|
|
Button
Position
|
Previous
|
|
Button
Template
|
Icon
|
|
Icon
CSS Classes
|
fa-bar-chart
|
|
Action
|
Defined by Dynamic Action
|
10. Now add two dynamic actions for the two buttons. Click the Dynamic Actions tab, right-click the Click folder, and select Create Dynamic Action. Click the New node and set the following properties.
This dynamic action is named Horizontal
Orientation – you are free to give it any other name you deem suitable. The
next three properties specify that this dynamic action should trigger when the Horizontal button is clicked.
|
Property
|
Value
|
|
Name
|
Horizontal Orientation
|
|
Event
|
Click
|
|
Selection
Type
|
Button
|
|
Button
|
Horizontal
|
Click the Show
node under the True folder to set the
following properties. When the Horizontal
button is clicked, the JavaScript code (defined in row 2) is fired. In this
code, dualChart is a static ID you
will set in step 12 for the Customer
Orders region. You control the chart’s orientation through the ojChart class, which has two options (Horizontal and Vertical), where Vertical is the default option. In this step, you inform the Oracle APEX
engine to display the chart horizontally when the Horizontal button is clicked. Note that the chart orientation only
applies to bar, line, area, combo, and funnel charts.
|
Property
|
Value
|
|
Action
|
Execute JavaScript Code
|
|
Code
|
$("#dualChart_jet").ojChart({orientation: 'horizontal'});
|
|
Selection
Type
|
Region
|
|
Region
|
Customer Orders
|
|
Event
|
Horizontal Orientation
|
|
Fire
on Initialization
|
No
|
11. Right-click the Click folder
and select Create Dynamic Action to
add one more for vertical orientation, as follows. Click the New node and set the following properties:
|
Property
|
Value
|
|
Name
|
Vertical Orientation
|
|
Event
|
Click
|
|
Selection
Type
|
Button
|
|
Button
|
Vertical
|
Click the Show
node under the True folder and set
the following properties:
|
Property
|
Value
|
|
Action
|
Execute JavaScript Code
|
|
Code
|
$("#dualChart_jet").ojChart({orientation: 'vertical'});
|
|
Selection
Type
|
Region
|
|
Region
|
Customer Orders
|
|
Event
|
Vertical Orientation
|
|
Fire
on Initialization
|
No
|
12. If you run the pages at this stage, you will not see the orientation
effect if you click any of the two buttons. This is because of the static ID (dualChart), which is mentioned in the
JavaScript code to reference the Customer
Orders region but has not been assigned to the region itself. Switch back
to the Rendering tab, click the Customer Orders region, and in the Advanced section enter dualChart as the value for the Static ID property. Now the region can
be recognized by this static ID.
13. Add two more buttons to Region
Buttons. These buttons will be used to render the series data as stacked or
unstacked (C). Set the following properties for the two buttons:
|
Property
|
Value (Button1)
|
Value (Button2)
|
|
Button
Name
|
Stack
|
Unstack
|
|
Label
|
Stack
|
Unstack
|
|
Button
Position
|
Next
|
Next
|
14. Create two dynamic actions for the two buttons as follows. Set the New nodes’ properties as defined in the
first table below:
|
Property
|
Value (New node)
|
Value (New node)
|
|
Name
|
Stack Chart
|
Unstack Chart
|
|
Event
|
Click
|
Click
|
|
Selection
Type
|
Button
|
Button
|
|
Button
|
Stack
|
Unstack
|
The Stack
Chart dynamic event’s Show node properties:
|
Property
|
Value
|
|
Action
|
Execute JavaScript Code
|
|
Code
|
$("#dualChart_jet").ojChart({stack: 'on'});
|
|
Selection
Type
|
Region
|
|
Region
|
Customer Orders
|
|
Event
|
Stack Chart
|
|
Fire
on Initialization
|
No
|
The Unstack
Chart dynamic event’s Show node properties:
|
Property
|
Value
|
|
Action
|
Execute JavaScript Code
|
|
Code
|
$("#dualChart_jet").ojChart({stack: 'off'});
|
|
Selection
Type
|
Region
|
|
Region
|
Customer Orders
|
|
Event
|
Unstack Chart
|
|
Fire
on Initialization
|
No
|
Save your work. Run the application and click Reports in the navigation menu. You will
see the Reports page created in
section 8.2. Click the first Customer
Orders card to access Page 17. You will see a chart, as shown in Figure 8-2.
Move your cursor over the chart bars and different portions within a particular
bar. You will see a tooltip (A) showing the order amount of the corresponding
customer. Click the Vertical and Horizontal buttons (B) to change the chart’s
orientation. Similarly, click the Stack
and Unstack buttons (C) to see respective
animated effects.
Also see:
