Thursday, 8 June 2017

Creating Charts In Oracle APEX Using Oracle JET Charts

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 being created in this post is based on Oracle JET Charts to reveal the amount 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 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 Automatic animation setting for the chart, which applies the Oracle JET’s default animation settings. It specifies whether 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 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 a 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 on 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 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 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.



The instruction provided in this post are extracted from the book Oracle Application Express 5.1 Basics & Beyond



No comments:

Post a Comment