8 How to Create a Stacked Bar Chart

About the Syntax for Creating Chart Queries

The syntax for the select statement of a chart is:

SELECT link, label, value
FROM   ...

Where:

• link is a URL. This URL will be called if the user clicks on the that point on the resulting chart.

• label is the text that displays in the bar.

• value is the numeric column that defines the bar size.

You must have all three items in your select statement. In the next example, the link is defined as null because there is no appropriate page to link to.

For example:

SELECT null link, 
       last_name label,
       salary value
FROM   employees
WHERE  DEPARTMENT_ID = :P101_DEPARTMENT_ID 

Creating an Application

First, you create an application using the Create Application Wizard.

To create an application using the Create Application Wizard:

1. On the Workspace home page, click the Application Builder icon.

   The Application Builder home page appears.

2. Click Create.

3. Select Create Application and click Next.

4. For Name:

   1. Name - Enter Bar Chart.

   2. Application - Accept the default.

   3. Create Application - Select From scratch.

   4. Schema - Select the schema where you installed the OEHR sample objects.

   5. Click Next.

      Next, you need to add a page. You have the option of adding a blank page, a report, a form, a tabular form, or a report and form. For this exercise, you add a blank page.

5. Add a blank page:

   1. Under Select Page Type, select Blank and click Add Page.

      The new page appears in the list at the top of the page.

   2. Click Next.

6. For Tabs, accept the default, One Level of Tabs, and click Next.

7. For Copy Shared Components from Another Application, accept the default, No, and click Next.

8. For Attributes, accept the defaults for Authentication Scheme, Language, and User Language Preference Derived From and click Next.

9. For User Interface, select Theme 2 and click Next.

10. Review your selections and click Create.

    The Application home page appears.

Creating a New Page

To create your chart, 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 Bar Chart application you just created.

The chart will display the sum for sales by product category. It will contain sales for the twelve months prior to the current month. In the following exercise, you use a wizard to create the chart and the first query. Then, you add additional queries (or series) for other product categories to make it stacked.

To create a new page:

1. On the Application home page, click Create Page.

2. For page, select Chart and click Next.

3. Select Flash Chart and click Next.

4. For Page Attributes:

   1. For Page Number, enter 2.

   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 Breadcrumb, accept the default.

   6. Click Next.

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

   The Chart Preview appears. Use Chart Preview to configure chart attributes. Click Update to refresh the preview image.

6. On Chart Preview, specify the following:

   1. Chart Type - Select Stacked 3D Column.

   2. Show Legend - Select Right.

   3. Click Update.

      Notice the changes to the preview.

   4. Click Next.

7. For Query:

   1. Enter the following query:

      SELECT NULL link,
             sales_month value,
             revenue "Hardware"
         FROM (
      SELECT TO_CHAR(o.order_date,'Mon YY') sales_month,
             SUM(oi.quantity * oi.unit_price) revenue,
             TO_DATE(to_char(o.order_date,'Mon YY'),'Mon YY') sales_month_order
        FROM OEHR_PRODUCT_INFORMATION p,
             OEHR_ORDER_ITEMS oi,
             OEHR_ORDERS o,
             OEHR_CATEGORIES_TAB ct
       WHERE o.order_date <= (trunc(sysdate,'MON')-1)
         AND o.order_date > (trunc(sysdate-365,'MON'))
         AND o.order_id = oi.order_id
         AND oi.product_id = p.product_id
         AND p.category_id = ct.category_id
         AND ct.category_name like '%hardware%'
      GROUP BY TO_CHAR(o.order_date,'Mon YY')
      ORDER BY sales_month_order
      )
      

      The value label (in this instance, Hardware) is displayed in the legend of stacked charts.

      Tip:

      You can also create a chart query interactively by clicking the Build Query button.

   2. For When No Data Found Message, enter:

      No orders found in the past 12 months.
      

   3. Click Next.

8. Review your selections and click Finish.

   The Success page appears.

Adding Additional Series

Now that you have created a page with a region defining the query, you need to add additional series. In the following exercise, you add a series for the categories software and office equipment.

To add additional series:

1. On the Success Page, click Edit Page.

   The Page Definition for page 2 appears.

2. Under Regions, click Flash Chart next to Revenue by Category.

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

3. To change the name the existing series:

   1. Click the Edit icon.

   2. In Series Name, enter Hardware.

   3. Click Apply Changes.

