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