July 06, 2009, 11:10 AM
ac1 [SOLVED] Manipulating Summary Values - Report Excel
Hi,
I have this temporary file:
KEY COL1 COL2 COL3 FIX_VALUE COL4 COL5
----- ---- ------------ --------- ---------- ------ -------
XXX CX 200,00 -99,030 -0,030 99,000 198,00
XXX TT 100,00 -90,030 -0,030 90,000 90,00
ZZZ CX 50,00 -396,030 -0,030 396,000 198,00
ZZZ TT 30,00 -300,030 -0,030 300,000 90,00 Where the columns 4 and 3 are calculeted in this way:
COL4 = COL5*36500/365/COL2
COL3 = FIX_VALUE - COL4
I would like to create a report excel with the row total with sum for column 2 and 5 and column 4 and 3 calculated as for the temporary file.
The report should be like this:
KEY COL1 COL2 COL3 FIX_VALUE COL4 COL5
----- ---- ------------ ------------- ---------- -------- -------------
XXX CX 200,00 -99,030 -0,030 99,000 198,00
TT 100,00 -90,030 -0,030 90,000 90,00
Total 300,00 -96,030 -0,030 96,000 288,00
ZZZ CX 50,00 -396,030 -0,030 396,000 198,00
TT 30,00 -300,030 -0,030 300,000 90,00
Total 80,00 -360,030 -0,030 360,000 288,00
I tried in this way:
TABLE FILE TMP
SUM COL2
COL3
FIX_VALUE
COL4
COL5
BY KEY
COL1
ON KEY SUBTOTAL AS 'TOTAL'
ON TABLE SAVE AS 'XLSTOTAL' FORMAT EXL2K
END
I don't want the sum for COL4 and COL3, but they should be calculated on the base of COL5 and COL2
Thanks for any help
This message has been edited. Last edited by: Kerry , July 13, 2009 11:03 AM July 06, 2009, 11:38 AM
Microfich If instead you calculate COL3 and COL4 'on the fly' via a COMPUTE statement within your TABLE command, you could then say:
ON KEY RECOMPUTE COL3 COL4
ON KEY SUBTOTAL COL2 COL5 The RECOMPUTE statement will recalculate your COMPUTE statements at break time.
July 06, 2009, 11:48 AM
Francis Mariani
TABLE FILE TMP
SUM
COL2
COMPUTE COL3/D12 = FIX_VALUE - (COL5 * 36500 / 365 / COL2);
FIX_VALUE
COMPUTE COL4/D12 = COL5 * 36500 / 365 / COL2;
COL5
BY KEY
BY COL1
ON KEY RECOMPUTE AS 'TOTAL'
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
July 07, 2009, 04:57 AM
ac1 Thank you Microfich and Francis, COMPUTE and ON KEY RECOMPUTE work fine together.
Actually the value 365 is a field of the file and I only had to change the COMPUTE in this way:
COMPUTE COL3/D12 = LAST FIX_VALUE - (COL5 * 36500 / (LAST NUMBER_DAYS) / COL2);
COMPUTE COL4/D12 = COL5 * 36500 / (LAST NUMBER_DAYS) / COL2;
otherwise FIX_VALUE and NUMBER_DAYS are multiplied, by the number of keys present in file, in the total rows.
The value of these fields never change, so it should be correct take the last value.
Is there a better way?
Thanks a lot
This message has been edited. Last edited by: ac1 , July 07, 2009 05:51 AM