开发者

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]
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