views:

12

answers:

1

I wrote this fairly simple MDX request to get the rank by sales count of each gender:

WITH MEMBER [Measures].[rank] AS RANK(
      [Gender].CurrentMember,
      Order(
         [Gender].Members,
         [Measures].[salescount],
         BDESC
      ),
      [Measures].[salescount]
   )
SELECT [Gender].Members ON COLUMNS,
[Measures].[rank] ON ROWS
FROM [SalesAnalysis]

The problem is that the result is wrong, I know M has more sales than F:

Axis #0:
  {}
Axis #1:
  {[Measures].[rank]}
Axis #2:
  {[Gender].[All Genders]}
  {[Gender].[F]}
  {[Gender].[M]}
Row #0: 1
Row #1: 2
Row #2: 3

If I use "Country" instead of "Gender", same problem: the rank is given according to the alphabetical order, instead of based on the sales count. (ie: all countries come listed in alphabetical order, with rank 1,2,3,4,...)

How can I fix the request to correctly show each gender's salescount rank?

A: 

OK, I have found the solution:

WITH MEMBER [Measures].[rank] AS RANK(
      [Reseller].CurrentMember,
      Order(
         [Reseller].Members,
         [Measures].[salescount],
         BDESC
      ),
      [Measures].[salescount]
   )
SELECT Order(
         [Reseller].Members,
         [Measures].[salescount],
         BDESC
      ).Item([theShopWhoseRankIWant]) ON COLUMNS,
[Measures].[rank] ON ROWS
FROM [SalesAnalysis]

Sorry for the noise, I hope this might help someone :-)

Nicolas Raoul