[solved] Column totals on across-values within by-field
In the example below, the sales figures are in local currency of COUNTRY. I like to have column totals on SEATS within each COUNTRY. There seems no provision in GUI report painter to do what I want. Is there a simple way to do this? Thanks.
DEFINE FILE CAR
CURRENCY/A3 = IF COUNTRY EQ 'JAPAN' THEN 'YEN' ELSE 'EUR';
END
TABLE FILE CAR
SUM
SALES
CURRENCY
BY COUNTRY
SUM
SALES
BY COUNTRY
BY CAR
ACROSS SEATS ACROSS-TOTAL
END
This message has been edited. Last edited by: Hua,
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
June 03, 2010, 12:14 PM
njsden
Hua, I'm not sure I understand your requirement completely. Is this perhaps what you need?
DEFINE FILE CAR
CURRENCY/A3 = IF COUNTRY EQ 'JAPAN' THEN 'YEN' ELSE 'EUR';
END
TABLE FILE CAR
SUM
SALES
CURRENCY
BY COUNTRY
ON COUNTRY SUBTOTAL SUM. SALES
SUM
SALES
BY COUNTRY
BY CAR
ACROSS SEATS ACROSS-TOTAL
END
It would help if you provided a sample output illustrating what you expect.
Thanks for your reply, njsden. Here is the sample output:
SEATS
2 4 5 TOTAL
COUNTRY SALES CURRENCY CAR SALES SALES SALES SALES
ENGLAND 12000 EUR JAGUAR 0 12000 12000
JENSEN 0 0
TRIUMPH 0 0
*TOTAL ENGLAND 0 0 12000 12000 <====== TOTAL PER SEAT PER COUNTRY
FRANCE 0 EUR PEUGEOT 0 0
*TOTAL FRANCE 0 0 0 0 0 <====== TOTAL PER SEAT PER COUNTRY
ITALY 30200 EUR ALFA ROMEO 25400 4800 30200
MASERATI 0 0 0
*TOTAL ITALY 25400 4800 30200 <====== TOTAL PER SEAT PER COUNTRY
JAPAN 78030 YEN DATSUN 43000 43000
TOYOTA 35030 35030
*TOTAL JAPAN 0 78030 0 78030 <====== TOTAL PER SEAT PER COUNTRY
W GERMANY 88190 EUR AUDI 7800 7800
BMW 8900 71490 80390
*TOTAL W GERMANY 0 8900 79290 88190 <====== TOTAL PER SEAT PER COUNTRY
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
June 03, 2010, 01:01 PM
njsden
How about this?
DEFINE FILE CAR
CURRENCY/A3 = IF COUNTRY EQ 'JAPAN' THEN 'YEN' ELSE 'EUR';
END
TABLE FILE CAR
SUM
SALES
CURRENCY
BY COUNTRY
SUM
COMPUTE C_SALES/D10.2 = SALES; AS 'SALES'
BY COUNTRY
BY CAR
ACROSS SEATS ACROSS-TOTAL
ON COUNTRY SUBTOTAL SUM. C_SALES
END
DEFINE FILE CAR
CURRENCY/A3 = IF COUNTRY EQ 'JAPAN' THEN 'YEN' ELSE 'EUR';
C_SALES/D10.2 = SALES;
END
TABLE FILE CAR
SUM
C_SALES AS 'SALES'
CURRENCY
BY COUNTRY
SUM
SALES
BY COUNTRY
BY CAR
ACROSS SEATS ACROSS-TOTAL
ON COUNTRY SUBTOTAL SUM. SALES
ON TABLE NOTOTAL
END
Originally posted by njsden: You're welcome! Please let us know if your adventure with Report Painter proves to be fruitful!
- Neftali.
As fruitful in GUI. Use different field name for sure when summing the same field in mult-sorts.
Hua
Developer Studio 7.6.11 AS400 - V5R4 HTML,PDF,XLS
June 03, 2010, 02:36 PM
njsden
quote:
you have readymade solutions
Not really Ram
As Hua had already done what we expect from anyone who asks for support in this forum (that is: do some research first, try by themselves and post workable sample codes using any of the IBI-provided files) it was rather easy to try different options until reaching the intended output. I even initially proposed some solution that was nowhere close to what Hua needed but thankfully it did not create more confusion (I guess ).