Planning Analytics

Hi,

Hopefully a simple one for the experts (I am not yet).

Is there an MDX phrase that could remove a specific level of a member hierarchy, please see image

Thanks

Adam

Hi Adam,

Multiple approaches come to mind to do this like using Descendants() on Store Property then doing an Except() of the children of Store Property.

If you only need Store Property and the leaves, ignoring any intermediate levels, there would be a different approach.

Can you give further insights into what you are doing and need and how flexible this needs to be in terms of replicating the same logic for each member in the set?

Hi,

Hopefully a simple one for the experts (I am not yet).

Is there an MDX phrase that could remove a specific level of a member hierarchy, please see image

Thanks

Adam

Hi George,

Thanks for the swift response.

The picture is just an example, there are a few main subtotals eg Store Payroll, Store Property, Store Operations (let's call that level1) and they all have children (level2) and then they also have a child that is a direct 1 to 1 of the parent (level 3) so level 2 is superfluous because level 3 has a better description of the GL code.

So I am trying to leave out level 2 for my subset.

Does that help at all? Or clear as mud?

Thanks

Adam

Hi Adam,

Multiple approaches come to mind to do this like using Descendants() on Store Property then doing an Except() of the children of Store Property.

If you only need Store Property and the leaves, ignoring any intermediate levels, there would be a different approach.

Can you give further insights into what you are doing and need and how flexible this needs to be in terms of replicating the same logic for each member in the set?

Hi,

Hopefully a simple one for the experts (I am not yet).

Is there an MDX phrase that could remove a specific level of a member hierarchy, please see image

Thanks

Adam

You could try something like this:

GENERATE(  {TM1SubsetToSet([REP Cost Element].[PL Mapping] , "Store Costs (OH)")},  {    [REP Cost Element].[PL Mapping].CurrentMember,    Descendants([REP Cost Element].[PL Mapping].CurrentMember,99,LEAVES)  })

Essentially the Generate will loop through each item in the set, keep that item and then all leaves that are descendants of that item.
So mechanically, it will always show the item and related leaves, nothing in between, whether 10 levels or no levels.

Hi George,

Thanks for the swift response.

The picture is just an example, there are a few main subtotals eg Store Payroll, Store Property, Store Operations (let's call that level1) and they all have children (level2) and then they also have a child that is a direct 1 to 1 of the parent (level 3) so level 2 is superfluous because level 3 has a better description of the GL code.

So I am trying to leave out level 2 for my subset.

Does that help at all? Or clear as mud?

Thanks

Adam

Hi Adam,

Multiple approaches come to mind to do this like using Descendants() on Store Property then doing an Except() of the children of Store Property.

If you only need Store Property and the leaves, ignoring any intermediate levels, there would be a different approach.

Can you give further insights into what you are doing and need and how flexible this needs to be in terms of replicating the same logic for each member in the set?

Hi,

Hopefully a simple one for the experts (I am not yet).

Is there an MDX phrase that could remove a specific level of a member hierarchy, please see image

Thanks

Adam

That works a treat, thanks George, it will be useful in many situations.

Have a great weekend

Adam

You could try something like this:

GENERATE(  {TM1SubsetToSet([REP Cost Element].[PL Mapping] , "Store Costs (OH)")},  {    [REP Cost Element].[PL Mapping].CurrentMember,    Descendants([REP Cost Element].[PL Mapping].CurrentMember,99,LEAVES)  })

Essentially the Generate will loop through each item in the set, keep that item and then all leaves that are descendants of that item.
So mechanically, it will always show the item and related leaves, nothing in between, whether 10 levels or no levels.

Hi George,

Thanks for the swift response.

The picture is just an example, there are a few main subtotals eg Store Payroll, Store Property, Store Operations (let's call that level1) and they all have children (level2) and then they also have a child that is a direct 1 to 1 of the parent (level 3) so level 2 is superfluous because level 3 has a better description of the GL code.

So I am trying to leave out level 2 for my subset.

Does that help at all? Or clear as mud?

Thanks

Adam

Hi Adam,

Multiple approaches come to mind to do this like using Descendants() on Store Property then doing an Except() of the children of Store Property.

If you only need Store Property and the leaves, ignoring any intermediate levels, there would be a different approach.

Can you give further insights into what you are doing and need and how flexible this needs to be in terms of replicating the same logic for each member in the set?

Hi,

Hopefully a simple one for the experts (I am not yet).

Is there an MDX phrase that could remove a specific level of a member hierarchy, please see image

Thanks

Adam