MDX Rank request returning unexpected results
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 kno开发者_如何学Cw 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?
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 :-)
精彩评论