Minimum Value in Group 2 from Aggregated result in Group 3

[RohanP]

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.

Regards Rohan

______________________________
- Regards Rohan (India,Mumbai)

 

[lbass]

The simplest way to do this, and maybe the only way to get the result in the group header, is to go to report->topN/SortGroup->choose the group 3 tab and select "Average of {table.field}", and then choose "Ascending". Then create a formula:

average({table.field},{table.group3field})

Place this in the group #2 header. The first average (the minimum because of topN) will now appear in the group 2 header.

If you can't reorder your group #3 instances, then you would have to use formulas like the following and display the result in the group #2 footer:

//{@reset} to be placed in the group#2 header:
whileprintingrecords;
numbervar min := 0;

//{@min} to be placed in the group#3 header or footer:
whileprintingrecords;
numbervar min;

if onfirstrecord or
{table.group2field} <> previous({table.group2field} or
average({table.field},{table.group3field}) < min then
min := average({table.field},{table.group3field}) else
min := min;

//{@display} to be placed in the group#2 footer:
whileprintingrecords;
numbervar min;

-LB

 

[RohanP]

Hey Dude,

Thankz a thousand times.
I had to use simple logic rather that useing next & prev functions. it worked out fine i just had to use sub-report to get the result in the header.

Thankz Again

______________________________
- Regards Rohan (India,Mumbai)