8 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 present 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 available only 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. Verify that Sample Application is installed before you begin the tutorial. See "Checking the Sample Application Installation".

This section contains the following topics:

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

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 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 Workspace home page, click the Application Builder icon.
Click Sample Application.
Click Create Page.
For page, select Chart and then click Next.
Select Stacked Bar, Vertical and then click Next.
For Page Attributes:
1. For Page Number, enter 750.
2. For Page Name, enter Revenue by Category.
3. For Region Template, accept the default.
4. For Region Name, enter Revenue by Category.
5. For Chart Color Theme, accept the default.
6. For Breadcrumb, accept the default.
7. Click Next.
For Tab Options, accept the default Do not use Tabs and then 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 statement of a chart is:
```
SELECT link, label, value
```
  You must have all three items in your select statement. In this example, the link is defined as null because there is no appropriate page to link to.
  
  Note that you cannot include an ORDER BY in the SELECT statement for a series in a stacked chart because the information is displayed in alphabetical order. Displaying dates in alphabetical order does not make sense: for example, October would be displayed 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 statement
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.

The Success page appears.

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 that you have already created the phones category.

To add a series for the computers category:

On the Success Page, select Edit Page.

The Page Definition for page 750 appears.
Under Regions, click SVG Chart next to Revenue by Category.

The SVG Chart page appears with the Chart Attributes tab selected. Scroll down to Chart Series. Note that only one series appears.

Add a chart series for Computer:

Scroll down to Chart Series and then click Add Series.
For Series Name, enter Computer.
Scroll down to 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 the category in the WHERE clause.

For When No Data Found Message, enter:

No orders found in the past 12 months.

At the top of the page, click Apply Changes.

Add a chart series for Audio:

Under Chart Series, click Add Series.
For Series Name, enter Audio.
Scroll down to 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.

At the top of the page, 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.

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.
If you have already run Sample Application in this session, then page 750 is displayed.

If prompted to enter a username and password, then:
1. For User Name, enter either demo or admin.
2. For Password, enter your workspace name in lowercase letters.
3. Click Login.
4. 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 then click Go.
6. Click the Run Page icon in the upper right corner.
The Revenue by Category chart appears, as shown in Figure 8-1.

Figure 8-1 Revenue by Category Bar Chart

Description of "Figure 8-1 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: ...