mdx statement to determine parent from parallel hierarchy

[DanielaD]

Hi, I need some help with an MDX statement in a subset.
I have already a mdx based subset which shows cost center based on a selection in a web sheet a user can do.

Result are N elements:
AACC/515345
AACC/D515489
AACC/515365

used mdx statement:

TM1FILTERBYLEVEL(DESCENDANTS(NAMETOSET("[Function_M].[" + [zzz_nav_Application].(STRTOMEMBER("[OL_S_Cognos_User].[" + USERNAME + "]"),[zzz_nav_Application].[M],[zzz_nav_Application_object].[Function_M],[zzz_nav_Application_measure].[fav03]) + "]")) , 0)

Based on this list, I need to find now the parent of the N elements from a parallel hierarchy, so not the hierarchy which was selected in the Websheet.
In these parallel hierarchy, the parents always start with "P" + the 6 numbers of the N element from the right.

This means in my example, the outcome should be:

P515345 -> C element, parent of AACC/515345
P515489 -> C element, parent of AACC/D515489
P515365 -> C element, parent of AACC/515365

I already tried with ".ancestors" but this functions only brings the parents of the original selected hierarchy.

I hope someone can help.

[declanr]

The below code concept should work for you.

Code: Select all

Generate (
	{TM1SubsetToSet([<Dim1>].[<Hier1>] , "N_Level_Set" , "Public")},
	{StrToMember ( "[<Dim2>].[<Hier2>].[P" + Right ( [<Dim1>].[<Hier1>].CurrentMember.Properties("Member_Name"), 6 ) + "]" )}
) 

Generate will loop through a list of elements and apply an action.

The list of elements is defined in the first part of the function:

Code: Select all

	{TM1SubsetToSet([<Dim1>].[<Hier1>] , "N_Level_Set" , "Public")},

So this assumes you have saved the MDX which finds the "N-Level" list as a subset. And would require you to tell it the dimension name, hierarchy name and subset name. But you could actually replace this whole section with the MDX that was used to define your first list if you don't have it saved down as a subset.


Then for every element in that subset it would loop through and perform the second action where it tries to return a "Member Unique Name" (which is how MDX defines an element:

Code: Select all

	{StrToMember ( "[<Dim2>].[<Hier2>].[P" + Right ( [<Dim1>].[<Hier1>].CurrentMember.Properties("Member_Name"), 6 ) + "]" )}

So again for this part you would need to specify the Dimension Name and Hierarchy name that you want the results in (<Dim2> and <Hier2>). The <Dim1> and <Dim2> should refer to where the n-level subset is defined.
I think from your post that <Dim1> and <Dim2> are probably the same dimension.

I'm not certain when you mention "Parallel Hierarchy" whether you are referring to "Named Hierarchies" in Planning Analytics; or simply different rollups in the same hierarchy... but the code would actually work in both instances anyway.

[DanielaD]

This works! Thank you a lot or your help!

Just for your information, if I talk about parallel hierarchies I mean diffrent rollups in the same hierarchy.
We currently don't use "Named Hierarchies".

[lotsaram]

Just for your information, if I talk about parallel hierarchies I mean diffrent rollups in the same hierarchy.

Then that's what you should say!. Rollups are the tree structures within a hierarchy defined by parent/child relationships and element weightings.

A hierarchy may contain multiple root members (but things work better if there's just a single root and each member has only a single parent ... however, this isn't always going to be possible.)

A dimension is just a container for hierarchies and used to define cubes.

[DanielaD]

I will keep it in mind. Thanks for the explanation!