7 How to Create a Stacked Bar Chart

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 Application Builder a 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:

About Sample Application
Creating a Stacked Bar Chart
Adding Additional Series
Changing the Chart Format
Viewing the Chart

About Sample Application

Application Builder installs with a number of demonstration applications. In this exercise you create a stacked bar chart within the demonstration application, Sample Application.

To see if Sample Application is installed:

Log in to the Database Home Page using your database username and password.
Click the down arrow on the right side of the Application Builder icon.
From the menu, select Demonstrations.

The Demonstration Applications page appears, displaying links to demonstration applications.
Locate Sample Application and check the Status column:
1. If the Status column displays Installed, return to the Database Home Page.
2. If the Status column displays Not Installed, select Install in the Action column. Follow the on-screen instructions.

Creating a Stacked Bar Chart

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 for sales by product category from within Sample Application. It will contain sales for the twelve months prior to the current month. In the following exercise, you create four queries (called series) for each of the product categories (phones, computers, audio, and video).

To create a new page:

On the Database Home Page, click the Application Builder icon.
Select Sample Application.
Click Create Page.
Select Chart and click Next.
Select Stacked Bar, Vertical and click Next.
For Page Attributes:
1. For Page, enter 750.
2. For Page Name, enter Revenue by Category.
3. For Page Template, accept the default.
4. For Region Name, enter Revenue by Category.
5. For Chart Color Theme, accept the default.
6. Click Next.
For Tab Options, accept the default Do not use Tabs and click Next.
For Query:
1. For Series Name, enter Phones.
2. 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 in this example, the link is defined as null.
  
  Note that you cannot include an ORDER BY in the SELECT statement for a series in a stacked chart because the information displays in alphabetical order. Displaying dates in alphabetical order does not make sense, for example, October would display before September. A more appropriate approach would be to display the data in chronological order. To display the data in chronological order, you need to order the data inside a nested SELECT.
3. For When No Data Found Message, enter:
```
No orders found in the past 12 months.
```
4. Click Next.
Review your selections and click Finish.

Adding Additional Series

Once you have created the new page with a region defining the query, you need to add the series. In the following exercise, you 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 as shown in Figure 7-1.

Figure 7-1 Chart Link on the Page Definition

Description of "Figure 7-1 Chart Link on the Page Definition"

The Chart Attributes page appears. Scroll down to Chart Series. Notice that only one series appears.

Add a chart series for Computer.

Scroll down to Chart Series and click Add Series.
For Series Name, enter Computer.
Scroll down the Series Query.

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.

Scroll up to the top of the page and click Apply Changes.

Add a chart series for Audio:

Scroll down to Chart Series and click Add Series.
For Series Name, enter Audio.
Scroll down the Series Query.

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.

Scroll up to the top of the page and click Apply Changes.

Add a chart series for Video:

Scroll down to Chart Series and click Add Series.
For Series Name, enter Video.
Scroll down the Series Query.

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.

Scroll up to the top of the page and click Apply Changes.

Changing the Chart Format

Next, you 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
Scroll up and select the Region Definition tab.
Scroll down to Header and Footer.

In Region Footer, enter:

Note: This reflects sales for the 12 months prior to the current month.

Scroll up to the top of the page and click Apply Changes.

Viewing the Chart

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. (See Figure 7-2.)

Figure 7-2 Run Page Icon

Description of "Figure 7-2 Run Page Icon"
If you have already run Sample Application in this session, page 750 displays.

If prompted to enter a username and password:
1. For User Name, enter either demo or admin.
2. For Password, enter your database user name in lowercase letters.
3. Click Login.
4. Navigate to page 6. Click Edit Page 1 on the Developer toolbar at the bottom of the page.
  
  The Page Definition appears.
5. In the Page field, enter 750 and click Go.
6. Click the Run Page icon in the upper right corner.
As shown in Figure 7-3, the Revenue by Category chart appears.

Figure 7-3 Revenue by Category Bar Chart

Description of "Figure 7-3 Revenue by Category Bar Chart"

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://apex.oraclecorp.com/pls/apex/f?p=2046:1: ...

http://apex.oraclecorp.com/pls/apex/f?p=2046:750: ...