Minimum Value in Group 2 from Aggregated result in Group 3

Hi There,
I have a 3 groups in a report.

Group 3 has a Formula (Formula1) returning average of a field based on group 2. eg:- Average({Table.Field},Group3)

I want to display the minimum Value returned by Formula1 in Group2's Header.

How can I do that? if i write Minimum(Formula1,Group2) it says "This field cannot be summarized" cause i have used a aggregate in Formula1.

Help will be appriciated.

The only way I have ever created a "Minimum of an Average" was to use 3 formulas which keep track of everything in a Global Variable.

This will not work if you print the value in the Group Header. You could probably use subreport (and a Shared Variable) for that...

Create these 3 Formulas..

1) In Group 2 Header; Name= ResetMinOfAvg; Suppress= True
Use to reset the value of the Global variable to the average of the first Group 3 within Group 2.

WhilePrintingRecords;
Global currencyVar MinOfAvg;

MinOfAvg := Average ({Table.Field}, {Table.GroupField});

2) In Group Footer 3; Name=MinOfAvg; Suppress=True
Use to see if the current Minimum value is less then the Average value calculated for the group.

WhilePrintingRecords;
Global currencyVar MinOfAvg;

If Average ({Table.Field}, {Table.GroupField}) < MinOfAvg Then
MinOfAvg := Average ({Table.Field}, {Table.GroupField}) ;


3) In Group 2 Footer; Name=DisplayMinOfAvg;Suppress=False
Use to display the MinOfAvg value.

WhilePrintingRecords;
Global currencyVar MinOfAvg;

MinOfAvg;

HTH

Hey Dude,

Thankz a thousand times.
it worked out fine i just had to use sub-report to get the result in the header.

Thankz Again :)