Monday, July 28, 2014

Parent, FirstChild and LastChild

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:
image
Have Fun And Till Next Time

No comments:

Post a Comment