In MDX, calculate the sales rank of a given shop
I have an OLAP cube containing the sales count for each of my shops.
Using MDX, how can I output the rank of a given shop?
I am dreaming about something like below (does not work), it would return 8 if SomeSh开发者_运维百科op is the 8th most-selling shop:
SELECT RANK( [Shop].CHILDREN, [Shop].[SomeShop]) from [Sales]
You should check out the examples on msdn, the last example will work here.
Something like this:
WITH MEMBER [Measures].[rank] AS RANK( [Shop].CurrentMember, [Shop].MEMBERS)
SELECT {[Measures].[rank], ...} on 0
ORDER([Shop].MEMBERS, [Measures].[rank], ASC) on 1
FROM [Sales]
Here is the solution I have found.
Any better solution would be greatly appreciated.
WITH MEMBER [Measures].[rank] AS RANK(
[Shop].CurrentMember,
Order(
[Shop].Members,
[Measures].[salescount],
BDESC
),
[Measures].[salescount]
)
SELECT Order(
[Shop].Members,
[Measures].[salescount],
BDESC
).Item([SomeShop]) ON COLUMNS,
[Measures].[salescount] ON ROWS
FROM [Sales]
精彩评论