mdx statement to determine parent from parallel hierarchy

Post Reply
DanielaD
Posts: 3
Joined: Thu Jun 20, 2024 8:29 am
OLAP Product: TM1
Version: PA Local 2.0.9.18
Excel Version: Office 365

mdx statement to determine parent from parallel hierarchy

Post by 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
MVP
Posts: 1821
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: mdx statement to determine parent from parallel hierarchy

Post by 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.
Declan Rodger
DanielaD
Posts: 3
Joined: Thu Jun 20, 2024 8:29 am
OLAP Product: TM1
Version: PA Local 2.0.9.18
Excel Version: Office 365

Re: mdx statement to determine parent from parallel hierarchy

Post by DanielaD »

This works! Thank you a lot or your help! :D

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
MVP
Posts: 3673
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: mdx statement to determine parent from parallel hierarchy

Post by lotsaram »

DanielaD wrote: Mon Jun 24, 2024 1:20 pm 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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
DanielaD
Posts: 3
Joined: Thu Jun 20, 2024 8:29 am
OLAP Product: TM1
Version: PA Local 2.0.9.18
Excel Version: Office 365

Re: mdx statement to determine parent from parallel hierarchy

Post by DanielaD »

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