Have you ever needed to have a TM1 subset that automatically includes all elements? What, I hear you say, doesn’t that always occur? Well, no. You can have static subsets (which is the default), or you can make dynamic subsets using MDX. And it’s easy!
How to Create Dynamic Subsets using MDX
Open Subset editor
Turn on View, Expression Window
Tools, Record Expression
Manipulate elements to what you want
Tools, Stop Recording
Answer Yes to Attach the Expression to the Subset?
Save Subset
Example of a Dynamic Subset
I have a Unit of Measure dimension. I want to have a subset that has all the level 0 elements, sorted alphabetically. I want this subset to always have all level 0 elements (thus it must be dynamic).
- Open the Subset Editor for the Unit of Measure dimension
- Start the expression recording.
- Click on the “All” button to show all elements in the dimension.
- Click on the Filter by Level button and choose level 0.
- Click on the Sort Ascending button.
- Stop expression recording
- Attach the expression to the subset
- Save as a Public subset called “Base Level” or “Level 0” or “L 0”, basically whatever your subset naming conventions are.
The resulting expression attached to the subset is:
{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Unit of Measure] )}, 0)}, ASC)}
