MDX Rank

Normally we use MDX Rank function to get the rank value considering just one dimension level. But I face an situation that Rank value needed to be taken considering two dimension levels. As an example needed to get the Rank of Sales value of stores, which related to a Region.

I was unable to find a direct solution and after doing some researches I was able to build the MDX query.

The query is given below.

WITH

MEMBER [Measures].[Rank] AS

RANK(([Store—Region].CurrentMember,[Store—Region_Store].CurrentMember),

ORDER(({[Store—Region_Store].[Store].MEMBERS*[Store—Region].CurrentMember}),

[Measures].[Sales],

BDESC))

SELECT

{[Measures].[Rank], [Measures].[Sales]} ON 0,

{[Store—Region_Store].[Store].MEMBERS} ON 1

FROM

[Sales]

where {[Store—Region].[All Store—Region].[Region1]}

If we give the region value as Region1, then what this query does is it rank only the stores which are related to the Region1 region. If the Region1 region contains 5 stores, the highest rank value you will get is 5.

Hope this will help for you as well...