Create dynamic leaf level subsets

Subsets (Set) in Planning Analytics are an analysts friend and one of the simplest ones is the all leaf (aka level 0 / all N/S) elements variety.

For example, you can use this to build a trial balances for a finance model, generate a stocktake summary for an inventory model and more.

A lot of the times, analysts don’t realise you can make these dynamic so they update as the related dimension changes ie if you add a new GL account, it automatically appears in the subset.

Here is how to build a dynamic one using the PAW interface

1. Right-click the dimension

2. Select Create Set
   (Set editor opens)

3. Click the MDX button
   (positioned next to the cog icon on the right of the screen)

4. Paste the following MDX code into the MDX code window and replace
   (Overwrite anything that may be there already)

{TM1SORT(TM1FILTERBYLEVEL(TM1SUBSETALL([<Dimension>]) , 0) , ASC)}

5. Replace <Dimension> with your dimension name in the MDX code

6. Click the Commit button to test it out

7. Click the Save as button
(bottom right)

8. In the Save box, enter the name for the set and click Save

You can now use the subset via any of the interfaces (PAW, Spreadsheet Services, Planning Analytics for Excel or even Perspectives) and it will automatically reflect any dimension changes to leaf level elements.