AS2000 MDX , TOP N with member name

人客想要在AS2000找出本年度1月到上月為止排名前10的產品大類名稱、排名及數量

其中
XX02年月為國曆 年月(例10105)
XX10產品為產品維度



//提供本年度1月到上月為止 總計的的TOP N MDX語法
With
MEMBER [X].[XX02年月].[THISYEAR] 
AS ' SUM (LASTPERIODS( IIF(MONTH(NOW() ) = 1 , 12, MONTH(NOW()) -1) ,  STRTOMEMBER ( "[X].[XX02年月].[ALL].[" + CSTR( IIF( MONTH(NOW()) = 1 , YEAR(NOW() ) -1912, YEAR(NOW()) -1911 )  )  + "].[" + CSTR( IIF ( MONTH(NOW()) = 1 ,YEAR(NOW() ) -1912 , YEAR(NOW()) - 1911)  )  +  RIGHT( "00" + CSTR( IIF(MONTH(NOW() ) = 1 , 12, MONTH(NOW()) -1) )  , 2 )  + "] " ) )  ) ' 

SET [ROW] 
AS '   topcount   ( {   [X].[XX10產品].LEVELS(1).ALLMEMBERS }   ,10, [MEASURES].[數量]   )    '

MEMBER [Measures].[排名]  
 AS   '  RANK (   [X].[XX10產品].CurrentMember, [ROW]  )  '

MEMBER [Measures].[產品名稱]  
AS   '   [X].[XX10產品].CurrentMember.Name  '

SET [COL] 
AS ' {  [Measures].[排名]   ,  [Measures].[產品名稱]  , [MEASURES].[數量]  }  '

Select
[COL]    On Columns  ,

[ROW]     On Rows

From CubeProducts

Where (  [X].[XX02年月].[THISYEAR]   )


 人客又反應, 排序怪怪的沒有按照順序排,
所以把 [ROW] 改成以下囉

SET [ROW] 

AS '    ORDER ( TOPCOUNT( {   [X].[XX10產品].LEVELS(1).ALLMEMBERS }   ,10, [MEASURES].[數量]   )  , [MEASURES].[數量]    , DESC  )    '