locked
How to slice results of a calculated member by the members of a hierarchy? RRS feed

  • Question

  • Dear experts,

    I am trying to define a Calculated Member such as:

    WITH MEMBER [Sales].[Sales Member] AS
    SUM
    (
      (
        [Location].[Hierarchy].[Channel].&[Direct],
        [Date].[Date].&[Orbitrary Date]
      ),
      [Measures].[Sales]
    )

    The problem is that when I am trying to do something like this in MDX:

    SELECT 
      [Sales].[Sales Member] ON 0,
      [Location].[Hierarchy].Members ON 1
    FROM [Cubix]

      I get a result that looks like a CrossJoin() between the sum of the intersection of the measure on the given Date, and every Member of the Location Hierarchy. Feels like I am missing something very important in terms of interacting with Hierarchies via MDX. How could I go about implementing said Calculated Member? I would also love to hear a basic explanation, of the principle if that's not entirely too much to ask for.

    Thank you.

    Friday, April 8, 2011 5:43 PM

Answers

  • I am assuming what you want is Sales amount against every location members filtered by date and channel(which is one the levels in hierarchy).

    An equivalent of AW is as below. If the above requirement is correct, you can do it without having SUM and including the filters in where clause.

     

    WITH MEMBER [Sales Member] AS
    --SUM
    --(
     --(
     -- [Product].[Product Categories].currentmember
     --),
     [Measures].[Internet Sales Amount]
    --)
    
    SELECT 
     [Sales Member] ON 0,
     nonempty([Product].[Product Categories].Members,[Sales Member] ) ON 1
    FROM [Adventure Works]
    WHERE 
    ([Product].[Category].&[1],[Date].[Calendar].[Month].&[2003]&[8])

     

    I would also love to hear a basic explanation, of the principle if that's not entirely too much to ask for

    What is happening is for every location members it will show the same result because it evaluates first based on channel.direct, date.orbitrarydate. if you want it evaluate it against every location member, you would have to put location.hierarchy.currentmember in the set defined for SUM. example; SUM( (location.hierarchy.currentmember, date.date.&orbitrarydate), Measure1)


    vinu
    • Proposed as answer by Erdem Zengin Sunday, April 10, 2011 5:16 PM
    • Marked as answer by Jerry Nee Monday, April 18, 2011 12:57 AM
    Friday, April 8, 2011 12:45 PM
  • Hi,

     

    Im a little unsure of whether your trying to define the member as a measure or part of a sales hierarchy but I’ll assume that it’s the a measure.

     

    You don’t have to define the sum because the measure aggregation function will take care of it.  All you have to do is define the slice (and remember that the calculations are done in the cube context).  So, the first measure (Sales1) is defined over the cube and gives the consistent value. 

     

    The formula for Sales1 could be replaced with

    ([Product].[Product Categories].[Category].&[4], [Date].[Calendar].[Calendar Year].&[2002], [Measures].[Reseller Sales Amount] )

    And we would get the same result.

     

    However, just to define the intersection 2002 and [sales amount] we can define the slice for and use that.

     

    Hope this helps.

     

    Paul

     

     

    WITH MEMBER Sales1 AS

    SUM(  ([Product].[Product Categories].[Category].&[4]

                , [Date].[Calendar].[Calendar Year].&[2002]

                )

                , [Measures].[Reseller Sales Amount]

    )

     

    MEMBER Sales2 AS

    ([Date].[Calendar].[Calendar Year].&[2002], [Measures].[Reseller Sales Amount])

     

     

    SELECT

    {Sales1, Sales2}

    ON 0,

     

    [Product].[Product Categories].[Category].MEMBERS ON 1

     

    FROM [Adventure Works]

    • Marked as answer by Jerry Nee Monday, April 18, 2011 12:57 AM
    Saturday, April 9, 2011 8:31 PM

