Skip navigation.
spilling the beans

MDX

Filter, by values, strings and attributes

developers
| |

The FILTER function is used to filter the dimension based on some kind of data values rather than just the members and their hierarchy on their own. This data might be cube data (numeric or string) or attribute data. This requires a change of thinking from straightforward single dimensions (lists with a hierarchy and occasionally some attributes) to a multi-dimensional space, where every dimension in these cubes must be considered and dealt with.


TopCount and BottomCount

developers
| |

A classic Top 10 command:
{ TOPCOUNT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, 10, [Test].([Posting Measures].[Amount]) )}
By omitting a sort order it sorts in the default order (which has the values descending in value and breaks any hierarchies present).


TM1Sort, TM1SortByIndex and Order

developers
| |

TM1Sort is the equivalent of pressing one of the two Sort Ascending or Sort Descending buttons in the subset editor – i.e. sort alphabetically.

TM1SortIndex is the equivalent of pressing one of the two Sort by index, ascending or Sort by index, descending buttons in the subset editor – i.e. sort by the dimension index (dimix).


Select by Level, Regular Expression (Pattern) and Ordinal

developers
|

Selecting members based on their level in the dimension hierarchy (TM1FilterByLevel) or by a pattern of strings in their name (TM1FilterByPattern) can be seen easily by using the Record Expression feature in the subset editor.


TM1SubsetAll, Members, member range

developers
|

The basis for many queries, this returns (almost, see below) the entire dimension, which is the equivalent of clicking the ‘All’ button in the Subset Editor.
TM1SUBSETALL( [Product] )


Syndicate content