Search This Blog

Thursday, 15 November 2012

Rank and Dense Rank in MDX

WITH
MEMBER [Measures].[Rank]ASRank(
[Product].[Model Name].
CurrentMember,[Product].[Model Name].[Model Name],
[Measures].[ORDER Count]
)

SET [Sorted Models]AS
ORDER
(
[Product].[Model Name].[Model Name]
,[Measures].[ORDER Count]
,
BDESC)MEMBER [Measures].[Previous Model INDEX]AS(Rank(
[Product].[Model Name].
CurrentMember,[Sorted Models]
)
-
2
)

MEMBER [Measures].[Dense Rank]AS
Case
When [Measures].[Rank] = 1
Then 1
Else(
[Sorted Models].
Item([Measures].[Previous Model INDEX]),[Measures].[Dense Rank]
)
+

Iif(
(
[Sorted Models].
Item([Measures].[Previous Model INDEX]),[Measures].[ORDER Count]
)
=
[Measures].[ORDER Count]
,0
,1
)
EndSELECT{
[Measures].[Rank],
[Measures].[Dense Rank]
}
ON 0,{
[Sorted Models]
}
ON 1FROM [Adventure Works]