Thursday 15 November 2012

Filter Dimension for last 5 years data


Following MDX query can be used to filter the Dimension for displaying past 5 years data.

SELECT { } ON COLUMNS,

{

    ( [Fiscal Period].[Fiscal Month Name].[Fiscal Month Name].ALLMEMBERS

    * [Fiscal Period].[Fiscal Year Name].[Fiscal Year Name].ALLMEMBERS

    * [Fiscal Period].[Period].[Period].ALLMEMBERS

    )

} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( StrToMember("[Fiscal Period].[Fiscal Year Name].&["+ FORMAT(DATEADD("YYYY",-5,NOW()),"yyyy")+"]")

              : StrToMember("[Fiscal Period].[Fiscal Year Name].&["+ FORMAT(DATEADD("YYYY",1,NOW()), "yyyy")+"]")

              ) ON COLUMNS

         FROM [KarnakReportmart]

     ) CELL PROPERTIES VALUE







No comments:

Post a Comment