Wednesday, September 28, 2011

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...

No comments:

Post a Comment