| Oracle® HTML DB 2 Day Developer Release 1.6 Part Number B14377-01 |
|
|
View PDF |
| Oracle® HTML DB 2 Day Developer Release 1.6 Part Number B14377-01 |
|
|
View PDF |
A stacked bar chart displays the results of multiple queries stacked on top of one another (either vertically or horizontally). Using a stacked bar chart is an effective way to communicate the absolute values of data points represented by the segments of each bar, as well as the total value represented by data points from each series stacked in a bar.
In Oracle HTML DB, stacked bar chart is only available as an SVG chart. To create a stacked bar chart, you can create the chart as a stacked bar chart, or you can create a regular (non-HTML) bar chart and then add queries to it.
This tutorial describes how to create a stacked bar chart using existing data within the demonstration application, Sample Application.
This section contains the following topics:
To access Sample Application:
Log in to Oracle HTML DB.
From the Workspace Administration list, select Review Demonstration Applications.
Locate Sample Application and check the Status column:
If the Status column displays Installed, click Run in the Action column.
If the Status column displays Not Installed, select Install in the Action column. When the Application Builder home page appears, click the Run icon.
When prompted, enter the appropriate username and password and click Login
For User Name, enter either demo or admin.
For Password, enter the name of the current workspace using all lowercase letters.
To create the initial report, you can either add a region to an existing page and define it as a stacked bar chart, or you can create a new page. In this exercise, you create a new page within the Sample Application.
The chart will display the sum by product category for sales from within the Sample Application. It will contain sales for the twelve months prior to the current month. In the following exercise, you will create four queries (called series) for each of the product categories (phones, computers, audio, and video).
To create a new page:
Navigate to the Workspace home page.
From the Applications list, select Sample Application.
Click Create Page.
Select Page with Component and click Next.
On Select Component Type, select Chart and click Next.
Select Stacked Bar and click Next.
For Identify Page Attributes:
For Page, enter 750.
For Page Name, enter Revenue by Category.
For Region Name, enter Revenue by Category.
Click Next.
For Tab Options, accept the default Do not use Tabs and click Next.
On Series Query:
For Series Name, enter Phones.
In SQL, enter:
SELECT NULL l,
sales_month,
revenue
FROM (
SELECT TO_CHAR(o.order_timestamp,'Mon YYYY') sales_month,
SUM(oi.quantity * oi.unit_price) revenue,
TO_DATE(to_char(o.order_timestamp,'Mon YYYY'),'Mon YYYY') sales_month_order
FROM DEMO_PRODUCT_INFO p,
DEMO_ORDER_ITEMS oi,
DEMO_ORDERS o
WHERE o.order_timestamp <= (trunc(sysdate,'MON')-1)
AND o.order_timestamp > (trunc(sysdate-365,'MON'))
AND o.order_id = oi.order_id
AND oi.product_id = p.product_id
AND p.category = 'Phones'
GROUP BY TO_CHAR(o.order_timestamp,'Mon YYYY')
ORDER BY sales_month_order
)
The syntax for the select of a chart is SELECT link, label, value. You must have all three and only three items in your select. Because there is no appropriate page to link to, the link is defined as null.
You cannot have an order by in the select statement for series in a stacked chart. The information will be displayed in alphabetical order and this does not work for dates (October displays before September). To get the data to display in chronological order, you need to order the data inside a nested select.
For When No Data Found Message, enter:
No orders found in the past 12 months.
Click Next.
Review your selections and click Finish.
Once you have created the new page with a region defining the query, you need to add the series. In the following exercise, you will add a series for each of the categories of product in the application (that is, computers, audio, and video). Note you have already created the phones category.
To add a series for computers category:
On the Success Page, select Edit Page.
The Page Definition for page 750 appears.
Under Regions, select Chart to the left of Revenue by Category.
Under Chart Series, notice that only one series appears.
Add a chart series for Computer. Under Chart Series, click Add Series.
For Series Name, enter Computer.
In SQL, enter:
SELECT NULL l,
sales_month,
revenue
FROM (
SELECT TO_CHAR(o.order_timestamp,'Mon YYYY') sales_month,
SUM(oi.quantity * oi.unit_price) revenue,
TO_DATE(to_char(o.order_timestamp,'Mon YYYY'),'Mon YYYY') sales_month_order
FROM DEMO_PRODUCT_INFO p,
DEMO_ORDER_ITEMS oi,
DEMO_ORDERS o
WHERE o.order_timestamp <= (trunc(sysdate,'MON')-1)
AND o.order_timestamp > (trunc(sysdate-365,'MON'))
AND o.order_id = oi.order_id
AND oi.product_id = p.product_id
AND p.category = 'Computer'
GROUP BY TO_CHAR(o.order_timestamp,'Mon YYYY')
ORDER BY sales_month_order
)
Note that this SQL matches the previous series. The only difference is that the category in the WHERE clause.
For When No Data Found Message, enter:
No orders found in the past 12 months.
Click Apply Changes.
Add a chart series for Audio. Under Chart Series, click Add Series.
For Series Name, enter Audio.
In SQL, enter:
SELECT NULL l,
sales_month,
revenue
FROM (
SELECT TO_CHAR(o.order_timestamp,'Mon YYYY') sales_month,
SUM(oi.quantity * oi.unit_price) revenue,
TO_DATE(to_char(o.order_timestamp,'Mon YYYY'),'Mon YYYY') sales_month_order
FROM DEMO_PRODUCT_INFO p,
DEMO_ORDER_ITEMS oi,
DEMO_ORDERS o
WHERE o.order_timestamp <= (trunc(sysdate,'MON')-1)
AND o.order_timestamp > (trunc(sysdate-365,'MON'))
AND o.order_id = oi.order_id
AND oi.product_id = p.product_id
AND p.category = 'Audio'
GROUP BY TO_CHAR(o.order_timestamp,'Mon YYYY')
ORDER BY sales_month_order
)
For When No Data Found Message, enter:
No orders found in the past 12 months.
Click Apply Changes.
Add a chart series for Video. Under Chart Series, click Add Series.
For Series Name, enter Video.
In SQL, enter:
SELECT NULL l,
sales_month,
revenue
FROM (
SELECT TO_CHAR(o.order_timestamp,'Mon YYYY') sales_month,
SUM(oi.quantity * oi.unit_price) revenue,
TO_DATE(to_char(o.order_timestamp,'Mon YYYY'),'Mon YYYY') sales_month_order
FROM DEMO_PRODUCT_INFO p,
DEMO_ORDER_ITEMS oi,
DEMO_ORDERS o
WHERE o.order_timestamp <= (trunc(sysdate,'MON')-1)
AND o.order_timestamp > (trunc(sysdate-365,'MON'))
AND o.order_id = oi.order_id
AND oi.product_id = p.product_id
AND p.category = 'Video'
GROUP BY TO_CHAR(o.order_timestamp,'Mon YYYY')
ORDER BY sales_month_order
)
For When No Data Found Message, enter:
No orders found in the past 12 months.
Click Apply Changes.
Next, you will enhance the appearance of the chart with axis titles by adding a region footer.
To format the Y axis:
Scroll down to Axes Settings.
In Y Axis Format, enter:
FML999G999G999G999G990
This formats the sales_month column as money, but without displaying the cents
Select the Region Definition tab.
Scroll down to Header and Footer Text. For Region Footer, enter:
Note: This reflects sales for the 12 months prior to the current month.
Click Apply Changes.
Now that the chart is complete, you can view it.
To run the chart:
Click the Run Page icon in the upper right corner of the page.
If you have already run Sample Application in this session, you will be taken to page 750. Otherwise, enter the appropriate username and password and click Login
For User Name, enter either demo or admin
For Password, enter the name of the current workspace using all lowercase letters
Navigate to page 750.
As shown in Figure 7-1, the Revenue by Category chart appears.
Tip:
One way to navigate to a new page within a running application is to change the second parameter (the page identifier) to 750. For example, you would change:
http://htmldb.oraclecorp.com/pls/htmldb/f?p=2046:1: ...
to
http://htmldb.oraclecorp.com/pls/htmldb/f?p=2046:750: ...
