Hi,

I would like to do smth like that:

TABLE FILE CAR

SUM
RETAIL_COST
COMPUTE FIELD/D20.2 =
IF ROWTYPE EQ SUBTOTAL THEN RETAIL_COST * SEATS
ELSE RETAIL_COST / SEATS;
BY COUNTRY
ON COUNTRY RECOMPUTE FIELD
END

Basically use the same field but compute it differently for SUBTOTAL row and normal rows?

Does anyone have an idea how to achieve that? I tried something along the lines of computing the field beforehand in a seperate HOLD and then do a

 COMPUTE FIELD2/D20.2 = IF FIELD IS MISSING THEN 'new COMPUTE' ELSE FIELD; 

but that doesn't work, the subtotals aren't treated as if the FIELD is MISSING.

This message has been edited. Last edited by: FP Mod Chuck,

Subtotals represent cumulative values of the column so I doubt you can reinterpret them in this way. Could you provide an example of the output you're looking to create?

I'll try:

I basically have a field that prints a comment to the user for a basic analysis how fields have changed in two instances of data (for example same table but different date).

So there is a column 1 with price1 and column 2 with price2 as values. I have two BY-fields and on the lower one I can comment much more detailed than on the higher one because I know the product. The comments for the lower category are already calculated by a SQL query beforehand.

Example:

  
STORE             PRODUCT         price1        price2              COMMENT
-----------------------------------------------------------------------------------------------------------------------------------------
New Jersey                        500           600                 
                  apple           100           120                 'Due to weather in New Jersey the price for apples is higher now.'
                  smartphones     280           200                 'Smartphones are cheaper now since no one needs another one.'
                  mountainbikes   120           280                 'Since fuel is expensive mountainbikes have risen in price.'

Thats basically my set up. What I want to do now is that I add a basic comment on the subtotal line which just says 'Overall the prices in New Jersey have risen' which is calculated on the subtotal of price1 and price2, i.e.

 IF price1 GT price2 THEN 'prices are lower now' ELSE IF price1 LT price2 THEN 'prices are higher now' ELSE 'prices haven't changed' 

but the comment per product should stay as is.

Do you understand what I mean?

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

Maybe you can create a DEFINE/COMPUTE that has the IF THEN ELSE and use it in a SUBFOOT.

quote:

Originally posted by BabakNYC:
Maybe you can create a DEFINE/COMPUTE that has the IF THEN ELSE and use it in a SUBFOOT.

My Output has a lot more fields to show, would that work together? I would want the field to be shown exactly in the same column as the other comments.

For example:

STORE             PRODUCT         price1        price2      COMMENT                                                                AVERAGE_TIP
--------------------------------------------------------------------------------------------------------------------------------------------------
New Jersey                        500           600         'Prices are higher now.'                                               2.50
                  apple           100           120         'Due to weather in New Jersey the price for apples is higher now.'     0.31
                  smartphones     280           200         'Smartphones are cheaper now since no one needs another one.'          4.25
                  mountainbikes   120           280         'Since fuel is expensive mountainbikes have risen in price.'           2.94

Just to make clear: There are other fields that are calculated via normal SUBTOTAL/RECOMPUTE.

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

You can do something like this. The first two aggregations are performed at a sort break level using "WITHIN" syntax and hidden. They are subsequently used in a calculation (which is also hidden) using column notation (CN).
The DESC prefixed calculations also use CN to refer to pre-calculated values. The DESC1 is hidden but including it means that it can be referenced in a SUBFOOT as Babak suggests.

TABLE FILE CAR
SUM RETAIL_COST WITHIN COUNTRY NOPRINT
    SEATS WITHIN COUNTRY NOPRINT
    RETAIL_COST
    COMPUTE CALC/D20.2 = C1 * C2; NOPRINT
	COMPUTE DESC/A100 = 'The retail cost for' || (' ' | CAR) || (' is ') | LJUST(20,FPRINT(C3, 'D20.2c', 'A20'), 'A20');
	COMPUTE DESC1/A100 = 'The retail cost within' || (' ' | COUNTRY) || (' is ') | LJUST(20,FPRINT(C1, 'D20.2c', 'A20'), 'A20'); NOPRINT
BY COUNTRY
BY CAR
-*ON COUNTRY RECOMPUTE FIELD =  RETAIL_COST / SEATS;
ON COUNTRY SUBFOOT
" <+0> <+0> <DESC1"
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  type=report, grid=off, headalign=body, $
  type=subfoot, backcolor=silver, $
ENDSTYLE
END
-RUN

T

Hi Tony,

really cool idea, would work like a charm in a normal report. Combined with the accordion structured from

ON TABLE SET EXPANDABLE ON 
ON TABLE SET EXPANDBYROWTREE ON

however it doesn't seem to work or do I have to add another line to make him acknowledge the expandable SUBTOTAL-row as my SUBFOOT? Any Ideas?

Played around with some things and came to the conclusion that the SUBTOTAL-line for the expandable structure is neither a SUBFOOT nor a SUBHEAD so I don't think I can alter it like you suggested.

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

Try it with just the "ON TABLE SET EXPANDABLE ON"
and remove the "-*ON TABLE SET EXPANDBYROWTREE ON"

It worked for me.

quote:

Combined with the accordion structured from
ON TABLE SET EXPANDABLE ON
ON TABLE SET EXPANDBYROWTREE ON
however it doesn't seem to work

No mention of accordian reports in your original post so I didn't cover for that.

T

quote:

Originally posted by Tony A:

quote:

Combined with the accordion structured from
ON TABLE SET EXPANDABLE ON
ON TABLE SET EXPANDBYROWTREE ON
however it doesn't seem to work

No mention of accordian reports in your original post so I didn't cover for that.

T

Yeah, I didnt think it would make a difference.

Deleting it destroys the structure of the report for me tho, so that is not an option. Guess I have to work around that somehow.