Tuesday, September 13, 2011

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

No comments:

Post a Comment