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