Hello All,

When I column total on specific fields it is not totalling for them. However when I give the column total as a whole, it totals for all the fields (including the one's it didnt total earlier).
I am unable to understand this behaviour. I just wanted a fresh set of eyes on this so that I can get the column totals for the specific fields. In the code below, the column total doesnt total for the USD values when specified.

Code:

 SET EMPTYREPORT = ON

DEFINE FILE X_FP_WEIGHTED_AVERAGE_RATE
NOMINAL_AMT/D20.2B = X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.NOMINAL_AMOUNT;
OUTSTANDING_NOTIONAL_AMT_USD/D20.2B = X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.OUTSTANDING_NOTIONAL_AMOUNT_US;
CARRYING_VALUE_LOCAL/D20.2B = X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.BOOK_VALUE_LOCAL + X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.ACCRUED_PROFIT_LOCAL; 
CARRYING_VALUE_USD/D20.2B = X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.BOOK_VALUE_LOCAL_US + X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.ACCRUED_PROFIT_LOCAL_US; 
END

TABLE FILE X_FP_WEIGHTED_AVERAGE_RATE
SUM 
     COMPUTE TOT_OUTSTANDING_NOTIONAL_AMT_USD/D20.2B = X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.OUTSTANDING_NOTIONAL_AMT_USD; NOPRINT 
     COMPUTE TOT_ACCRUED_PROFIT_LOCAL_USD/D20.2B = X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.CARRYING_VALUE_USD; NOPRINT 
PRINT 
     X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.CONTRACTUAL_FIXED_RATE AS 'Contractual,Fixed,Rate'
     X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.CONTRACTUAL_FLOAT_RATE AS 'Contractual,Floating,Rate'
     X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.NOMINAL_AMT AS 'Outstanding,Notional Amt,(Tx Ccy)'
     X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.OUTSTANDING_NOTIONAL_AMT_USD AS 'Outstanding,Notional Amt,(USD)'
     X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.CARRYING_VALUE_LOCAL AS 'Carrying,Value,(Local)'
     X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.CARRYING_VALUE_USD AS 'Carrying,Value,(USD)'
     COMPUTE PCT_NOTIONAL_AMT_USD/D20.8 = X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.OUTSTANDING_NOTIONAL_AMT_USD / TOT_OUTSTANDING_NOTIONAL_AMT_USD; NOPRINT 
     COMPUTE PCT_ACCRUED_PROFIT_LOCAL_USD/D20.8 = X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.CARRYING_VALUE_USD / TOT_ACCRUED_PROFIT_LOCAL_USD; NOPRINT 
     COMPUTE WAR_FIXED/D20.8 = PCT_NOTIONAL_AMT_USD * X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.CONTRACTUAL_FIXED_RATE; AS 'Weighted,Avg,Fixed'
     COMPUTE WAR_FLOAT/D20.8 = PCT_NOTIONAL_AMT_USD * X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.CONTRACTUAL_FLOAT_RATE; AS 'Weighted,Avg,Float'
     COMPUTE WAR_YIELD/D20.8 = PCT_ACCRUED_PROFIT_LOCAL_USD * X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.EFFECTIVE_RATEYLD; AS 'Weighted,Avg,Yield'
     COMPUTE COMBINED/D20.8 = WAR_FIXED + WAR_FLOAT; AS 'Combined,Fix/Flo,Weighted,Avg(USD)'
BY  X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.OWNER_ID AS 'Business,Group'
BY  X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.ORIGINAL_TIME_BAND_IDENTIFIER AS 'Classification'
BY  LOWEST X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.TXN_IDENTIFIER NOPRINT 
     
ON X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.OWNER_ID SUBFOOT WITHIN 
"Subtotal<X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.OUTSTANDING_NOTIONAL_AMT_USD<X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.CARRYING_VALUE_USD <+0> <+0> <+0> "
WHERE ( X_FP_WEIGHTED_AVERAGE_RATE.INPUT.I_INSTANCE EQ '&ENV_VAR' ) AND ( X_FP_WEIGHTED_AVERAGE_RATE.INPUT.I_PORTFOLIO_ID EQ '&PORT_ID' ) 
AND ( X_FP_WEIGHTED_AVERAGE_RATE.INPUT.I_TAG EQ '&RESULT' ) AND ( X_FP_WEIGHTED_AVERAGE_RATE.INPUT.I_LONG_SHORT EQ '&LONG_SHORT' )
AND ( X_FP_WEIGHTED_AVERAGE_RATE.INPUT.I_FROM_DATE EQ DT(&FROMDT_YYMD.PLEASE ENTER THE FROM DATE.) ) 
AND ( X_FP_WEIGHTED_AVERAGE_RATE.INPUT.I_TO_DATE EQ DT(&TODT_YYMD.PLEASE ENTER THE TO DATE.) );
ON TABLE SET PAGE-NUM OFF 
ON TABLE COLUMN-TOTAL AS 'Grand Total' 'X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.OUTSTANDING_NOTIONAL_AMT_USD' 'X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.CARRYING_VALUE_USD' WAR_FIXED WAR_FLOAT WAR_YIELD COMBINED
ON TABLE PCHOLD FORMAT &WFFMT
ON TABLE SET STYLE *
     INCLUDE = gtms_sty,
$
     PAGESIZE='A3',
     SQUEEZE=ON,
     ORIENTATION=LANDSCAPE,
$
ENDSTYLE
END 

Thanks for looking into this.

Thanks,
Sumant

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

I'm curious as to why the column is in quotes in the ON TABLE COLUMN-TOTAL line, but not in the PRINT line...

Hello Francis,

The quotes in the column total have been put by the GUI.
I have removed the quotes and ran the report, but I still do not get the total on them.
The only way I can get a column total is when I total for all the fields.


Sumant

Which exact columns do not get totaled by COLUMN-TOTAL? Have you tried ON TABLE SUB-TOTAL?

{BY|ON} fieldname {SUB-TOTAL|SUBTOTAL} [MULTILINES]
      [field1 [AND] field2...] [AS 'text'][WHEN expression;]

'X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.OUTSTANDING_NOTIONAL_AMT_USD' and 'X_FP_WEIGHTED_AVERAGE_RATE.ANSWERSET1.CARRYING_VALUE_USD' are the fields that do not get totalled. They are the ones which are in the quotes.
Something weird is happening with SUBTOTAL command. When I subtotal and specify only the specific fields to subtotal, it subtotals other fields also(computes).
However when I subtotal in the subfoot they are working fine.
The COLUMN-TOTAL still fails for the fields in the quotes. Remaining others are totalled just fine.


Thanks,
Sumant