Top N with Others, with a twist

[jcrouch]

HI,

I needed a report to show the top 10 departments within a list of groups. I did find in these forums and elsewhere methods to do this but only if 'department' was the top level. This was done using a rank data item, then another data item based off of that that used a if/then to diplay the others.

But I am stuck when I try using that same technique on a field that is down a level. To clarify this is what I need:

Group     Top 10 Dept     Sales
A            Red                   100
A            Blue                   90
...
A            Others               75
B            Green                125
B            Blue                   95
...
B            Others               120

I am doing this using relational data on a list report BTW.

The rank by sales I can get and then I filter that using [rank]<=10, setting that to After Auto Agg. But if I replace Dept with:

If ([rank]<=10) Then ([Dept]) Else ('Others')

This does not work. I get one Dept per Group that is ranked 1. I think this is because when I replace the Dept field with the if/then its now doing that Before Auto Agg?

It seems to me if I could get that data item to calculate After Auto Agg it would work, but Im not sure if thats possible.

Any suggestions? Am I on the right track?

Thanks,

Jeremy