[SOLVED][RS] Sorting within multiple groups

[workdan]

I've got a ranking report that I need to sort. The report shows locations and then the employees at each, I've got groupings on both those columns. I need the report to show locations in descending order of performance (by a measure), and then within each location, show the employees in descending order of their performance.

eg.) I have location A with employees 1 (60 sales) and 2 (10 sales), and location B with employees 3 (50 sales) and 4 (100 sales). The report should look like this:

Location   Employee   Sales
B              4                100
                3                50
B Total                       150
A              1                60
                2                10
A Total                       70

I can't find a way to do this, which is odd, because it seems like a fairly simple requirement. As far as the Grouping and Sorting property on the list, I can't add the same data item as a sort key in two different groups.

The alternative I can think of is to use the Cognos Rank() summary function to create an expression and then sort on that, but I'm not sure how: something like RANK([Sales] FOR [Location]) doesn't produce correct results, and if I try to use the AT keyword, I get an error saying "Unable to use AT on the first parameter of the RANK function".

Any ideas?

Thanks,

Dan

[MDXpressor]

Hi Dan,
With the Location data item selected, go into the advanced sorting function.  Place the Sales data item in the Location sort key.  Then go back to your list, highlight the Sales column and set to sort descending.

Let me know how it works out for you.
Cheers.

[workdan]

Aha! MDX, you made my day.

Initially, trying what you suggested didn't work. When I looked at the advanced sorting setup, however, I saw that your technique had added the sales measure to the Location Group's sort keys, and to the Detail sort keys. However, since I had a subgroup on Employee between those two, it wasn't getting sorted. It appears that you can add the same item as a sort key on a group and on the details, but not on two groups.

Since I don't actually need the group on Employee (I only needed to group them so I could subtotal on Location), I removed the group and it sorts the way I want it to.

Thanks for pushing me in the right direction!

Regards,

Dan