January 12, 2007, 02:31 PM
MadamZuZuSubtotal on Over Calculations
DEFINE FILE CAR
CCOUNT/D6 = 1;
END
TABLE FILE CAR
SUM
CCOUNT
OVER PCT.DEALER_COST WITHIN MODEL
BY MODEL
ACROSS COUNTRY
ON TABLE SUBTOTAL CCOUNT
ON TABLE SET NODATA '--'
END
how do i not display "dealer cost" in my subtotal at all?
Thanks!
January 15, 2007, 04:18 AM
Tony ABearing in mind that the subtotal line(s) is/are created as a result of the report contents, I don't think that you can using a straight forward report.
If you were to use FML / FRL - call it what you will - then you have control over what is being printed but that would then require you to prepare the values for the report.
One thing you will not be able to do is to use the syntax to control the ROWS (e.g. BY MODEL ROWS '100 LS 2 DOOR AUTO' OVER '2000 4 DOOR BERLINA' OVER '....... etc.) as the WITHIN syntax seems to be mutually exclusive to using ROWS - you get a FOC168 error.
T
January 15, 2007, 10:00 AM
Tony AHad to use some grey cells for this, but you can do it using FRL (etc. etc.) and a couple of passes of the data.
This solution makes use of the "WHEN EXISTS" syntax of FRL and when building up the data I specifically do not create a value for the subtotal of the percentage field so that the FRL syntax omits the final line as required.
First pass summates the CCOUNT values by MODEL and COUNTRY and also adds a couple of COMPUTED fields to help with positioning in the final report.
The second pass provides the same but substitutes a single value so that the summation produces a single row of output for the subtotal.
The third and final pass summate the PCT.DEALER_COST WITHIN MODEL.
As the hold file used is set-up in append mode all the data is combined when held soo that it can easily be used in a single FRL report.
APP FI TEMPHOLD DISK TEMPHOLD.FTM (APPEND
DEFINE FILE CAR
CCOUNT/D6 WITH MODEL = 1;
MODEL_ST/A24 = 'Subtotal';
END
TABLE FILE CAR
SUM CCOUNT AS 'Count'
COMPUTE ROW_NUM/I1 = 1;
SORT_ORD/I1 = 1;
BY COUNTRY
BY MODEL
ON TABLE HOLD AS TEMPHOLD
END
TABLE FILE CAR
SUM CCOUNT
COMPUTE ROW_NUM/I1 = 1;
SORT_ORD/I1 = 2;
BY COUNTRY
BY MODEL_ST
ON TABLE HOLD AS TEMPHOLD
END
TABLE FILE CAR
SUM PCT.DEALER_COST WITHIN MODEL
COMPUTE ROW_NUM/I1 = 2;
SORT_ORD/I1 = 1;
BY COUNTRY
BY MODEL
ON TABLE HOLD AS TEMPHOLD
END
TABLE FILE TEMPHOLD
SUM DEALER_COST
BY SORT_ORD NOPRINT
BY MODEL
ACROSS COUNTRY
FOR ROW_NUM
1 WHEN EXISTS AS '' OVER
2 WHEN EXISTS AS ''
END
Messy, but I hope this helps
T
January 16, 2007, 11:24 AM
MadamZuZuThanks!