4. Add a chart series for software:

   1. Scroll down to Chart Series and then click Add Series.

   2. For Series Name, enter Software.

   3. Scroll down to Series Query.

   4. In SQL, enter:

      SELECT NULL link,
             sales_month value,
             revenue "Software"
         FROM (
      SELECT TO_CHAR(o.order_date,'Mon YY') sales_month,
             SUM(oi.quantity * oi.unit_price) revenue,
             TO_DATE(to_char(o.order_date,'Mon YY'),'Mon YY') sales_month_order
        FROM OEHR_PRODUCT_INFORMATION p,
             OEHR_ORDER_ITEMS oi,
             OEHR_ORDERS o,
             OEHR_CATEGORIES_TAB ct
       WHERE o.order_date <= (trunc(sysdate,'MON')-1)
         AND o.order_date > (trunc(sysdate-365,'MON'))
         AND o.order_id = oi.order_id
         AND oi.product_id = p.product_id
         AND p.category_id = ct.category_id
         AND ct.category_name like '%software%'
      GROUP BY TO_CHAR(o.order_date,'Mon YY')
      ORDER BY sales_month_order
      )
      

      The value label (in this instance, Software) is displayed in the legend of stacked charts. Note that this SQL matches the previous series. The only difference is the category in the WHERE clause.

   5. For When No Data Found Message, enter:

      No orders found in the past 12 months.
      

   6. At the top of the page, click Apply Changes.

5. Add a chart series for office equipment:

   1. Under Chart Series, click Add Series.

   2. For Series Name, enter Office Equipment.

   3. Scroll down to Series Query.

   4. In SQL, enter:

      SELECT NULL link,
             sales_month value,
             revenue "Office Equipment"
         FROM (
      SELECT TO_CHAR(o.order_date,'Mon YY') sales_month,
             SUM(oi.quantity * oi.unit_price) revenue,
             TO_DATE(to_char(o.order_date,'Mon YY'),'Mon YY') sales_month_order
        FROM OEHR_PRODUCT_INFORMATION p,
             OEHR_ORDER_ITEMS oi,
             OEHR_ORDERS o,
             OEHR_CATEGORIES_TAB ct
       WHERE o.order_date <= (trunc(sysdate,'MON')-1)
         AND o.order_date > (trunc(sysdate-365,'MON'))
         AND o.order_id = oi.order_id
         AND oi.product_id = p.product_id
         AND p.category_id = ct.category_id
         AND ct.category_name like '%office%'
      GROUP BY TO_CHAR(o.order_date,'Mon YY')
      ORDER BY sales_month_order
      )
      

      The value label (in this instance, Office Equipment) is displayed in the legend of stacked charts.

   5. For When No Data Found Message, enter:

      No orders found in the past 12 months.
      

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

Updating the Sample Data

The sample data that installed with the OEHR Sample Objects application is not current. To make the data current, you need to update the dates in the sample data. You will accomplish this by running an update statement in SQL Commands

To update the dates in the seed data:

1. Return to the Workspace home page. Click the Home breadcrumb link at the top of the page.

2. On the Workspace home page, click SQL Workshop and then SQL Commands.

   The SQL Commands page appears.

3. Enter the following in the SQL editor pane:

   DECLARE
      l_date_offset  number;
   BEGIN
   
   FOR c1 IN (SELECT TRUNC(max(order_date)) max_date
                FROM oehr_orders)
   LOOP
      l_date_offset := round(sysdate - c1.max_date);
   END LOOP;
   UPDATE oehr_orders
      set order_date = order_date + l_date_offset;
   COMMIT;
   END;
   /
   

4. Click Run (Ctrl+Enter) to execute the command.

Viewing the Chart

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

To run the chart:

1. Return to page 2, Revenue by Category:

   1. Click the Home breadcrumb link at the top of the page.

   2. Click Application Builder and then click your Bar Chart application.

   3. Click 2 - Revenue by Category.

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

3. If prompted for a user name and password, enter your workspace user name and password and click Login. See "About Application Authentication".

   Your bar chart should resemble Figure 8-1.

   Figure 8-1 Revenue by Category Stacked Bar Chart

   
   Description of "Figure 8-1 Revenue by Category Stacked Bar Chart"
   
   

   The chart displays the revenue for each product category by month. A legend that defines the color associated with each product appears at the top of the page. Note that the text in X Axis is spaced too closely together. In the next section, you edit the chart attributes to correct this issue.

Editing Chart Attributes

In this exercise, you change the appearance of your chart by editing chart attributes.

To edit chart attributes:

1. Click Edit Page 2 on the Developer toolbar.

2. Under Regions, click Flash Chart.

   The Chart Attributes page appears.

3. Under Chart Settings, edit the chart width. In Chart Width, enter 800.

4. Scroll down to Display Settings. From Animation, select Dissolve.

5. Scroll down to Axes Settings. From Show Group Separator, select Yes.

6. Scroll down to Font Settings. For X Axis Title, select the Font Size 10.

7. Click Apply Changes to a the top of the page.

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

   You chart should resemble Figure 8-2.

   Figure 8-2 Revised Stacked Bar Chart

   
   Description of "Figure 8-2 Revised Stacked Bar Chart"
   
   

   Note that the chart displays on-screen gradually using a dissolve and the X Axis displays correctly.