Get childs of all parent elements from existing subset via mdx

[VKTech]

Hi All,
I am trying to get childs of all parent level elements in epilog via mdx. I coudnt find any direct function for it.
initaially parent level elements are inserted in data tab already via source file, requiremnet is to drill dowm the same subset there by including only childs of those parents into existing subset. Note that Parent level elements already addded in data tab before.

can you help here?

Regards,
VKTech

[burnstripe]

You could use generate function to loop through all the parents listed in the subset, to drill down to get the children. I'm not at a pc today so can't give an example til later in the week. Regarding children, are you looking for just

1) the direct children of those parents
2) n (lowest) level elements of those parents
3) something else

[VKTech]

At first thanks for your revert!
This parent is at level 1 , I just need to drill next 0 level child's so it can be zeroed out later.... So yes direct children of parents..

[burnstripe]

Below is an example. I have a period dimension with a hierarchy financial year which has periods rolling into a year. Multiple years (consolidations) are listed in a public subset called "Years".

The code below returns the children of each of those years listed in the subset

Code: Select all

DISTINCT(
	GENERATE(
		TM1SubsetToSet([Period].[Financial Year] , "Years" , "public") 
		, [Period].[Financial Year].CURRENTMEMBER.CHILDREN
	)
)

Broken down this is:

Return the list of elements within the subset

Code: Select all

TM1SubsetToSet([Period].[Financial Year] , "Years" , "public")

Loop through each of those subset elements and return the immediate children

Code: Select all

	GENERATE(
		TM1SubsetToSet([Period].[Financial Year] , "Years" , "public") 
		, [Period].[Financial Year].CURRENTMEMBER.CHILDREN
	)

Remove any potential duplications in list

Code: Select all

DISTINCT(
	GENERATE(
		TM1SubsetToSet([Period].[Financial Year] , "Years" , "public") 
		, [Period].[Financial Year].CURRENTMEMBER.CHILDREN
	)
)

[gtonkin]

Another potential method is to access the members in the set and then get their children:

Code: Select all

TM1SubsetToSet([Period].[Financial Year] , "Years" , "public").ITEM(0).ITEM(0).CHILDREN

You need Item(0) twice to reference the inner and outer sets. Omitted the Distinct() but add per Burnstripe's post if necessary.

And maybe a bit more of an explanation from ChatGPT on this as this method is quite useful when trying to use a sets as the basis for various use cases.


In your MDX expression, the need to use .ITEM(0) twice arises from how TM1SubsetToSet generates nested structures. Here’s why:

Breakdown:
TM1SubsetToSet([Period].[Financial Year], "Years", "public"):

This function returns a set of members from the "Years" subset within the [Period].[Financial Year] hierarchy.
The result is a set of members that could contain multiple elements, which is why you need to reference the first element of the set using .ITEM(0).
.ITEM(0) (first occurrence):

This selects the first element of the set returned by TM1SubsetToSet. Since a set can contain multiple elements, you're essentially picking the first one here.
However, this first element may itself be a tuple or a nested set, depending on the TM1 context.
.ITEM(0) (second occurrence):

After selecting the first element (which may still be a nested set or tuple), you need to reference the first item within that tuple/set as well. This is why you call .ITEM(0) again to access that specific member.
.CHILDREN:

Finally, after you’ve drilled down to the specific member you’re interested in, you can apply .CHILDREN to retrieve the children of that member.

Summary:
The double use of .ITEM(0) occurs because the TM1SubsetToSet returns a set of sets or a set of tuples. You need to access the first element of the outer set and then further drill down to the first element within that subset. Only after this double reference do you get to the specific element (likely a year) on which you can call .CHILDREN.

[VKTech]

Thank you for your revert !

[VKTech]

Thanks a lot for your time on helping on this!