Wednesday, 20 November 2013

Getting Top N values using RANK function

Below Query shows how can we get top N values using RANK function in MDX.

Example 1

WITH SET NEWSET AS ORDER
(
    [Product].[Product Categories].[SubCategory].MEMBERS
    ,[Measures].[Order Count]
    ,BDESC
)
MEMBER [MEASURES].[Ranked] AS RANK (       
        [Product].[Product Categories].CURRENTMEMBER
        ,NEWSET
    )
SELECT
    {
        [Measures].[Order Count]
        ,[MEASURES].[Ranked]
    } ON 0
    ,ORDER( FILTER([Product].[Product Categories].[SubCategory].MEMBERS
                    ,[MEASURES].[Ranked]<=20)
            , [MEASURES].[Ranked]
            , ASC) ON 1
FROM [Adventure Works]


No comments:

Post a Comment