none
MDX how to get all children ??

    Question

  • Hi,

    I want to find all calendar dates lies under slicer " [Date].[Calendar].[Month].&[2004]&[5] ".


    WITH MEMBER Measures.CName as
    [Date].[Calendar].[Date].children
    select
    {  Measures.CName } on columns
    from [Adventure Works]
    WHERE [Date].[Calendar].[Month].&[2004]&[5]

    Thanks,

    Ravi

    Saturday, August 24, 2013 1:40 PM

Answers

  • PrevMember function applies to a member, not a set. It will return the previous member in the same level as the member. What I believe you're trying to do is return back current days amounts along with a comparison to yesterdays. To do so, you can create a query scoped calculated measure that overwrites the current date context using the PrevMember function. If you use a Descendants function and the [Date].[Calendar] user navigable hierarchy, you can also use other functions, such as the ParallelPeriod function to return back a comparison to previous month and previous year amounts. Below is an example using Adventure Works (I'm using 2007, different version, instead of 2002) that demonstrates how to do so.

    WITH SET CDate as
    	Descendants(
    		[Date].[Calendar].[Month].&[2007]&[5],
    		[Date].[Calendar].[Date]
    	)
    MEMBER [Measures].[Prev Day Internet Sales Amount] AS
    	( [Date].[Date].PrevMember, [Measures].[Internet Sales Amount] ), 
    	FORMAT_STRING="Currency"
    MEMBER [Measures].[Prev Month Internet Sales Amount] AS
    	( 
    		ParallelPeriod([Date].[Calendar].[Month], 1, [Date].[Calendar].CurrentMember),
    		[Measures].[Internet Sales Amount]
    	),
    	FORMAT_STRING="Currency"
    MEMBER [Measures].[Prev Year Internet Sales Amount] AS 
    	( 
    		ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember),
    		[Measures].[Internet Sales Amount]
    	),
    	FORMAT_STRING="Currency"
    SELECT	{
    		[Measures].[Prev Year Internet Sales Amount],
    		[Measures].[Prev Month Internet Sales Amount],
    		[Measures].[Prev Day Internet Sales Amount],
    		[Measures].[Internet Sales Amount]
    	} ON COLUMNS,
    	{   
    		[CDate]
    	} ON ROWS  
    FROM	[Adventure Works]
    

    HTH, Martin

    <a href="http://martinsbiblog.spaces.live.com" target="_blank">http://martinmason.wordpress.com</a>

    Saturday, August 24, 2013 8:58 PM

All replies

  • It is resolved......

    WITH set CName as
    [Date].[Date].members
    select
    {  CName } on columns
    from [Adventure Works]
    WHERE ([Date].[Calendar].[Month].&[2002]&[5])

    • Marked as answer by Ravi.Kumar Saturday, August 24, 2013 2:20 PM
    • Unmarked as answer by Ravi.Kumar Saturday, August 24, 2013 2:26 PM
    Saturday, August 24, 2013 2:16 PM
  • Here, I want to know PreviousMember of May 2002

    WITH set CDate as
    [Date].[Date].members
    set CPerviousDate as
    CDate.PrevMember
    select
    {   CPerviousDate } on columns
    from [Adventure Works]
    WHERE ([Date].[Calendar].[Month].&[2002]&[5],[Measures].[Internet Sales Amount])

    Saturday, August 24, 2013 2:28 PM
  • PrevMember function applies to a member, not a set. It will return the previous member in the same level as the member. What I believe you're trying to do is return back current days amounts along with a comparison to yesterdays. To do so, you can create a query scoped calculated measure that overwrites the current date context using the PrevMember function. If you use a Descendants function and the [Date].[Calendar] user navigable hierarchy, you can also use other functions, such as the ParallelPeriod function to return back a comparison to previous month and previous year amounts. Below is an example using Adventure Works (I'm using 2007, different version, instead of 2002) that demonstrates how to do so.

    WITH SET CDate as
    	Descendants(
    		[Date].[Calendar].[Month].&[2007]&[5],
    		[Date].[Calendar].[Date]
    	)
    MEMBER [Measures].[Prev Day Internet Sales Amount] AS
    	( [Date].[Date].PrevMember, [Measures].[Internet Sales Amount] ), 
    	FORMAT_STRING="Currency"
    MEMBER [Measures].[Prev Month Internet Sales Amount] AS
    	( 
    		ParallelPeriod([Date].[Calendar].[Month], 1, [Date].[Calendar].CurrentMember),
    		[Measures].[Internet Sales Amount]
    	),
    	FORMAT_STRING="Currency"
    MEMBER [Measures].[Prev Year Internet Sales Amount] AS 
    	( 
    		ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember),
    		[Measures].[Internet Sales Amount]
    	),
    	FORMAT_STRING="Currency"
    SELECT	{
    		[Measures].[Prev Year Internet Sales Amount],
    		[Measures].[Prev Month Internet Sales Amount],
    		[Measures].[Prev Day Internet Sales Amount],
    		[Measures].[Internet Sales Amount]
    	} ON COLUMNS,
    	{   
    		[CDate]
    	} ON ROWS  
    FROM	[Adventure Works]
    

    HTH, Martin

    <a href="http://martinsbiblog.spaces.live.com" target="_blank">http://martinmason.wordpress.com</a>

    Saturday, August 24, 2013 8:58 PM