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