7 How to Create a Stacked Bar Chart

Accessing Sample Application

To access Sample Application:

1. Log in to Oracle HTML DB.

2. From the Workspace Administration list, select Review Demonstration Applications.

3. Locate Sample Application and check the Status column:

   1. If the Status column displays Installed, click Run in the Action column.

   2. If the Status column displays Not Installed, select Install in the Action column. When the Application Builder home page appears, click the Run icon.

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

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 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:

1. Navigate to the Workspace home page.

2. From the Applications list, select Sample Application.

3. Click Create Page.

4. Select Page with Component and click Next.

5. On Select Component Type, select Chart and click Next.

6. Select Stacked Bar and click Next.

7. For Identify Page Attributes:

   1. For Page, enter 750.

   2. For Page Name, enter Revenue by Category.

   3. For Region Name, enter Revenue by Category.

   4. Click Next.

8. For Tab Options, accept the default Do not use Tabs and click Next.

9. On Series 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, 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.

   3. For When No Data Found Message, enter:

      No orders found in the past 12 months.
      
      

   4. Click Next.

10. 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 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:

1. On the Success Page, select Edit Page.

   The Page Definition for page 750 appears.

2. Under Regions, select Chart to the left of Revenue by Category.

   Under Chart Series, notice that only one series appears.

3. Add a chart series for Computer. Under Chart Series, click Add Series.

   1. For Series Name, enter Computer.

   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 = '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.

   3. For When No Data Found Message, enter:

      No orders found in the past 12 months.
      
      

   4. Click Apply Changes.

4. Add a chart series for Audio. Under Chart Series, click Add Series.

   1. For Series Name, enter Audio.

   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 = 'Audio'
      GROUP BY TO_CHAR(o.order_timestamp,'Mon YYYY')
      ORDER BY sales_month_order
      )
      
      

   3. For When No Data Found Message, enter:

      No orders found in the past 12 months.
      
      

   4. Click Apply Changes.

5. Add a chart series for Video. Under Chart Series, click Add Series.

   1. For Series Name, enter Video.

   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 = 'Video'
      GROUP BY TO_CHAR(o.order_timestamp,'Mon YYYY')
      ORDER BY sales_month_order
      )
      
      

   3. For When No Data Found Message, enter:

      No orders found in the past 12 months.
      
      

   4. Click Apply Changes.

Changing the Chart Format

Next, you will enhance the appearance of the chart with axis titles by adding a region footer.

To format the Y axis:

1. Scroll down to Axes Settings.

2. In Y Axis Format, enter:

   FML999G999G999G999G990
   
   

   This formats the sales_month column as money, but without displaying the cents

3. Select the Region Definition tab.

4. Scroll down to Header and Footer Text. For Region Footer, enter:

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

5. Click Apply Changes.

Viewing the Chart

Now that the chart is complete, you can view it.

To run the chart:

1. Click the Run Page icon in the upper right corner of the page.

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

3. Navigate to page 750.

   As shown in Figure 7-1, the Revenue by Category chart appears.

   Figure 7-1 Revenue by Category Bar Chart

   
   Description of the illustration bar_chart.gif
   
   

http://htmldb.oraclecorp.com/pls/htmldb/f?p=2046:1: ... 

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