[b]Running total or summing maximum(field)[/b]

[kphu]

Greetings,

I have an inventory production report that tracks the stage of completion that it is in. Basically the process is started by growing a batch. We never know how large the batch is going to be because of changing growing conditions but typically each batch turns into a lot. Once in a while the batches are too small to be 1 lot so we combine 2 batches as 1 lot or 3 batches as 1 lot, etc.

So my report looks something like this.

TNumber HNumber Year Qtr CostPerLot Stage
GrpHeader 030801 H030308 2003 QTR4 $10,000
Details 030801 H030308 2003 QTR3 $5,000
Details 030801 H030308 2003 QTR4 $10,000
GrpHeader 030802 H030501 2003 QTR4 $11,000
Details 030802 H030501 2003 QTR3 $7,000
Details 030802 H030501 2003 QTR4 $11,000
Details 030802 H030508 2003 QTR3 $7,000
Details 030802 H030508 2003 QTR4 $11,000

The formula's to show the costperlot value your seeing in the groupheader section is using the max({CPL} value.

What I would like to do is sum up the max({CPL}) in the group header section.

So that it would look like this.

TNumber HNumber Year Qtr CostPerLot Stage
GrpHeader 030801 H030308 2003 QTR4 $10,000
Details 030801 H030308 2003 QTR3 $5,000
Details 030801 H030308 2003 QTR4 $10,000
GrpHeader 030802 H030501 2003 QTR4 $22,000
Details 030802 H030501 2003 QTR3 $7,000
Details 030802 H030501 2003 QTR4 $11,000
Details 030802 H030508 2003 QTR3 $7,000
Details 030802 H030508 2003 QTR4 $11,000

I've tried creating a running total but can't do it on the max({CPL}) field.

Any suggestions, much appreciated.

Ken

 

[hneal98]

You might want to use the sum(fld, condfFld) function, where fld is the field you are summing (in this case the max({CPL}), and the condFld is the group. Not sure if it will work, but just a suggestion.

 

[kphu]

thanks for the suggestion.

But when i do that I get the dreaded error "Summary/running total can not be created"

I've known that this is a drawback from crystal for a long time. perhaps someone out there might have an idea.

But for now I'll do what I've always done to resolve this issue which is to export raw data to excel and write a macro to create the report in excel.

Its tedious but it works.

Ken

 

[lbass]

Is the higher value in Qtr 4 just by chance, or is it always higher? If it is always the higher value, then you could just create a formula:

if {table.qtr} = "Qtr4" then {table.costperlot}

Then insert a sum on this. Otherwise you could insert a subreport that uses a running total ({table.costperlot}, sum) with an evaluation formula like:

{table.costperlot} = maximum({table.costperlot},{table.groupfield})

Reset on change of group. Link the subreport on the group field to the main report and then insert the subreport in the group header. In the subreport, suppress all sections except the group footer where the running total is displayed.

-LB