MDX question about "TM1FILTERBYLEVEL"

[Ajay]

Hi All

I'm relatively new to MDX so apologies for asking what maybe a daft question.

I have the following code in the "Prolog" tab within a TI process:


# Create 'Default' subset in 'Customers' dimension
#----------------------------------------------------------------------------
SUBSETCREATEBYMDX ( 'Default', '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customers] )}, 1)}' );


My question relates to whether it is possible to change the <level_number> from "1" to always pick the highest level, as the lower nodes can vary ?

Thanks in advance
Ajay

[qml]

You won't be able to do it using TM1FILTERBYLEVEL and doing this in pure MDX would be quite complex.

I'd reccomend creating an attribute (numeric or text) on that dimension, that would e.g. have 1 for those elements that are top level nodes. Then you simply apply a filter based on that attribute:

Code: Select all

{FILTER( {TM1SUBSETALL( [Customers] )}, [Customers].[Attribute] = 1)}

Now, you could either maintain this attribute manually, or, better yet, create a rule on the attribute cube that would do it for you:

Code: Select all

['Attribute'] = S: IF (ELPARN ('Customers', !Customers) = 0, 1, 0);

[lotsaram]

I disagree with qml I think you can do it quite easily using the DNLev function to return the depth / number of levels in a dimension.

Code: Select all

nLevels = DNLev('Customers') - 1;
SUBSETCREATEBYMDX ( 'Default', '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customers] )}, ' | nLevels  | ')}' );

However if you have multiple hierarchies and the hierarchies have different depths then this won't necessarily give you what you want. If you want MDX to return all "root" elements (all elements at the top of hierarchies that don't have parents themselves) then you can find the answer here. http://www.tm1forum.com/viewtopic.php?f=3&t=6293#p26022

[qml]

If you want MDX to return all "root" elements (all elements at the top of hierarchies that don't have parents themselves)

This is what I took the following line to mean:

pick the highest level, as the lower nodes can vary

[lotsaram]

Ahh I thought you were replying to Ajays original question about whether the top level number could be automatically done. On reading more carefully I see we were on the same wavelength, but you don't need an additional attribute or rule as you can refer to CurrentMember.Parent.Name="" directly in MDX to get the same result.

[qml]

Agree, no need to complicate it with that attribute after all.

[Ajay]

Thanks Guys

Some interesting points.

I've just copied the following into my TI process but keep getting a syntax error telling me there is an invalid operator at nLevels |'.....any ideas:

nLevels = DNLev('Customers') - 1;
SUBSETCREATEBYMDX ( 'Default', '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customers] )}, ' | nLevels | ')}' );

Regards
Ajay

[lotsaram]

You need to wrap nLevels in NumberToString.