Report Practical - Matrix Report

 Matrix Report :

MATRIX REPORT 1:

SELECT CUSTOMER_ID,ORDER_ID,ORDER_DATE FROM ORDERS;

SELECT ORDER_ID,PRODUCT_ID,NVL(UNIT_PRICE,0)*NVL(QUANTITY,0) AMOUNT FROM ORDER_ITEMS;

SELECT A.CUSTOMER_ID,A.ORDER_DATE,B.PRODUCT_ID,B.AMOUNT FROM (SELECT CUSTOMER_ID,ORDER_ID,ORDER_DATE FROM ORDERS) A, (SELECT ORDER_ID,PRODUCT_ID,NVL(UNIT_PRICE,0)*NVL(QUANTITY,0) AMOUNT FROM ORDER_ITEMS) B WHERE A.ORDER_ID=B.ORDER_ID;

Query 2:

SELECT CUSTOMER_ID,TO_CHAR(ORDER_DATE,'RRRR') YR,NVL(SUM(AMOUNT),0) TOTAL_AMOUNT FROM
(
SELECT A.CUSTOMER_ID,A.ORDER_DATE,B.PRODUCT_ID,B.AMOUNT
FROM
(SELECT CUSTOMER_ID,ORDER_ID,ORDER_DATE FROM ORDERS) A,
(SELECT ORDER_ID,PRODUCT_ID,NVL(UNIT_PRICE,0)*NVL(QUANTITY,0) AMOUNT FROM ORDER_ITEMS) B
WHERE A.ORDER_ID=B.ORDER_ID
)
GROUP BY CUSTOMER_ID,TO_CHAR(ORDER_DATE,'RRRR');

MATRIX REPORT 2:

SELECT ORDER_MODE,TO_CHAR(ORDER_DATE,'RRRR') YY, SUM(ORDER_TOTAL) FROM ORDERS GROUP BY ORDER_MODE,TO_CHAR(ORDER_DATE,'RRRR');