Excellent - Glad to hear it - gives you some more time for your weekend too.
Original Message:
Sent: Fri July 26, 2024 10:46 AM
From: Adam Hubbard
Subject: MDX to remove a member level
That works a treat, thanks George, it will be useful in many situations.
Have a great weekend
Adam
------------------------------
Adam Hubbard
Original Message:
Sent: Fri July 26, 2024 10:38 AM
From: George Tonkin
Subject: MDX to remove a member level
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.
------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg
Original Message:
Sent: Fri July 26, 2024 10:15 AM
From: Adam Hubbard
Subject: MDX to remove a member level
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
------------------------------
Adam Hubbard
Original Message:
Sent: Fri July 26, 2024 10:05 AM
From: George Tonkin
Subject: MDX to remove a member level
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?
------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg
Original Message:
Sent: Fri July 26, 2024 09:05 AM
From: Adam Hubbard
Subject: MDX to remove a member level
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
![](https://dw1.s81c.com//IMWUC/MessageImages/5655a5fb5d3f4f359daf4a25fcfde340.png)
------------------------------
Adam Hubbard
------------------------------