HTML DB: How To Create a Stacked Bar Chart

How-To Document

How To Create a Stacked Bar Chart

Date: 08-Dec-2004
(for Oracle HTML DB version 1.6)

chart image

Introduction

A stacked bar chart displays the results of multiple queries stacked on top of (vertical) or next to (horizontal) each other. It is a nice way to display data that you want segmented but where you are also interested in the total. The stacked bar chart is only available as an SVG chart. You can initially create a chart as a stacked bar or you can create a regular (non-HTML) bar chart and then add additional queries to it, making it stacked. This How-To will walk you through creating a stacked bar chart based upon the data within the Sample Application that is created when an Oracle HTML DB Workspace is provisioned.

The chart will display the sum, by product category, for sales from within the Sample Application. The sales will be displayed for the twelve months prior to the current month. It excludes sales in the current month because it is just a partial month. There will be 4 queries created, called series, one for each of the categories of products in the Sample Application (Phones, Computer, Audio, Video).

Creating the initial Stacked Bar Chart

To create the initial report, you can add a region to an existing page and define it as a stacked bar chart or you can create a new page. You will create a new page within the Sample Application.

Navigate to the Workspace home page.
From the Applications list, select Sample Application.
Click Create Page >.
Select Page with Component and click Next >.
Select Chart and click Next >.
Select Stacked Bar and click Next >.
For Page Attributes:
- For Page, enter 700.
- For Page Name, enter Revenue by Category.
- For Region Name, enter Revenue by Category.
- Click Next >.
Keep the default of Do not use tabs and click Next >.
For Series Query:

For Series Name, enter Phones.

For 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.

If you were to run the page now, you would see a standard bar chart. To Make it into a stacked chart, you will add additional series.

Adding Additional Series

Now that you have the new page created with the region defining the first query, you need to add series. You will add a series for each of the categories of products in the application (Phones, Computer, Audio, Video). The one for Phones has already been created. You will also enhance the overall look of the chart with Axis Titles.

The first series you will add will be for Computer:

Select Edit Page.
In the Regions section, select Chart to the left of Revenue by Category.

Under Chart Series, you will notice just one Series listed. Click Add Series.

For Series Name, enter Computer.

For 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
)

This SQL matches the previous series, the only difference is the category being used in the where clause.

For When No Data Found Message, enter No orders found in the past 12 months..
Click Apply Changes.

The second series you will add will be for Audio. Click Add Series.

For Series Name, enter Audio.

For 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.

The last series you will add will be for Video. Click Add Series.

For Series Name, enter Video.

For 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.

Now you will format the Y Axis and add a region footer.

Under Axes Settings, For Y Axis Format, enter FML999G999G999G999G990. This will format the sales_month column as money without displaying the cents.
Select the Region Definition tab.
Under Header and Footer Text, for Region Footer, enter Note: This reflects sales for the 12 months prior to the current month..
Click Apply Changes.

Your chart is now complete. You can run it by selecting the run page icon ( ) in the upper right. If you have already run the application in this session, you will be taken directly to the page. If you haven't, you will need to login to the application using the directions listed on the login page (use the demo user). Once you have logged in, you will be taken to the first page defined by the application, not your new page. You will need to change your url to navigate to page 700. To do this, just change the second parameter, the page identifier, after the 'f?p=' to 700 (e.g. change from 'f?p=100:1:...' to 'f?p=100:700:...').