Oracle APEX Tutorial 7 – Stacked Bar Chart

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 pieces of information contained in it. It is recommended that you review our article Oracle APEX Tutorial Preparation and OEHR Sample Data Install before proceeding with this tutorial.

[youtube]http://www.youtube.com/watch?v=wROM3MPE8CQ[/youtube]

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 &lt;= (trunc(sysdate,'MON')-1)
AND o.order_date &gt; (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 &lt;= (trunc(sysdate,'MON')-1)
AND o.order_date &gt; (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 &lt;= (trunc(sysdate,'MON')-1)
AND o.order_date &gt; (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;
/

2 Comments on “Oracle APEX Tutorial 7 – Stacked Bar Chart – Video Training”


Souvik Ghosh
07.10.2011 at 7:10 am

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

Mohammed Moazzam
06.21.2016 at 7:46 am

Dear Team,

Greetings!

I have installed APEX 5.03 installed and I tried creating bar char but ends with flash chart error.
I am looking forward to create bar chart, please post video.

Regards,
Moazzam.

Oracle APEX Tutorial 7 – Stacked Bar Chart – Video Training

Introduction

High Level Steps

Code Snippets

2 Comments on “Oracle APEX Tutorial 7 – Stacked Bar Chart – Video Training”

Leave a Reply to Mohammed Moazzam Cancel reply

Let's Talk!
(888) 812-6318