Oracle APEX Tutorial 7 – Stacked Bar Chart – Video Training

Posted on Aug 6, 2009 in Technical Tips | 1 comment



Introduction

Entering and retrieving data is all well and good, but sometimes a visual display of the raw numbers gives more impact and relates more information. A common way to do this is with a chart. APEX is well equipped to create charts, and this tutorial focuses on creating a stacked bar chart, or a bar chart in which each bar has multiple peices of information contained in it. It is reccomended that you review our article Oracle APEX Tutorial Preparation and OEHR Sample Data Install before proceeding with this tutorial.

YouTube Preview Image

High Level Steps

1) Create Application (0:30)
2) Add Additional Series (3:40)
3) Test and Edit Chart (6:20)

The times in parenthesis are approximate start times for that step

Code Snippets

Message for No Data Found (All Series)

No orders found in last 12 months.

SQL for initial application

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
)

SQL for second series

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
)

SQL for Third Series

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
)

SQL Script for Updating Sample Data

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;
/
Bookmark and Share

Related Information:

  1. First and Last Day of Month – Oracle SQL Syntax Examples
    First Day of Current Month...
  2. Oracle APEX Tutorial 4 – Form Layout – Part 1 – Video Training
    APEX gives you a lot...
  3. Oracle TO_CHAR Function – SQL Syntax Examples (Most With Dates, TO_DATE)
    This article provides common examples...
  4. Oracle APEX Tutorial 5 – Check Boxes – Part 2 – Video Training
     Within APEX, there are...
  5. Oracle APEX Tutorial 5 – Check Boxes – Part 1 – Video Training
    Within APEX, there are a...
  6. Oracle APEX Tutorial 2 – Parameterized Report Part 2 – Video Training
    This Tutorial is a guided...
  7. Oracle APEX Tutorial 8 – Up and Downloading Files – Part 2 – Video Training
    Reports and data entry constitue...
  8. Oracle APEX Tutorial 9 – JavaScript – Video Training
    JavaScript is everywhere on the...

1 Comment

  1. Souvik Ghosh
    July 10, 2011

    Hello Team,

    I need more clarity on below pice of code

    “WHERE o.order_date <= (trunc(sysdate,’MON’)-1)”

    as per my knowledge if i use ; means the line is over how just after ; i can use =

    Thanks & regrads,

    Souvik Ghosh

    reply

Leave a Reply