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 <= (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; / |
2 Comments on “Oracle APEX Tutorial 7 – Stacked Bar Chart – Video Training”
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
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.