none
MDX Leaves and Parents RRS feed

  • Question

  • Hai,

     

    Select
    Generate
     (({DESCENDANTS([Organisation].[Organisation],,LEAVES)},
     {[Organisation].[Organisation].CURRENTMEMBER.PARENT,
     [Organisation].[Organisation].CURRENTMEMBER}) ,
     InStr(1, [Organisation].[Organisation].CurrentMember.Properties("MEMBER_CAPTION"), "Teams", 1) =1)
     Dimension Properties [Organisation].[Organisation].[MEMBER_TYPE] On Columns
     From [Cubed_Org]  Cell Properties VALUE

    I want the leaves with their parents in a ragged dimension. Also only that with the word "Teams" inside. The error now is:
    The Organisation hierarchy is used more than once in the Crossjoin function. Can you help me with this mdx? Thanks.

    Friday, November 30, 2018 7:12 AM

All replies

  • The following approach which unions the members with the next level up should work. Note that your filter condition only returns members where the name starts with "Teams" (not contains "Teams") for contains you'd need to change the Instr check to be >= instead of just =. Also note that doing an InStr filter on member captions may not perform all that well if your dimension is large. If this sort of filter is a common requirement you might want to consider adding an attribute to your dimension so that you can then use the EXISTS function against that attribute instead. 

    SELECT
    HIERARCHIZE(
      FILTER(
        UNION(
           DESCENDANTS([Organisation].[Organisation],,LEAVES),
            DRILLUPLEVEL( 
    DESCENDANTS([Organisation].[Organisation],,LEAVES) )
        )
        ,  InStr(1, [Organisation].[Organisation].CurrentMember.Properties("MEMBER_CAPTION"), "Teams", 1) =1)
      )
    ) On Columns
    FROM [Cubed_Org]


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Pirlo Zhang Monday, December 3, 2018 2:17 AM
    Friday, November 30, 2018 11:20 PM
    Moderator
  • Hai,

    Thanks for the mdx code. It works for a part, the children have always the word Teams as alias but the parents off them not. So how can I solve that?

    Monday, December 3, 2018 11:22 AM
  • Sorry I was assuming from the structure of your original example that you only wanting members in the last 2 levels with the work "team" in the name.

    So to fix this you could move the FILTER() call and just wrap both of the calls to DESCENDANTS() in the team filter. Or to avoid doing that expensive operation twice you could create a named set and then reference that.

    eg.

    WITH SET [LeafTeams] AS
      FILTER(
           DESCENDANTS([Organisation].[Organisation],,LEAVES),
        ,  InStr(1, [Organisation].[Organisation].CurrentMember.Properties("MEMBER_CAPTION"), "Teams", 1) =1)
    SELECT

    HIERARCHIZE(
        UNION(
           [LeafTeams],
            DRILLUPLEVEL( 
    [LeafTeams] )
      )
    ) On Columns
    FROM [Cubed_Org]


    http://darren.gosbell.com - please mark correct answers

    Monday, December 3, 2018 7:31 PM
    Moderator
  • Hai,

    Thanks for de mdx code, but it seems this query also uses the filter Instr() in the union. I still don't get the parents of the leaves with "team" in the caption.

    Thursday, December 6, 2018 12:56 PM
  • So what exactly is your requirement? 

    If it/s leaves with a parent that has a caption starting with the word "Team" then the following should work:

    WITH SET [LeafTeams] AS
      FILTER(
           DESCENDANTS([Organisation].[Organisation],,LEAVES),
        ,  InStr(1, [Organisation].[Organisation].CurrentMember.Parent.Properties("MEMBER_CAPTION"), "Teams", 1) =1)
    SELECT

    HIERARCHIZE(
        UNION(
           [LeafTeams],
            DRILLUPLEVEL( 
    [LeafTeams] )
      )
    ) On Columns
    FROM [Cubed_Org]


    http://darren.gosbell.com - please mark correct answers

    Thursday, December 6, 2018 7:18 PM
    Moderator
  • Hai,

    An example off my organisation structure, parent-child dimension:

    Total > parent

    Total 1> child under Total

    Teams 1> child under Total 1

    Teams 2> child under Total 1

    So I'll see the children with Teams in there name (the leaves, so Team 1 and Team 2) and the parent off them and that is Total 1.


    • Edited by MYMY2 Friday, December 7, 2018 8:36 AM
    Friday, December 7, 2018 8:36 AM
  • Hai,

    An example off my organisation structure, parent-child dimension:

    Total > parent

    Total 1> child under Total

    Teams 1> child under Total 1

    Teams 2> child under Total 1

    So I'll see the children with Teams in there name (the leaves, so Team 1 and Team 2) and the parent off them and that is Total 1.

    So in theory that is what the second answer I posted is supposed to do, but reading through the documentation on DrillUpLevel I'm not sure if it will work correctly on a ragged parent/child hierarchy.

    The other approach would be to use the generate() function to iterate through the teams and get the parent members.

    eg.

    WITH SET [LeafTeamsAndParents] AS
    HIERARCHIZE(
      GENERATE(
        FILTER(
             DESCENDANTS([Organisation].[Organisation],,LEAVES),
          ,  InStr(1, [Organisation].[Organisation].CurrentMember.Properties("MEMBER_CAPTION"), "Teams", 1) =1)
        , UNION( { 
    [Organisation].[Organisation].CurrentMember }
                      , { 
    [Organisation].[Organisation].CurrentMember.Parent } )  )
    )
    SELECT

           [LeafTeamsAndParents]
    ) On Columns
    FROM [Cubed_Org]


    http://darren.gosbell.com - please mark correct answers

    Tuesday, December 11, 2018 11:06 PM
    Moderator