Hello Focal Point Friends:

I'm encountering some strange behavior with FML reports when attempting to do a recap calculation for multiple column totals and restricting it to one column. So, essentially I am trying to create a grand total for all column totals. I'm not certain if using defines for the different columns might be complicating things, but any assistance is appreciated. Example code using Car file:

 SET ACROSSTITLE = SIDE
SET NODATA = 0
DEFINE FILE CAR
2_SEATS/I6=IF CAR.BODY.SEATS EQ 2 THEN CAR.BODY.SALES ELSE 0;
4_SEATS/I6=IF CAR.BODY.SEATS EQ 4 THEN CAR.BODY.SALES ELSE 0;
5_SEATS/I6=IF CAR.BODY.SEATS EQ 5 THEN CAR.BODY.SALES ELSE 0;
END
TABLE FILE CAR
SUM
     CAR.BODY.2_SEATS/I6CB
     CAR.BODY.4_SEATS/I6CB
     CAR.BODY.5_SEATS/I6CB
FOR
     CAR.ORIGIN.COUNTRY
"EUROPE" LABEL R1 OVER
'ENGLAND' AS 'ENGLAND' LABEL R2 OVER
'W GERMANY' AS 'W GERMANY' LABEL R3 OVER
'FRANCE' AS 'FRANCE' LABEL R4 OVER
'ITALY' AS 'ITALY' LABEL R5 OVER
BAR OVER
RECAP R7=R1 + R2 + R3 + R5;
 AS 'TOTAL EURO SALES' OVER
" " LABEL R8 OVER
"ASIA" LABEL R9 OVER
'JAPAN' AS 'JAPAN' LABEL R10 OVER
BAR OVER
RECAP R12=R10;
 AS 'TOTAL ASIA SALES' OVER
" " LABEL R13 OVER
"SALES SUMMARY" LABEL R14 OVER
RECAP R15=R12 + R7;
 AS 'TOTAL SALES' OVER
" " LABEL R16 OVER
RECAP R17(3)=R15(1)+R15(2)+R15(3);
 AS 'ALL SALES SUMMARY'
ON TABLE SUBHEAD
"CAR SALES FOR EUROPE AND ASIA"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
TYPE=DATA,
     LABEL=TOTAL,
     COLUMN=N1,
     BACKCOLOR='SILVER',
     STYLE=BOLD,
$
TYPE=DATA,
     LABEL=R12ASALES,
     COLUMN=N1,
     BACKCOLOR='SILVER',
     STYLE=BOLD,
$
TYPE=DATA,
     LABEL=R7ROSALES,
     COLUMN=N1,
     BACKCOLOR='SILVER',
     STYLE=BOLD,
$
TYPE=DATA,
     LABEL=TOTAL,
     ACROSSCOLUMN=N1,
     BACKCOLOR='SILVER',
$
TYPE=DATA,
     LABEL=R12ASALES,
     ACROSSCOLUMN=N1,
     BACKCOLOR='SILVER',
$
TYPE=DATA,
     LABEL=R7ROSALES,
     ACROSSCOLUMN=N1,
     BACKCOLOR='SILVER',
$
TYPE=ACROSSVALUE,
     ACROSS=1,
     JUSTIFY=RIGHT,
$
TYPE=FREETEXT,
     LABEL=R9,
     STYLE=BOLD,
$
TYPE=FREETEXT,
     LABEL=R1,
     STYLE=BOLD,
$
TYPE=FREETEXT,
     LABEL=R10,
     STYLE=BOLD,
$
TYPE=FREETEXT,
     LABEL=R11,
     STYLE=BOLD,
$
ENDSTYLE
END 

The code gives me blank values for the "ALL SALES SUMMARY" (R17). If I remove the column reference R17(3), and use R17=R15(1)+R15(2)+R15(3) I end up with the same figure in all three columns that does not match the data.

On my actual report I'm having a similar anomaly. In this case I want the recap to be within the 4th column, like R17(4); so R17(4)=R15(1)+R15(2)+R15(3)+R15(4) and my recap total ends up in the second column on display instead of the 4th.