All replies

  • Dear experts,

    I am trying to define a Calculated Member such as:

    WITH MEMBER [Sales Member] AS
    SUM
    (
     (
      [Location].[Hierarchy].[Channel].&[Direct],
      [Date].[Date].&[Orbitrary Date]
     ),
     [Measures].[Sales]
    )

    The problem is that when I am trying to do something like this in MDX:

    SELECT 
     [Sales].[Sales Member] ON 0,
     [Location].[Hierarchy].Members ON 1
    FROM [Cubix]

     

    I get a result that looks like Sales for a given Date, summed across every Location, CrossJoined() with every Member of the Location Hierarchy. Feels like I am missing something very important in terms of interacting with Hierarchies via MDX. How could I go about implementing said Calculated Member? I would also love to hear a basic explanation, of the principle if that's not entirely too much to ask for.

    Thank you.

     

     

     


    • Edited by ap3rson Friday, April 8, 2011 11:55 AM Formatting
    • Merged by Jerry Nee Monday, April 11, 2011 7:14 AM duplicate post
    Friday, April 8, 2011 11:55 AM
  • I am assuming what you want is Sales amount against every location members filtered by date and channel(which is one the levels in hierarchy).

    An equivalent of AW is as below. If the above requirement is correct, you can do it without having SUM and including the filters in where clause.

     

    WITH MEMBER [Sales Member] AS
    --SUM
    --(
     --(
     -- [Product].[Product Categories].currentmember
     --),
     [Measures].[Internet Sales Amount]
    --)
    
    SELECT 
     [Sales Member] ON 0,
     nonempty([Product].[Product Categories].Members,[Sales Member] ) ON 1
    FROM [Adventure Works]
    WHERE 
    ([Product].[Category].&[1],[Date].[Calendar].[Month].&[2003]&[8])

     

    I would also love to hear a basic explanation, of the principle if that's not entirely too much to ask for

    What is happening is for every location members it will show the same result because it evaluates first based on channel.direct, date.orbitrarydate. if you want it evaluate it against every location member, you would have to put location.hierarchy.currentmember in the set defined for SUM. example; SUM( (location.hierarchy.currentmember, date.date.&orbitrarydate), Measure1)


    vinu
    • Proposed as answer by Erdem Zengin Sunday, April 10, 2011 5:16 PM
    • Marked as answer by Jerry Nee Monday, April 18, 2011 12:57 AM
    Friday, April 8, 2011 12:45 PM
  • Hi,

     

    Im a little unsure of whether your trying to define the member as a measure or part of a sales hierarchy but I’ll assume that it’s the a measure.

     

    You don’t have to define the sum because the measure aggregation function will take care of it.  All you have to do is define the slice (and remember that the calculations are done in the cube context).  So, the first measure (Sales1) is defined over the cube and gives the consistent value. 

     

    The formula for Sales1 could be replaced with

    ([Product].[Product Categories].[Category].&[4], [Date].[Calendar].[Calendar Year].&[2002], [Measures].[Reseller Sales Amount] )

    And we would get the same result.

     

    However, just to define the intersection 2002 and [sales amount] we can define the slice for and use that.

     

    Hope this helps.

     

    Paul

     

     

    WITH MEMBER Sales1 AS

    SUM(  ([Product].[Product Categories].[Category].&[4]

                , [Date].[Calendar].[Calendar Year].&[2002]

                )

                , [Measures].[Reseller Sales Amount]

    )

     

    MEMBER Sales2 AS

    ([Date].[Calendar].[Calendar Year].&[2002], [Measures].[Reseller Sales Amount])

     

     

    SELECT

    {Sales1, Sales2}

    ON 0,

     

    [Product].[Product Categories].[Category].MEMBERS ON 1

     

    FROM [Adventure Works]

    • Proposed as answer by Erdem Zengin Sunday, April 10, 2011 4:48 PM
    Saturday, April 9, 2011 8:28 PM
  • Hi,

     

    Im a little unsure of whether your trying to define the member as a measure or part of a sales hierarchy but I’ll assume that it’s the a measure.

     

    You don’t have to define the sum because the measure aggregation function will take care of it.  All you have to do is define the slice (and remember that the calculations are done in the cube context).  So, the first measure (Sales1) is defined over the cube and gives the consistent value. 

     

    The formula for Sales1 could be replaced with

    ([Product].[Product Categories].[Category].&[4], [Date].[Calendar].[Calendar Year].&[2002], [Measures].[Reseller Sales Amount] )

    And we would get the same result.

     

    However, just to define the intersection 2002 and [sales amount] we can define the slice for and use that.

     

    Hope this helps.

     

    Paul

     

     

    WITH MEMBER Sales1 AS

    SUM(  ([Product].[Product Categories].[Category].&[4]

                , [Date].[Calendar].[Calendar Year].&[2002]

                )

                , [Measures].[Reseller Sales Amount]

    )

     

    MEMBER Sales2 AS

    ([Date].[Calendar].[Calendar Year].&[2002], [Measures].[Reseller Sales Amount])

     

     

    SELECT

    {Sales1, Sales2}

    ON 0,

     

    [Product].[Product Categories].[Category].MEMBERS ON 1

     

    FROM [Adventure Works]

    • Marked as answer by Jerry Nee Monday, April 18, 2011 12:57 AM
    Saturday, April 9, 2011 8:31 PM