MDX Filter by level 1 and not level 0

[bogdan]

Hi, this is an MDX for TM1 subset create question.
We want display level 1 and remove level 0 rom the subset.

Many thanks

[declanr]

Normally for MDX I would recommend writing it yourself and in that case the TM1 MDX Primer document (which can be found by searching this forum or google) would be your first port of call.
But for a simple TM1FilterByLevel you may as well just use the "record expression" option in the subset editor and create it using the wizard. Just record it like you can a macro in excel, go through the steps that you would to manually select only level 1 elements i.e. Expand all => Filter by level. Then stop recording and save your subset with the expression included.

[bogdan]

What would I need to display only the elements of level 1 and it was not possible to choose the elements of level 0 .

I am having an dimension with the following structure

-aaa
| - a11
| - a12
| - a12
-bbb
| - b14
| - b21

I need what would happen

aaa
bbb

[rmackenzie]

We want display level 1 and remove level 0 rom the subset.

But for a simple TM1FilterByLevel you may as well just use the "record expression" option in the subset editor and create it using the wizard.

Recording is good to start off but you can end up with a crapola expression if you end up with the {TM1SubsetBasis} nonsense in the recording. Like here the {TM1SubsetBasis} is meaningless outside its immediate context:

Code: Select all

{TM1FILTERBYLEVEL( {TM1SubsetBasis()}, 1)}

Wherever you see {TM1SubsetBasis} it just means you can use some other MDX expression in it's place. For example, if you want all the level-1 elements in the dimension then whilst recording you would hit the All button and then filter for level 1 to return:

Code: Select all

{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Account] )}, 1)}

See how {TM1SubsetBasis} has been replaced with {TM1SUBSETALL( [Account] )} ?

If you have an existing subset that you wish to filter for level-1 elements then you can use something like this:

Code: Select all

{TM1FILTERBYLEVEL( {[Account].[YOUR_SUBSET_NAME_GOES_HERE]}, 1)}

or

Code: Select all

{TM1FILTERBYLEVEL( {TM1SUBSETTOSET( [Account], "YOUR SUBSET NAME GOES HERE" )}, 1)}

You'd use the 2nd choice if perhaps you had subsets and elements named the same in the dimension.

[bogdan]

Code: Select all

{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Account] )}, 1)}

Filters for level 1, but an opportunity to expand the child elements. And what would need cannot be displayed.

[bogdan]

How do I remove the select child elements ?

[declanr]

How do I remove the select child elements ?

So the MDX does provide what you wanted but you want the user to not have the option to expand the level 1 element?
If that's the case, that just isn't how the subset editor/cube viewer works.

You would have 2 options I can think of from the top of my head:
1/ Remove security access to the level 0 elements.
2/ Use an active form with your subset in the tm1rptrow formula and set it to not be expandable.

[rmackenzie]

That is just the way the subset editor works. The expression gives you a starting point, after which users can go on and do drilldown if they wish.

[bogdan]

And with the help of functions "HIERARCHIZE" or "EXPAND" it is impossible to exclude the child elements?

[tomok]

And with the help of functions "HIERARCHIZE" or "EXPAND" it is impossible to exclude the child elements?

As has been already explained, an MDX query will only return elements that match your query as a subset list. It does not change the underlying structure of the dimension. So, if my MDX returned just one element called "All Elements", which is a rollup of all the elements in the dimension, then there is nothing I can do to stop the user from clicking on the plus sign next to All Elements and expanding the list short of taking away their security rights to those children.