Oracle APEX Tutorial 7 - Stacked Bar Chart - Video Training

By M&S Consulting (Kevin) on Aug 6th, 2009
Filed Under Technical Tips // Tags: ,

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. Oracle APEX Tutorial 4 – Form Layout – Part 1 – Video Training
    APEX gives you a lot of power and flexibility with its built in wizards. They are very good at creating...
  2. Oracle TO_CHAR Function - SQL Syntax Examples (Most With Dates, TO_DATE)
    This article provides common examples of TO_CHAR using dates....
  3. Oracle APEX Tutorial 5 – Check Boxes – Part 2 – Video Training
     Within APEX, there are a multitude of things you can use Check Boxes for, and many ways they can...
  4. Oracle APEX Tutorial 5 – Check Boxes – Part 1 – Video Training
    Within APEX, there are a multitude of things you can use Check Boxes for, and many ways they can be...
  5. Oracle APEX Tutorial 3 - Drilldown Report Part 2- Video Training
    This tutorial covers section 4 of the Oracle APEX Advanced Tutorials, Creating a Drilldown Report...
  6. Oracle APEX Tutorial 3 - Drilldown Report Part 1- Video Training
    This tutorial covers section 4 of the Oracle APEX Advanced Tutorials, Creating a drilldown report...
  7. Oracle APEX Tutorial 2 - Parameterized Report Part 2 - Video Training
    This Tutorial is a guided walkthrough of section 3 of the Oracle APEX Advanced Tutorials....
  8. Oracle APEX Tutorial 4 – Form Layout – Part 2 – Video Training
    APEX gives you a lot of power and flexibility with it's built in wizards. They are very good at creating...

Leave a Reply


Archives

Recent Comments

  • Kavitha Muniraj said: Hi, Could you please send a script which checks the status of forms and reports of oracle...
  • Siddharth said: Hey thanks a lot !! Have been lookin all over for this :) My PC may not be able t handle FlashBuilder...
  • Cyrex said: Hey man why I cant install Apex 4? is there anyone can help me.,?
  • sharanabasava said: ALTER TABLE [my_table_name] RENAME COLUMN [current_column_name] TO [new_column_name]; Sir,I...
  • Huub Vankan said: Thanks for this easy solution. Indeed strange that is does not work in the (x86) folder….

Calendar

July 2010
M T W T F S S
« Jun    
 1234
567891011
12131415161718
19202122232425
262728293031  

Editor's Picks

Featured Testimonial

"M&S has taken 'partnership' to a new level. They are not only a deeply skilled service provider, the M&S team is actually a mission critical component to our operations, taking on new initiatives as well as maintaining custom, open source, and COTS solutions."

CEO, MedPivot

Free SQL Service