Dear All,

I have a report which is working fine until I added the following line of codes in order to get the Total and Percentage by the channels against the Grand Total.

Would appreciate it if anyone help.

Below are the code snippet:

YEAR1/A4='&yearFrom';
YEAR2/A4='&yearTo';
STDT/DMYY=HDATE(PYMT_V3.INBDAT, 'YYMD');
DATE3/A8YYMD=STDT;
MM3/A2=EDIT(DATE3,'$$$$99$$');
YY3/A4=EDIT(DATE3,'9999$$$$');
YEAR_YY/YY=STDT;
MONTH_M/Mt=STDT;
YEAR_A/A5=EDIT(YEAR_YY,'9999$');
CURRDT/DMYY='&DMYY';
MTH/A1=
IF MM3 EQ '01' THEN 'A' ELSE IF MM3 EQ '02' THEN 'B' ELSE IF MM3 EQ '03' THEN 'C' ELSE
IF MM3 EQ '04' THEN 'D' ELSE IF MM3 EQ '05' THEN 'E' ELSE IF MM3 EQ '06' THEN 'F' ELSE
IF MM3 EQ '07' THEN 'G' ELSE IF MM3 EQ '08' THEN 'H' ELSE IF MM3 EQ '09' THEN 'I' ELSE
IF MM3 EQ '10' THEN 'J' ELSE IF MM3 EQ '11' THEN 'K' ELSE IF MM3 EQ '12' THEN 'L';
MONTH/A5=DECODE MTH (
A 'JAN' B 'FEB' C 'MAR' D 'APR' E 'MAY' F 'JUN'
G 'JUL' H 'AUG' I 'SEP' J 'OCT' K 'NOV' L 'DEC'
);
YEARTOT/D12.2C=JAN + FEB + MAR + APR + MAY + JUN + JUL + AUG + SEP + OCT + NOV + DEC;
PTYPE/A20=
IF INTTYP EQ 2000 THEN 'CDM' ELSE IF INTTYP EQ 2100 THEN 'CASH' ELSE IF INTTYP EQ 2300 THEN 'IB' ELSE IF INTTYP EQ 2310 THEN 'IBG' ELSE
IF INTTYP EQ 2320 THEN 'IBFT' ELSE IF INTTYP EQ 2400 THEN 'CHEQUE' ELSE IF INTTYP EQ 2700 THEN 'ATM' ELSE IF INTTYP EQ 2800 THEN 'AUTO DEBIT';
END

TABLE FILE PYMT_V3
SUM
'PYMT_V3.INBEL/D12.2C' AS 'TOTAL AMOUNT'
'PYMT_V3.YEARTOT' AS 'TOTAL'
BY YEAR_A AS 'YEAR'
BY INTTYP AS 'CHANNEL CODE'
BY PTYPE AS 'CHANNEL'
ACROSS MONTH_M AS 'MONTH'
WHERE YY3 GE YEAR1;
WHERE YY3 LE YEAR2;
ON TABLE HOLD AS HOLD1
END
TABLE FILE HOLD1
SUM
YEARTOT
ON TABLE SAVE
END
-RUN
-READ SAVE &GRAND_TOT.D12.2C.
-RUN

TABLE FILE PYMT_V3
SUM
'PYMT_V3.INBEL/D12.2C' AS 'TOTAL AMOUNT'
COMPUTE PCT/D6.2% = YEARTOT / &GRAND_TOT * 100; AS '%'
BY YEAR_A AS 'YEAR'
BY INTTYP AS 'CHANNEL CODE'
BY PTYPE AS 'CHANNEL'
ACROSS MONTH_M AS 'MONTH'
ON 'PYMT_V3.YEAR_A' SUBTOTAL AS '*TOTAL'

This message has been edited. Last edited by: Kerry,

Do you have fields named JAN FEB MAR etc?

You might to understand what you are doing before making changes to code.

T

What you are doing seems unnecessarily complicated

1) You take a two-character representation for the month - 01, 02 .. 12 and convert it to a single-character letter value - A, B .. L;

2) You take that letter value and convert it to a 3-character representation for the month - JAN, FEB .. DEC (although ypu allow 5 characters to do this in);

3) You then try to add the names of the months and put them in a double-precision field. I think you somehow lost your way here and what you want is some value, not the names of the months.

I think for starters you could make it a lot simpler by:

DEFINE FILE x
YY3/YY=PYMT_V3.INBDAT;
MM3/M=PYMT_V3.INBDAT;
MTH/MTR=PYMT_V3.INBDAT;
END

I don't understand all the unnecessary (to me) conversion of the date information to alpha. You have it as YYMD in the beginning and you can go from there directly to what you need: the year, month in numeric format, month spelled out.

If you clean this up (replace 16 lines of code with 3) I think the problem with the totals should become apparent.

Dear George,

Thank you for replying and the simplified code.

Will definitely test it out next week.


Regards...JS