How to get Sum of Top 5 in Group and then Sort by that Sum

[Former Member]

Hi,

This is my first time at SCN.  I am developing an application in VS2008 using VB.Net and Crystal Reports for VS2008.  To make things easy I will give you the full story.

I have a report that displays only the best 5 golf scores (sorted ASC) Grouped by Golf Cub.  Stopping the display to show only 5 scores was done by suppressing the Group if the "place" value was greater than 5.  The datatable behind the report has many more scores in it.

When I attempt to set a Running Total or Insert a Summary (Sum) field for the (Golf Club) Group to get the sum of these 5 scores I get the sum of ALL scores for ALL players from that Club. 

Ultimately I want to be able to sort the report based on the sum of those 5 scores ie. by Club.

Can anyone set me on the right path.

Thanks in advance,

John

[abhilash_kumar · Accepted solution]

Hi John,

The most efficient way to handle this is to report off of a SQL Query that 'limits' all groups to return only 5 records.

You can then insert a Summary and use the 'Group Sort Expert' to sort all groups on this summary.

-Abhilash

[Former Member · Accepted solution]

Thanks,

Yes, that's essentially what I ended up doing.

Cheers,

John