MDX Dynamic Hierarchy 1

ตัวอย่างการทำ Dynamic Hierarchy กับ Calculate member
LEVELS ใช้ Function นี้ในการ Convert string เป็น Hierarchy LEVEL ได้
MEMBERS  ใช้ Function นี้ในการ Convert string เป็น Member (CurrentMember) ได้ด้วย

With member [Measures].[HRC] as
"[Dim_Date].[HRC_YQMD]"

MEMBER [Measures].[AccmCash] as
    Aggregate(
          PERIODSTODATE(
            LEVELS([Measures].[HRC]+".[Year]"),-- CONVERT String to LEVEL
            MEMBERS([Measures].[HRC]+".CURRENTMEMBER") -- CONVERT String to MEMBER
          ),[Measures].[Cash])


SELECT {[Measures].[Cash],[Measures].[AccmCash]} ON 0,
NON EMPTY
[Dim_Date].[Hrc_YQMD].[Date].MEMBERS ON 1
FROM (SELECT [Dim_Date].[Month].&[201201] ON 0 FROM [Cube_Txns])

RESULT
Date                  CASH            ACCMCASH

20120101 44,176,675 44,176,675
20120102 46,656,659 90,833,334
20120103 43,954,618 134,787,952
20120104 46,481,631 181,269,583
20120105 45,993,021 227,262,603
20120106 43,674,555 270,937,158
20120107 40,730,331 311,667,489
20120108 37,520,953 349,188,442
20120109 41,457,006 390,645,448
20120110 41,601,764 432,247,212