Dynamic Ranking

WITH MEMBER
[Measures].[Leave Count Rank] AS
IIF(ISEMPTY([Measures].[Leave Count]), NULL,

RANK(AXIS(1).ITEM(0).ITEM(AXIS(1).ITEM(0).COUNT-1).HIERARCHY.CURRENTMEMBER,

ORDER(

EXTRACT(AXIS(1), AXIS(1).ITEM(0).ITEM(AXIS(1).ITEM(0).COUNT-1).HIERARCHY)
,
[Measures].[Leave Count],

DESC)
)
)

SELECT {
[Measures].[Leave Count]
,[Measures].[Leave Count Rank]
} ON
AXIS(0),

NON
EMPTY
[Division].[DivisionShortName].[DivisionShortName].MEMBERS
*
[Department].[Department ShortName].[Department ShortName].MEMBERS

ON
AXIS(1)
FROM [Resource Utilization]
WHERE ([Date].[Fiscal Year].&[2009])