I originally thought column labels were complicating matters, so I reverted the code to have my column labels match the stock R(incremental#). The same behavior persisted.

It's strange as with a more simplified example in Car:

 DEFINE FILE CAR
CONVERTIBLE/I6=IF CAR.BODY.BODYTYPE EQ 'CONVERTIBLE' THEN CAR.BODY.SALES ELSE 0;
SEDAN/I6=IF CAR.BODY.BODYTYPE EQ 'SEDAN' THEN CAR.BODY.SALES ELSE 0;
COUPE/I6=IF CAR.BODY.BODYTYPE EQ 'COUPE' THEN CAR.BODY.SALES ELSE 0;
ROADSTER/I6=IF CAR.BODY.BODYTYPE EQ 'ROADSTER' THEN CAR.BODY.SALES ELSE 0;
HARDTOP/I6=IF CAR.BODY.BODYTYPE EQ 'HARDTOP' THEN CAR.BODY.SALES ELSE 0;
END
TABLE FILE CAR
SUM
     CAR.BODY.CONVERTIBLE
     CAR.BODY.SEDAN
     CAR.BODY.HARDTOP
     CAR.BODY.ROADSTER
     CAR.BODY.COUPE
FOR
     CAR.ORIGIN.COUNTRY
'ENGLAND' AS 'ENGLAND' LABEL R1 OVER
'FRANCE' AS 'FRANCE' LABEL R2 OVER
'ITALY' AS 'ITALY' LABEL R3 OVER
'JAPAN' AS 'JAPAN' LABEL R4 OVER
'W GERMANY' AS 'W GERMANY' LABEL R5 OVER
RECAP R6=R1+R2+R3+R4+R5;
 AS 'SALES' OVER
" " LABEL R7 OVER
RECAP R8(5)=R6(1)+R6(2)+R6(3)+R6(4)+R6(5);
 AS 'ALL SALES'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END 

Having the recap in the one column R8(5) in this example works fine. Curious if anyone has encountered something similar. Advanced thanks for any input and apologies if I'm missing something obvious.

This message has been edited. Last edited by: Mike Williams,

Mike,

Your columns are mixed up because you re-defined your verb objects:

 
     CAR.BODY.2_SEATS/I6CB
     CAR.BODY.4_SEATS/I6CB
     CAR.BODY.5_SEATS/I6CB
 

This created 3 new columns, so you really have 6 columns (two for each verb object). The only visible columns are 2,4, and 6. Here is a simplified version of your original:

  
DEFINE FILE CAR
2_SEATS/I6=IF CAR.BODY.SEATS EQ 2 THEN CAR.BODY.SALES ELSE 0;
4_SEATS/I6=IF CAR.BODY.SEATS EQ 4 THEN CAR.BODY.SALES ELSE 0;
5_SEATS/I6=IF CAR.BODY.SEATS EQ 5 THEN CAR.BODY.SALES ELSE 0;
END
TABLE FILE CAR
SUM
     CAR.BODY.2_SEATS/I6CB
     CAR.BODY.4_SEATS/I6CB
     CAR.BODY.5_SEATS/I6CB
FOR
     CAR.ORIGIN.COUNTRY
"EUROPE"                              OVER
'ENGLAND' AS 'ENGLAND'     LABEL CA   OVER
'W GERMANY' AS 'W GERMANY' LABEL CB   OVER
'FRANCE' AS 'FRANCE'       LABEL CC   OVER
'ITALY' AS 'ITALY'         LABEL CD   OVER
BAR OVER
RECAP TA=CA + CB + CC + CD;
 AS 'TOTAL EURO SALES'     LABEL TA   OVER
" "                                   OVER
"ASIA"                                OVER
'JAPAN' AS 'JAPAN'         LABEL CE   OVER
BAR OVER
RECAP TB=CE;
 AS 'TOTAL ASIA SALES'     LABEL TB   OVER
" "                                   OVER
"SALES SUMMARY"                       OVER
RECAP TC=TA+TB ;
 AS 'TOTAL SALES'          LABEL TC   OVER
" "                                   OVER
BAR                                   OVER
RECAP
  MYTOT(6) = TC(1) + TC(3) + TC(6) ;
                                      OVER
BAR
ON TABLE SUBHEAD
"CAR SALES FOR EUROPE AND ASIA"
ON TABLE SET ASNAMES ON
END
-RUN

which gives:

 PAGE     1
 
 CAR SALES FOR EUROPE AND ASIA
                     2_SEATS    4_SEATS    5_SEATS                             
                     -------    -------    -------                             
 EUROPE
 ENGLAND                  0          0     12,000 
 W GERMANY                0      8,900     79,290 
 FRANCE                   0          0          0 
 ITALY               25,400      4,800          0 
                   ---------  ---------  ---------
 TOTAL EURO SALES    25,400     13,700     91,290 
  
 ASIA
 JAPAN                    0     78,030          0 
                   ---------  ---------  ---------
 TOTAL ASIA SALES         0     78,030          0 
  
 SALES SUMMARY
 TOTAL SALES         25,400     91,730     91,290 
  
                   ---------  ---------  ---------
 MYTOT                                    208,420 
                   ---------  ---------  ---------
 

Appreciate the help, Ed.