Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Return parent from cubemember

  1. #1

    Return parent from cubemember



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Is there a way to return the parent from a cubemember?

    Ie I have store locations that are referenced via:

    =cubemember("Sales","[Region].[District].[Store].&[10]")

    Which returns the store name for location number 10. I want to return the District name for store number 10.

    Jesse

  2. #2
    Seeker Will Riley's Avatar
    Join Date
    Mar 2011
    Location
    Solihull, United Kingdom
    Posts
    8
    Articles
    0
    Try this

    =cubemember("Sales","[Region].[District].[Store].&[10].Parent")

    The Parent function will (as it suggests) the parent of a specified member from the specified hierarchy, in your case Region.

    Hope this helps.

    Will
    Hope this helps,

    Will

  3. #3
    Awesome, thanks for the help Will.

    Jesse

  4. #4
    When I do this it returns "STORE" as opposed to the district name which is what I'm looking for.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    I'm curious... my cube knowledge is pretty close to nothing, but what about...
    =cubemember("Sales","[Region].[District].[Store].&[10].Parent.Parent")

    or is there a property that you can tag with the parent, like as

    =cubemember("Sales","[Region].[District].[Store].&[10].Parent.Parent.&[Name]")

    Totally guessing, but that's what I'd try... Course I'd probably fail too, but...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Seeker Will Riley's Avatar
    Join Date
    Mar 2011
    Location
    Solihull, United Kingdom
    Posts
    8
    Articles
    0
    Hmmm, This could have something to do with the limited support for hierarchies in Powerpivot. The function syntax I gave you was based on my "upbringing" with MDX and SSAS cubes and is the correct way to navigate a dimension hierarchy in MDX. If we have the AdventureWorks SSAS cube, we can have =CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3]&quot This gives us a value of "Central" Using the following formula =CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3].Parent&quot We return "United States" which is the correct parent for "Central" (FYI Ken, the following formula =CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3].Parent.Parent&quot will return "North America", the parent of "United States" ) If I get the time today, I'll explore this with Powerpivot (which is I assume what you are using?) - I remember some colleagues in the PPVT world commenting on a lack of support for hierarchies so this might be the sort of thing they were referring to! Cheers, Will
    Last edited by Will Riley; 2011-04-01 at 08:20 AM. Reason: weird rendering of my post!

  7. #7
    Seeker Will Riley's Avatar
    Join Date
    Mar 2011
    Location
    Solihull, United Kingdom
    Posts
    8
    Articles
    0
    Ken... not sure what went wrong with the above rendering... looked fine until I posted it but the formatting seems to get all screwed? Seemed to be working fine yesterday

  8. #8
    Seeker Will Riley's Avatar
    Join Date
    Mar 2011
    Location
    Solihull, United Kingdom
    Posts
    8
    Articles
    0
    Quote Originally Posted by Will Riley View Post
    Ken... not sure what went wrong with the above rendering... looked fine until I posted it but the formatting seems to get all screwed? Seemed to be working fine yesterday
    Edit: Yeah... now it never posts as per the WYSIWYG editor .... I tried FF4, IE8 & 9 and same results

  9. #9
    Seeker Will Riley's Avatar
    Join Date
    Mar 2011
    Location
    Solihull, United Kingdom
    Posts
    8
    Articles
    0
    (Ken... this is using the standard text editor as opposed to WYSIWYG - maybe a bug?)

    Hmmm,

    This could have something to do with the limited support for hierarchies in Powerpivot.

    The function syntax I gave you was based on my "upbringing" with MDX and SSAS cubes and is the correct way to navigate a dimension hierarchy in MDX.

    If we have the AdventureWorks SSAS cube, we can have

    =CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3]")

    This gives us a value of "Central"

    Using the following formula

    =CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3].Parent")

    We return "United States" which is the correct parent for "Central"

    (FYI Ken, the following formula =CUBEMEMBER(". Adventure Works DW 2008R2 Channel Sales","[Sales Territory].[Sales Territory].[Region].&[3].Parent.Parent") will return "North America", the parent of "United States" )

    If I get the time today, I'll explore this with Powerpivot (which is I assume what you are using?) - I remember some colleagues in the PPVT world commenting on a lack of support for hierarchies so this might be the sort of thing they were referring to!

    Cheers,

    Will

  10. #10
    Seeker Will Riley's Avatar
    Join Date
    Mar 2011
    Location
    Solihull, United Kingdom
    Posts
    8
    Articles
    0
    Well, I tried to do this in PowerPivot and it seems you can't (at least not using the Parent MDX function)

    Reading around various posts on the interweb, it seems that there is no inherent hierarchy support in PPVT like there is in SSAS, so even though dragging related attributes from a PPVT table or related tables (Country, Region, City etc) does "appear" to give the same visual hierarchy effect, the fact is that under the covers, there is no hierarchy established between say Country & Region, it merely places them in the grid in a one-many left to right order so you get the same effect.

    Therefore, the CUBEMEMBER function, has no idea that Country is a parent of City, as with Powerpivot, such a hierarchy is not supported.

    Until Denali and the new BISM designer, where you will be able to author PPVT and SSAS from the same semantic model, I think you're stuffed. I'll keep playing, but i'm not sure you'll be able to do this easily with a single function, which is a real shame.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •