Using the Parent functionality you can get the First Child and Last Child within hierarchy level. 15-JAN-2011 ==> Parent = JAN-2011 ==> FirstChild = 1-JAN-2011 ==> LastChild = 31-JAN-2011.
Here is an example:
WITH
MEMBER [Measures].[Sales Amount First Child] AS
SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.PARENT.FIRSTCHILD), FORMAT_STRING="Currency"
MEMBER [Measures].[Sales Amount Last Child] AS
SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.PARENT.LASTCHILD), FORMAT_STRING="Currency"
SELECT
{
[Measures].[Sales Amount]
, [Measures].[Sales Amount First Child]
, [Measures].[Sales Amount Last Child]
} ON COLUMNS
,
{
[Date].[Calendar].Children
} ON ROWS
FROM [Adventure Works]
where [Date].[Fiscal].[Month].&[2011]&[3]
This will give us:
Have Fun And Till Next Time
No comments:
Post a Comment