Advanced MDX Ranking

There was a situation that I want to get the Rank value considering three dimension levels. In one of earlier post (MDX Rank post), I mentioned how to get the rank value for two dimension levels.

This post will shows the MDX query to get the rank value for three dimension levels.

Lets consider a scenario that we want to get the Rank value of a department considering the stores of a particular region. Below MDX query will give the required rank value.

WITH

MEMBER [Measures].[RankSales] AS

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

ORDER(({[Store—Region_Store].[Store].MEMBERS*[Store—Region].CurrentMember*[Item—

Department

].CurrentMember}),

[Measures].[Sales],

BDESC))

SELECT

{[Measures].[RankSales]} ON 0,

{[Store—Store].[Store].MEMBERS*[Item—

Department

].[

Department

].MEMBERS

} ON 1

FROM

[Sales]

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

Department_Sub

].[All Item—

Department_Sub

].[

Department1

]}

Likewise we can expand the query to any dimension levels. Hopes this will help you...