Sort Group based on calcualted average

[tofuTnT]

Hi All,
I am trying to sort the group based on a calcualted average value. The data look like
NAME, TYPE, VALUE
N1, A, 4
N1, A, 5
N1, B, 4
N2, A, 1
N2, B, 2
N2, B, 4

I grouped the records by Name and the average is calculated based on Type. Each group has two average, Average of Type A and Average of Type B. The calculated average is as follow:
CAL_AVG = (AVG_OF_TYPE_A * 2 + AVG_OF_TYPE_B) /3

I put the CAL_AVG in the group footer and want to order the group by CAL_AVG.

Is there a way to do that?

Thanks

tofuTnT

 

[lbass]

What version of CR are you using? You should always specify this.

-LB

 

[tofuTnT]

Crystal Report Standard 11

Thanks
tofuTnT

 

[Madawc]

Check Top N, I think it works for averages.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

I think you would have to handle this in a command where you could return the averages directly, something like:

Select table.`name`, table.`type`, table.`value`,
(select avg(A.`value`) from table A
where A.`name` = table.`name` and
A.`type` = 'A') as avgA,
(select avg(A.`value`) from table A
where A.`name` = table.`name` and
A.`type` = 'B') as avgB
From table

The syntax/punctuation will vary based on your datasource. Then in the report, insert a group on {command.name} and then create a formula {@calc} to do the calculation:

(({command.avgA}*2)+{command.avgB})/3

Place this in the detail section and insert a maximum on it. Then go to report->group sort and choose "All" and select maximum of {@calc} as your sorting field. You have to insert a summary on a field in order for the group sort option to become available (even though the value of {@calc} in the detail section is the same as the maximum for the group.

-LB