Report Totals
John McClain Jan 23, 2012 2:41 PMI have searched the forum for an answer to this (Google too) and have not been able to see anything that speaks to this question.
In APEX, I have a standard SQL report region with this SQL:
WITH DETAIL_DATA as (
SELECT so.so_store_cd
, sl.qty
, sl.unit_prc
, i.repl_cst
FROM so so
, so_ln sl
, itm i
WHERE so.del_doc_num = sl.del_doc_num
AND sl.itm_cd = i.itm_cd
AND so.so_wr_dt BETWEEN :P3_BEGIN_DATE and :P3_END_DATE)
, SUMMARY_DATA as (SELECT so_store_cd
, sum(qty) ext_qty
, sum(qty*unit_prc) ext_prc
, sum(qty*repl_cst) ext_cost
, sum(qty*unit_prc)-sum(qty*repl_cst) ext_gmd
FROM DETAIL_DATA
GROUP BY so_store_cd)
--
SELECT nvl(so_store_cd,'Grand Total:') store,
sum(ext_qty) ext_qty
, sum(ext_prc) ext_prc
, sum(ext_gmd) ext_gmd
, decode(sum(ext_prc), 0, 0, round(sum(ext_gmd)/sum(ext_prc),4)) * 100||'%' ext_gmp
FROM SUMMARY_DATA
GROUP BY rollup((so_store_cd))
And I get this:
STORE EXT_QTY EXT_PRC EXT_GMD EXT_GMP
05 2 120 -40 -33.33%
CW 2 1458 678 46.5%
50 10 8749.58 7192.98 82.21%
02 56 16332.73 7287.68 44.62%
00 93 76759.39 65299.36 85.07%
JM 582 591991.59 483010.31 81.59%
01 2141 799715.33 509445.92 63.7%
C1 2912 637650.54 334570.17 52.47%
Grand Total: 5798 2132777.16 1407444.42 65.99%
Works great until you enable sorting. Then, the "Grand Total:" row is considered to be just another data row, and that gets sorted along with the "actual" data.
I have also tried just enabling column sums in Report Attributes since that stays at the bottom, but I can't see a way to perform any calculations, i.e. sum(ext_gmd)/sum(ext_prc). I don't just want to sum that column (ext_gmp), I need to have the calculation result.
So, my questions are:
1) Is there a way to "lock" the row at the bottom of the results using the Group by/Rollup method, irrespective of sorting, or
2) Is there any way to perform calculations of the Apex generated Total row when using the Report Attributes SUM functionality?
Thanks
John
In APEX, I have a standard SQL report region with this SQL:
WITH DETAIL_DATA as (
SELECT so.so_store_cd
, sl.qty
, sl.unit_prc
, i.repl_cst
FROM so so
, so_ln sl
, itm i
WHERE so.del_doc_num = sl.del_doc_num
AND sl.itm_cd = i.itm_cd
AND so.so_wr_dt BETWEEN :P3_BEGIN_DATE and :P3_END_DATE)
, SUMMARY_DATA as (SELECT so_store_cd
, sum(qty) ext_qty
, sum(qty*unit_prc) ext_prc
, sum(qty*repl_cst) ext_cost
, sum(qty*unit_prc)-sum(qty*repl_cst) ext_gmd
FROM DETAIL_DATA
GROUP BY so_store_cd)
--
SELECT nvl(so_store_cd,'Grand Total:') store,
sum(ext_qty) ext_qty
, sum(ext_prc) ext_prc
, sum(ext_gmd) ext_gmd
, decode(sum(ext_prc), 0, 0, round(sum(ext_gmd)/sum(ext_prc),4)) * 100||'%' ext_gmp
FROM SUMMARY_DATA
GROUP BY rollup((so_store_cd))
And I get this:
STORE EXT_QTY EXT_PRC EXT_GMD EXT_GMP
05 2 120 -40 -33.33%
CW 2 1458 678 46.5%
50 10 8749.58 7192.98 82.21%
02 56 16332.73 7287.68 44.62%
00 93 76759.39 65299.36 85.07%
JM 582 591991.59 483010.31 81.59%
01 2141 799715.33 509445.92 63.7%
C1 2912 637650.54 334570.17 52.47%
Grand Total: 5798 2132777.16 1407444.42 65.99%
Works great until you enable sorting. Then, the "Grand Total:" row is considered to be just another data row, and that gets sorted along with the "actual" data.
I have also tried just enabling column sums in Report Attributes since that stays at the bottom, but I can't see a way to perform any calculations, i.e. sum(ext_gmd)/sum(ext_prc). I don't just want to sum that column (ext_gmp), I need to have the calculation result.
So, my questions are:
1) Is there a way to "lock" the row at the bottom of the results using the Group by/Rollup method, irrespective of sorting, or
2) Is there any way to perform calculations of the Apex generated Total row when using the Report Attributes SUM functionality?
Thanks
John