Results 1 to 10 of 13

Thread: Return parent from cubemember

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Return parent from cubemember

    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,344
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    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!

Posting Permissions

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