I have a procedure where I want to subtotal some columns at a particular sort level so I used SUBTOTAL. Some of the columns are directly from the database and they are subtotaled correctly. Some of the rows are computed values and they are subtotaled correctly. One of the rows is subtotaled incorrectly since that column a margin value and it is adding it up (this is like an average so it should not be added - it should be recalculated). Changed SUBTOTAL to RECOMPUTE to correct this and it did for that row and also the other rows that came directly from the database but the rows that are computed values are all way to high (not subtotaled correctly). I can't seem to get this to work althought I've tried 50 combinations of SUBTOTAL and RECOMPUTE. I guess what I'd like to do is SUBTOTAL some rows and RECOMPUTE others but it seems that the RECOMPUTE overrides the SUBTOTAL or maybe that's a coincedence. Any ideas?

TABLE FILE EISBOD
PRINT
ORD AS 'Order'
PRS7/P8C AS 'Pairs'
VALSOLD/P10C AS 'Total Sales'
COMPUTE TOTALSOLDEXT/D10C = SP * PRS7; NOPRINT
COMPUTE TOTALCOSTEXT/D10C = ( TOTSTDCST * PRS7 ); AS 'Total Cost'
COMPUTE TOTALMARGIN/D10C = ( ( SP - TOTSTDCST ) * PRS7 );
AS 'Total Margin'
COMPUTE MarginPct/D7.2% = ( ( TOTALSOLDEXT - TOTALCOSTEXT ) / TOTALSOLDEXT ) * 100;
AS 'Margin%'
BY CUSTNAME AS 'Customer'
BY LOGOP AS 'Logo'
BY PAT AS 'Product'
BY ACS AS 'FOB'
BY TOTSTDCST AS 'Landed'
BY SP AS 'Selling Price'
BY TOTAL COMPUTE MarginPrice/D7.4 = SP - TOTSTDCST; AS 'Margin/Price'

ON CUSTNAME SUBTOTAL AS '*TOTAL '

ON LOGOP SUBTOTAL AS '*TOTAL'
HEADING
"Margin By Pattern"
ON TABLE SUBFOOT
" "
"&DATEMDYY<+0> <+0>&TOD<+0> <+0>&FOCFEXNAME"
WHERE TOTSTDCST NE 0;
WHERE COMP EQ &Company.(FIND COMP IN EISBOD).Company.;
WHERE SLSNAME EQ '&SalesmanName.(FIND SLSNAME IN EISBOD).Salesman Name.';
WHERE RPTDATE GE '&FromDate.From Date.';
WHERE RPTDATE LE '&ThruDate.Thru Date.';
WHERE LF EQ '&LF.(OR(FIND LF IN EISBOD)).LF.';
WHERE STSCD EQ '&StsCd.(OR(FIND STSCD IN EISBOD)).Status Code.';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET POPUPDESC ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML



The column titled "Margin %' is the row I need recomputed.

The column titled "Total Cost" is an example of a subtotal that gives a way high number when RECOMPUTE used.

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

I didn't go through your example carefully, but will when I get a minute. Have you tried SUMMARIZE to see if you get what you need?

How high is "too high" ?
Can you tell what it IS doing with it ? For example, does it look more like a "GRAND TOTAL" ?

You may be able to bury them both in DEFINE statements, or functions, to make them appear the same way to either "SUBTOTL" or "SUMMARIZE" or "RECOMPUTE" ?

Hope this helps ...

quote:

COMPUTE TOTALSOLDEXT/D10C = SP * PRS7; NOPRINT
COMPUTE TOTALCOSTEXT/D10C = ( TOTSTDCST * PRS7 ); AS 'Total Cost'
COMPUTE TOTALMARGIN/D10C = ( ( SP - TOTSTDCST ) * PRS7 );

SP and TOTSTDCST are rates, are defined at the lowest sort level. But since they do not involve a COMPUTE formula, the values used in the COMPUTEs are the sum of their detail values.

Try using a ratio in these computes, something like

COMPUTE ONE/I5=1; NOPRINT
COMPUTE SP1/D12.2 = SP/ONE; NOPRINT
COMPUTE TOTSTDCST1/D12.2 = TOTSTDCST/ONE; NOPRINT
COMPUTE TOTALSOLDEXT/D10C = SP1 * PRS7; NOPRINT
COMPUTE TOTALCOSTEXT/D10C = ( TOTSTDCST1 * PRS7 ); AS 'Total Cost'
COMPUTE TOTALMARGIN/D10C = ( ( SP1 - TOTSTDCST1 ) * PRS7 );

SP1 will equal SP at the original COMPUT level, but in the rollup RECOMPUTEs it will be a weighted average.

JD,
SUBTOTAL and RECOMPUTE are exclusive and if you use both the last operation will prevail. Your only option is to use SUBFOOT and a RECAP for the recalculation of your "margin". See an example using the CAR file:

  
TABLE FILE CAR
PRINT 
     RETAIL_COST
     DEALER_COST
     SALES
     COMPUTE PROFIT/D9 = SALES * ( RCOST - DCOST );
     COMPUTE RATIO/F6.2 = DCOST / RCOST;
BY COUNTRY
BY CAR
     
ON COUNTRY RECAP 
CRATIO/F6.2 = DCOST / RCOST;
ON COUNTRY SUBFOOT
"Total <COUNTRY<ST.RCOST<ST.DCOST<ST.SALES<ST.PROFIT<CRATIO"
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=ON,
     FONT='TIMES NEW ROMAN',
     SIZE=10,
$
TYPE=SUBFOOT, HEADALIGN=BODY, JUSTIFY=RIGHT, $
TYPE=SUBFOOT, ITEM=1, JUSTIFY=LEFT, $
TYPE=SUBFOOT, ITEM=2, JUSTIFY=LEFT, $
ENDSTYLE
END

Since you are PRINTING all the lines, I would put the computed fields in a DEFINE first.
COMPUTE in the TABLE is only necessary if you working with SUM.
The last suggestion from Danny should do the job.

Thanks for all of the replies. I will work through each of them and report back on the results.