Return parent from cubemember

[Jesse]

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

[Will Riley]

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

[Jesse]

Awesome, thanks for the help Will.

Jesse

[Jesse]

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

[Ken Puls]

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...

[Will Riley]

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

[Will Riley]

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

[Will Riley]

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

[Will Riley]

(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

[Will Riley]

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.