wimgielis.com

MDX statements

• Mar. 24, 2024

Introduction

MDX statements are what SQL queries are to relational databases, but instead acting on OLAP (multidimensional) databases. In the case of TM1, we will use these expressions to query a dimension (hierarchy) and its members, cubes and their data points, etc. You will find out that a lot is possible if you get the syntax right.

Topics

Sources
Credits
use-cases
Referencing elements
Using an existing subset in an expression
Elements at different levels
Sorting
Descendants and Ascendants
More on hierarchical structures
Planning Analytics hierarchies
Union and Intersect
Cube values
Filtering on attributes
Double lookups
Filtering over dimensions
Filtering with wildcards
More on sorting
Generate
Subsets based on other objects
Distinct elements
Orphans and top consolidations
TM1 users and groups
Application and control objects
Temporary subsets
TM1ELLIST
IS
Count
Escape character
Caveats
Excel
MDX views

Sources

Writing MDX statements will not be that easy, certainly not in the beginning. Where can you find help ?

1. This very page you are looking at ! In all honesty and humbleness, it contains very good material.
2. This very nice overview with other practical examples by George Tonkin.
3. This high-quality overview for more details, written by Phillip Bichard and Martin Findon.
4. MDX for views written by George Tonkin (see all parts of the series).
5. Microsoft documentation on the topic. Also, see member properties and several related pages.
6. Functions in IBM DB2. Mind you, not all these functions are supported in Planning Analytics, however it may add some more information you may find useful.
7. TM1 has a 'Record expression' functionality:


Record the relevant steps, much like the Macro recorder in MS Office applications - Visual Basic for Applications or VBA for short.
8. PAW has equivalent functionality, where you could insert elements and make manipulations, then get the see/change/commit the generated MDX behind. Due to autocomplete of functions you can also see what is supported and learn from that.
9. Working with Time Related MDX Functions by George Tonkin (you need an IBM ID to access).

Credits

I already dropped the name, George Tonkin. A great number of discoveries were done by him and he shared them to me and the TM1 community via this page but also through the TM1 forum. He does a lot of effort to advance the collective knowledge on things like MDX and REST API in the TM1 community. This page would not nearly be the same without his contributions. Send him a big thank you and please make sure to share your own findings with George and myself. Countless hours (or weeks) went into collecting and testing the information below and I (we) appreciate any comment / remark / addition / error correction.

MDX use-cases

As a TM1 developer, we mainly use MDX statements in the following cases:

• to make dynamic subsets rather than static subsets. For example, a cubeview showing the sales of the customers who bought last month as well (and only those customers);
• to create custom views in a Turbo Integrator (TI) process. We will often zero out (part of) a cube, this is clearing cells at the lowest level elements in certain dimensions. This list of elements can be generated with no need to loop through the dimension ourselves.
• Active forms in Excel have dynamic row subsets, you can expand and contract consolidations. The list of elements in the rows can again be an MDX statement, even quite complex statements.

Mind the syntax, it is crucial to get that straight. If not, TM1 won‘t like it… Some of the statements can be generated in the Subset Editor: turn on the Properties Window (menu View), then choose Tools > Record Expression. Record the expression while you do the steps manually. Stop recording when you are finished.

General tips

the following high-level tips should be taken into account:

• As MDX statements lead to dynamic selections of elements, it is important to keep performance in mind. Usually it's all really quick but pay attention to large dimensions with deeply nested consolidation structures. One would argue that at least elements that are not needed in the dimension, and do not contain historical data, should be removed from the dimension.
• Try to keep the definition of MDX subsets rather condensed instead of having MDX expressions with thousands of characters. For instance, if you re-use the same code within 1 or more dynamic subsets, consider adopting a more modular approach. Create a different (public) subset with that piece of logic and use the resulting subset in other MDX statements. Refer to TM1SubsetToSet below in the text. It will certainly lead to more maintainable subsets and MDX expressions.
• Turbo Integrator could be used to set up bunches of MDX-driven subsets, if you like. No need to create/update them all manually. TI could also easily convert dynamic subsets into static subsets.

Often used MDX statements

Below, I will present you the MDX statements I need most of the time. This page will therefore serve as a kind of code library: we can copy/paste and adjust only names of cubes/dimensions/elements. You will notice [] around dimension names and element names. They could be left out, unless (for instance) the name contains a space. Then we must enclose the name with []. The expressions are most of the time case-insensitive. Except for function names, the expressions are also space-insensitive.

Referencing elements

Using an existing subset in an expression

SubsetCreatebyMDX( 'Subset by measure', '{TM1SubsetToSet([Account], [Measure].CurrentMember.Name, "public")}', 'Account' );

Elements at different levels

pDim = 'Customer';
LEVEL_MIN = 1;
LEVEL_MAX = 6;

vMDX = 'Filter( TM1SubsetAll( [%pDim%] ), StrToValue([%pDim%].CurrentMember.Properties("ELEMENT_LEVEL")) >= %LEVEL_MIN%';
vMDX = vMDX | ' AND StrToValue([%pDim%].CurrentMember.Properties("ELEMENT_LEVEL")) <= %LEVEL_MAX% )';
vMDX = Expand( 'Filter( ' | vMDX | ', [%pDim%].CurrentMember.Parent.Name = "" )' );

AsciiOutput( 'test.txt', vMDX );

We can use the concept of levels as follows. Given a simple dimension called Period_MDX like this, the following statements all work - depending on the entries in the }HierarchyProperties cube where we can give meaningful names to levels:


Filter( Descendants( [Period_MDX].[All months]), [Period_MDX].CurrentMember.Level.Ordinal < 2 )
Intersect( Descendants( [Period_MDX].[All months]), [Period_MDX].[Year].Members )
Filter( Descendants( [Period_MDX].[All months]), [Period_MDX].CurrentMember.Level.Name = "Year" ), OR:
Filter( Descendants( [Period_MDX].[All months]), [Period_MDX].CurrentMember.Level.Name = "level001" )

Filter( Descendants( [Period_MDX].[All months]), [Period_MDX].CurrentMember.Level.Ordinal = [Period_MDX].[Year].Ordinal ), OR:
Filter( Descendants( [Period_MDX].[All months]), [Period_MDX].CurrentMember.Level.Ordinal = [Period_MDX].[level001].Ordinal )


For this to work, you should refer to the picture below for the level names:





Run the little TI process to update the hierarchies in the different dimensions (every time you are done making changes to that particular cube) !

Note also this possibility:
Filter( TM1DrillDownMember( TM1SubsetAll([Period_MDX]), ALL, Recursive), StrToValue([Period_MDX].CurrentMember.Properties("LEVEL_NUMBER")) = 1)
It starts at the top of the hierarchy and goes down a level with every number increased. Level 0 equals root members, level 1 the children of the root members, etc.

Sorting

{StrToMember( "[Sales_Msr].[" +
Case [Sys_Param].([Sys_Param].[Sales amounts abbreviation], [Sys_Param_Msr].[String])
   When 'K' Then 'Value (K)'
   When 'M' Then 'Value (M)'
Else
   'Value'
End
+ "]" )}

Descendants and Ascendants

SubsetCreateByMDX( vSubset, '{TM1FilterByLevel( Descendants( [Month].[' | pMonth | ']), 0)}', 'Month' );

When hierarchies can be out of sync, that is difficult to spot check. Another helper MDX can be as follows, to identify parent segments that contain wrong descendant elements (any level deep):
Filter( Filter( TM1SubsetAll( [Customer] ), [Customer].CurrentMember.Parent.Name = "" ), Count( Filter( Descendants([Customer].CurrentMember), Instr( [Customer].CurrentMember.Name, [Customer].CurrentMember.Parent.Name ) = 0 )) > 0 )

The interesting part to notice as well is that [Customer].CurrentMember can be different following the context where it is evaluated.

More on hierarchical structures

Sometimes we require a consolidated element with all of its descendants, coupled with a different consolidation with that one's immediate children:
{ Descendants([Fct_Assumption].[Total Assumption]) } + { DrillDownLevel( [Fct_Assumption].[Subtotals]) }

An example on the tuple variant for DrillDownMember will be useful. Below we drill down on "AW", where the selection is "910-20" in the other dimension, together with "AT9210" with "910-30":
SELECT {[Nummer].[3]} ON 0, DrilldownMember( {([Postcode].[AW],[VerkoopOrganisatie].[910-20]), ([Postcode].[AT9210],[VerkoopOrganisatie].[910-30])}, { [Postcode].[AW] }) ON 1 FROM [test]

This is an example of an (asymmetric) MDX view. The possibilities are endless !

What should we do if we want to retrieve the next or previous member (index-wise) in any dimension ? The added challenge is that the current element is stored in a subset, rather than entered in a cube or as an attribute. For the solution, please refer to my posts here where I demonstrate Named levels in TM1.
{ ParallelPeriod( [Week].[Week_L0], -1, TM1Member( [Week].[Current week].Item(0), 0) )}

ParallelPeriod is actually to be understood as a lag. With a positive value, you go backwards in the dimension, index-wise. Please check out the function LastPeriods as well, elsewhere on this page.

Note that I also use the Week_L0 'level' element in the }HierarchyProperties cube. Pay attention to use e.g. 'Week_L0' if you fill out the cube cell, however, if you did not specify anything you should use level002 (within the given dimension/hierarchy). You need to refresh when edits are made to this cube, but other than that, it's a good way to make your TM1 models more dynamic and 'readable' by humans. Refreshing can be done using the TI function RefreshMDXHierarchy( '' ); for all dimensions / hierarchies, or RefreshMDXHierarchy( 'dimension or hierarchy' ); for a given dimension/hierarchy.



I used the dimension for 'Week' as an example but the above function also works on dimensions that do not have a time concept, like companies.

Notice the defaultMember element too, we will discuss it later on in the document.

For more information on Named levels, refer to: IBM documentation. The idea is also that statements like [Customer].[Country].Members or [Customer].[Country].Members work, certainly in for dimensions/hierarchies that are not ragged - here, we have a Customer dimension where a rollup exists for customers into countries (the result is the list of distinct countries).

Planning Analytics hierarchies

Since some time now we can use hierarchies in IBM Planning Analytics. Any dimension can become a container of 1 or more so called hierarchies. These hierarchies can be used in cube views and reports in an orthogonal fashion, such that one can analyze the data in a much richer way than before. The so-called Leaves hierarchy is automatically maintained by the software, containing a copy of all level 0 elements in the entire dimension (over hierarchies). Here is a dynamic subset on the }Dimensions dimension to identify all dimensions that contain hierarchies: Filter( TM1SubsetAll( [}Dimensions] ), Instr( [}Dimensions].CurrentMember.Lead(1).Name, [}Dimensions].CurrentMember.Name + ":" ) = 1 )

Union and Intersect

vDim = 'Account';

vSubset1 = 'Cost accounts';
vSubset2 = 'Filter by level 0';
vSubset3 = 'Input cost accounts';

# Create MDX
vMdx = '{Intersect( TM1SubsetToSet( [' | vDim | '], "' | vSubset1 | '" ), TM1SubsetToSet( [' | vDim | '], "' | vSubset2 | '" ) )}';

# Or, in a more readable format:
vMdx = Expand( '{Intersect( TM1SubsetToSet( [%vDim%], "%vSubset1%" ), TM1SubsetToSet( [%vDim%], "%vSubset2%" ) )}' );

SubsetCreateByMDX( vSubset3, vMDX, vDim );

Cube values

Selecting elements based on values in a cube (we do not have to specify every dimension in the cube):
{Filter( TM1FilterByLevel( TM1SubsetAll( [BS_Account] ), 0), [BalanceSheet].([FIN_Year].[2023],[FIN_Period].[P07],[FIN_Scenario].[Actual],[BS_Measures].[Amount]) <> 0)}

If we do not specify an element in a dimension of the cube, TM1 will use the element with the lowest index of all elements at the highest level in the dimension, unless we specify a defaultMember in the }HierarchyProperties control cube. So watch out and make sure this is what you need. Note that we also have a DEFAULT_HIERARCHY property in the }DimensionProperties cube.
Note that the user must have (READ) access to the cube that is used in the lookup, just as to the elements.
The defaultMember property of a dimension/hierarchy can be retrieved with:
{ [SalesPerson].DefaultMember }
The }HierarchyProperties control cube is useful to turn your TM1 models into more dynamic models which are 'readable' by developers that come after you !
Topcount, bottomcount, topsum, bottomsum, toppercent and bottompercent could be done in a similar fashion:
{TopCount( TM1FilterByLevel( TM1SubsetAll( [SalesPerson] ), 0), 5, [Sales].([Sales_Measures].[Amount]))}

A practical use-case for Order and TopCount:
{Order( TopCount( TM1FilterByLevel( TM1SubsetAll( [SalesPerson] ), 0), 10, [Sales].([Sales_Measures].[Amount])), [Sales].([Sales_Measures].[Amount]), BDesc)} Statements like TopCount or TopSum can be recorded with the interface, as well as sorting elements alphabetically:


In the above expression please take out the dreaded TM1SubsetBasis (cf. supra). By the way, I discovered a nice trick to quickly see in which cubes a dimension is used: Go to the dimension's Subset Editor, then Tools > Filter... and the dropdown will show you the cubes containing the dimension.

In Excel reports, the TM1RPTFILTER function can be used as well, to drive TopCount, BottomSum and similar results.

A practical example is an MDX statement to replace the spacer trick. This method allows you to show the next 5 open entries ('rows') for the user. Think about vacancies to budget new hires, or entering CAPEX details.



{Head( Filter( [Data Entry Row].[All Rows].Children, [Entry Cube].([Version].[Forecast], [Entry Cube Measure].[Entry]) = "" ), 5 )}

Important notes:

• This method requires a refresh of the sheet/dashboard to get the extra rows to appear (but not after every row that was input).
• For the HEAD function to work properly the element ordering and leveling in the dimension is important !
• If your Entry measure is of type Numeric, replace the double quotes with a 0 in the expression.
• You could still add [Data Entry Row].CurrentMember, in the Filter expression but you don't have to

Thank you Lotsaram

But now it gets interesting ! To the best of my knowledge, there aren't many ways to have a dynamic subset on a Months dimension return for example both a month and its year to date (YTD) counterpart. Like, "Nov" and "YTD_Nov" where "Nov" is input in a lookup cube.



This can be done, however, with similar syntax:
{Union( TM1FilterByPattern( TM1SubsetAll([Month]), [Prm_Finance].([Prm_Fin_Param].[Month of close], [Prm_Finance_Msr].[Text])), TM1FilterByPattern( TM1SubsetAll([Month]), "YTD_" + [Prm_Finance].([Prm_Fin_Param].[Month of close], [Prm_Finance_Msr].[Text])))}

Alternatively you can again use the LookUpCube function:
{Union( TM1FilterByPattern( TM1SubsetAll([Month]), LookUpCube( "Prm_Finance", "([Prm_Fin_Param].[Month of close], [Prm_Finance_Msr].[Text])" )), TM1FilterByPattern( TM1SubsetAll([Month]), "YTD_" + LookUpCube( "Prm_Finance", "([Prm_Fin_Param].[Month of close], [Prm_Finance_Msr].[Text])" )))}

Syntax 1 definitely has my vote, since it is somewhat shorter, but most of all because we avoid the double quote character ".

You can of course use Turbo Integrator to create/maintain subsets but honestly, this MDX-based dynamic solution certainly has merits, and is something I would add to TM1 models to increase the usefulness to the TM1 user community.

You could use a wildcard to cut down on the length of the expression, but beware that this could lead to additional elements in the subset which might not be wanted:
{TM1FilterByPattern( TM1SubsetAll([Month]), "*" + [Prm_Finance].([Prm_Fin_Param].[Month of close], [Prm_Finance_Msr].[Text]))}

Finally, and apologies for the spoiler to the Generate function which is discussed below:
Generate( Filter( TM1FilterByLevel( TM1SubsetAll( [Period] ), 0), [Period].CurrentMember.Properties("YTD") <> ""), {StrToMember("[Period].[" + [Period].CurrentMember.Properties("YTD") + "]")})

Here we filter out all lowest-level Period elements that have an attribute value (textual) for their YTD name. Then the MDX returns those YTD elements instead of the level 0 elements. Think of Generate as swapping elements for the result of a subquery that uses each element (in turn). More on this below !

Filtering elements in rows or columns can also be done on selections in other dimensions, provided that the latter are part of the title section of a view or report. For example, let's filter out models based on the region:


Suppose that each region/country holds the name of the (consolidated) model that can be sold in the region/country:




Then we can get the relevant consolidated model element based on the region selection in the titles section:







1. The MDX is like this:
{StrToMember( "[model].[" + [region].CurrentMember.Properties("Model selection") + "]")}

2. Alternatively: We can also use the Filter function as with Declan Rodger here
Filter( TM1SubsetAll( [model] ), [model].CurrentMember.Name = [region].[Model selection])

3. This is somewhat longer, in case the attribute name could/would coincide with an element name in the dimension:
Filter( TM1SubsetAll( [model] ), [model].CurrentMember.Name = [region].CurrentMember.Properties("Model selection"))

If we want to have the consolidated element and all its lowest-level descendants (for data entry for example):
{StrToMember( "[model].[" + [region].CurrentMember.Properties("Model selection") + "]")} + TM1Sort( TM1FilterByLevel( Hierarchize( Descendants( {StrToMember( "[model].[" + [region].CurrentMember.Properties("Model selection") + "]")} )), 0 ), Asc )

# Process to (re)generate subsets on the product dimension, where every company has its own (dynamic) subset of products sold
sDim_Comp = 'Rp_Company';
sDim_Prod = 'Rp_ProductGroup';
sAttr_For_Prod = 'Product Group parent member';


# MDX query to get the products linked to a company
sMDX = 'TM1Sort( TM1FilterByLevel( Descendants( {StrToMember("[%sDim_Prod%].[" + [%sDim_Comp%].[%sElem_Code%].Properties("%sAttr_For_Prod%") + "]") }] ), 0 ), Asc )';

# loop over companies and (re)generate the dynamic subsets
c = 1;
While( c <= Dimsiz( sDim_Comp ) );

   sElem_Name = Dimnm( sDim_Comp, c );
   sElem_Code = Attrs( sDim_Comp, sElem_Name, 'Code' );

   If( sElem_Code @<> '' );
   If( Ellev( sDim_Comp, sElem_Name ) = 0 );
   If( Elisanc( sDim_Comp, 'Total Company', sElem_Name ) = 1 );

      vSubset = '}Products_' | sElem_Code;
      If( SubsetExists( sDim_Prod, vSubset ) = 0 );
          SubsetCreate( sDim_Prod, vSubset );
      EndIf;
      SubsetMDXSet( sDim_Prod, vSubset, Expand( sMDX ));
      SubsetAliasSet( sDim_Prod, vSubset, 'Description' );

   EndIf;
   EndIf;
   EndIf;

   c = c + 1;
End;

Filtering on attributes

Double lookups

We have discussed that attribute values can be retrieved. They can be converted into element names. Let's go a step further and do 2 such lookups. Based on the current month, we ask the "Prior Period", of which we again ask the Period Period:
{ StrToMember("[Period].[" + StrToMember("[Period].[" + [Period].[Jul 2023].Properties("Prior Period") + "]").Properties("Prior Period") + "]") }

I am not saying that is the best method or even the only one. You could store the attribute values differently and do 1 lookup instead of 2. Of you could use the Lag function.

A second illustration goes as follows. A system cube holds the choice of a year. This is converted into a valid element in the Year dimension. That dimension has a text attribute called "Best month", filled in with 01, 02, ..., 12. Lastly the dimension Period has the MDX expression which gives us 1 element as the final result: 03
{StrToMember("[Period].[" + StrToMember("[Year].[" + [Sys_CFG].([Sys_CFG_Environment].[Current_Env], [Sys_CFG_Input].[Value], [Sys_CFG_Msr].[Selected year]) + "]").Properties("Best month") + "]")}

Filtering over dimensions

A very useful trick involving Instr is the following. You can apply it to views in the Cube viewer, Planning Analytics, Active forms, and so on. Well, you can filter (e.g.) accounts in the rows of a view in the cube viewer, when the account prefix equals the company name. Company is another dimension in the cube:
{Filter( TM1SubsetAll( [St_Account] ), Instr( [St_Account].CurrentMember.Name, [Mp_Std_Company].([Mp_Std_Company_Msr].[Company]) ) = 1 )}

• St_Account is the dimension of accounts in the rows.
• Mp_Std_Company is the cube where the companies are mapped (a 2D cube with companies and a measure called "Company"). Below, China Guangzhou is mapped in the lookup cube to "C010", which filters the prefix in the accounts.

It appears that in this setup the element selection for the dimension in the titles must be "retrieved" via a cube, such as Mp_Std_Company. A good alternative can be to use the }ElementAttributes control cube for said dimension.



This is based on the information in this topic.

Filtering with wildcards

More on sorting

vDim = 'PL_Account';
vSubset = 'tmp';
vSubset_tmp = vSubset | '_xyz';

SubsetCreate( vDim, vSubset_tmp, 1 );
vMDX = '{TM1SubsetToSet([' | vDim | '].[' | vDim | '], "' | vSubset | '", "public")}';
SubsetMDXSet( vDim, vSubset_tmp, vMDX );
vMDX = 'Order( {TM1SubsetToSet([' | vDim | '].[' | vDim | '], "' | vSubset_tmp | '", "public")}, [' | vDim | '].[' | vDim | '].CurrentMember.Properties("MEMBER_CAPTION"), Asc )';

SubsetMDXSet( vDim, vSubset, vMDX );
SubsetMDXSet( vDim, vSubset, '' );
SubsetDestroy( vDim, vSubset_tmp );
                        

Generate

A second example is provided below. You have a typical month structure but the fiscal year starts in April while the calendar year starts in January (obviously there are multiple ways to tackle this and this is not necessarily the best method):
Generate( Order([Month_YTD_FY_Offset].[Total Year].Children, [Month_YTD_FY_Offset].CurrentMember.Properties("FY order"), Asc), Descendants( {StrToMember("[Month_YTD_FY_Offset].[" + [Month_YTD_FY_Offset].CurrentMember.Name + "]")}))

A numeric attribute is used to capture the order of the months in the fiscal year. The output is summarized for clarity, not all days are shown.



gives:

Final example: you have a dimension ('A') with elements. These elements can occur as an alias (called Alt_Name) in a second dimension ('B'). You would like to create a subset containing all dimension A elements that occur in dimension B:
Generate( Filter( TM1SubsetAll( [B] ), Len([B].[Alt_Name]) > 0), {StrToMember("[A].[" + [B].[Alt_Name] + "]")})

Caveat: if the Alt_Name alias is filled in in dimension B, that name has to exist as an element in dimension A ! If it does not, TM1 will throw up an error in the best case, some TM1 versions will even experience a crash ! Thank you Ty Cardin

m = 'Test users';
n = Expand( 'Groups linked to subset %m%' );

If( SubsetExists( '}Clients', m ) = 0 );
   SubsetCreate(  '}Clients', m );
EndIf;
If( SubsetExists( '}Groups', n ) = 0 );
   SubsetCreateByMDX( n, Expand( 'Hierarchize( Generate( TM1SubsetToSet([}Clients], "%m%" ), Filter( TM1SubsetAll([}Groups]), [}ClientGroups].([}Clients].CurrentMember) <> "" )))'), '}Groups' );
EndIf;

Subsets based on other objects

SubsetCreateByMDX( 'Target subset', 'TM1SubsetToSet( [Account], "Source subset" )' );

SubsetCreateByMDX( 'Target subset', 'TM1SubsetAll( [Account] )' );

SubsetCreateByMDX( 'Target subset', 'Generate( TM1SubsetAll([Account]), Filter( TM1SubsetAll([Account2]), [Account].CurrentMember.Name = [Account2].CurrentMember.Name))' );

SubsetCreateByMDX( 'Target subset', 'Generate( TM1SubsetToSet([Account], "Filter by level 0"), Filter( TM1SubsetAll([Account2]), [Account].CurrentMember.Name = [Account2].CurrentMember.Name))' );

Distinct elements

dDimName = 'Versions';

# the operator +
SubsetCreateByMDX( '1', Expand( '{{[%dDimName%].[Planning]} + {[%dDimName%].[Budget]} + 
{[%dDimName%].[%PriorMonthForecast%]} + {[%dDimName%].[Jan Forecast]} + {[%dDimName%].[Mar Forecast]} + 
{[%dDimName%].[%PriorMonthSnapshot_1%]} + {[%dDimName%].[%PriorMonthSnapshot_2%]}}' ));

# the function Union, which only works on pairs
SubsetCreateByMDX( '2', Expand( 'Union( {[%dDimName%].[Planning]}, { {[%dDimName%].[Budget]}, 
{[%dDimName%].[%PriorMonthForecast%]}, {[%dDimName%].[Jan Forecast]}, {[%dDimName%].[Mar Forecast]}, 
{[%dDimName%].[%PriorMonthSnapshot_1%]}, {[%dDimName%].[%PriorMonthSnapshot_2%]} } )' ));

# function Distinct
SubsetCreateByMDX( '3', Expand( 'Distinct( { {[%dDimName%].[Planning]}, {[%dDimName%].[Budget]}, 
{[%dDimName%].[%PriorMonthForecast%]}, {[%dDimName%].[Jan Forecast]}, {[%dDimName%].[Mar Forecast]}, 
{[%dDimName%].[%PriorMonthSnapshot_1%]}, {[%dDimName%].[%PriorMonthSnapshot_2%]} } )' ));

# Distinct with condensed coding (double Expand function is needed)
d = '[%dDimName%].';
SubsetCreateByMDX( '4', Expand( Expand( 'Distinct( { %d%[Planning], %d%[Budget], %d%[%PriorMonthForecast%], 
%d%[Jan Forecast], %d%[Mar Forecast], %d%[%PriorMonthSnapshot_1%], %d%[%PriorMonthSnapshot_2%] } )' )));

Orphans and top consolidations

If( HierarchyHasOrphanedLeaves( 'Dimension name', 'Hierarchy name' ) = 1 );
   # The hierarchy contains orphaned leaves, take action if needed
EndIf;

TM1 users and groups

# Make sure the }ClientPropertiesSyncInterval=5 is present in the TM1s.cfg file

c0 = 'Active clients security';
vCube = '}ClientGroups';
vDim_1 = '}Clients';
vDim_2 = '}Groups';

## start from scratch
If( ViewExists( vCube, c0 ) > 0 );
   ViewDestroy( vCube, c0 );
   SubsetDestroy( vDim_1, c0 );
   SubsetDestroy( vDim_2, c0 );
EndIf;

# create subsets in the relevant dimensions
SubsetCreateByMDX( c0, Expand( 'TM1Sort( Filter( TM1SubsetAll( [%vDim_1%] ), [}ClientProperties].([}ClientProperties].[STATUS]) = "ACTIVE" ), Asc )' ), vDim_1 );
SubsetCreateByMDX( c0, Expand( 'TM1Sort( Hierarchize( Generate( Filter( TM1SubsetAll( [%vDim_1%] ), [}ClientProperties].([}ClientProperties].[STATUS]) = "ACTIVE" ), Filter( TM1SubsetAll( [%vDim_2%] ), [%vCube%].([%vDim_1%].CurrentMember) <> "" ))), Asc )' ), vDim_2 );

# apply an Alias
If( Dtype( '}ElementAttributes_}Clients', '}TM1_DefaultDisplayValue' ) @= 'AA' );
   SubsetAliasSet( vDim_1, c0, '}TM1_DefaultDisplayValue' );
EndIf;
If( Dtype( '}ElementAttributes_}Groups', '}TM1_DefaultDisplayValue' ) @= 'AA' );
   SubsetAliasSet( vDim_2, c0, '}TM1_DefaultDisplayValue' );
EndIf;

# create a view
ViewCreate( vCube, c0 );

# add subsets to the view
ViewSubsetAssign( vCube, c0, vDim_1, c0 );
ViewSubsetAssign( vCube, c0, vDim_2, c0 );

# the view layout
ViewRowDimensionSet(  vCube, c0, vDim_1, 1 );
ViewRowDimensionSet(  vCube, c0, vDim_2, 2 );
ViewSuppressZeroesSet( vCube, c0, 1 );

Application and control objects

When you look at the Microsoft links I mentioned above, you will find a lot more functions. Including functions for information on the dimension structure: FirstSibling, Parent, Children, FirstChild / LastChild, Ancestors, and so on. Many other options for creating dynamic subsets are explained on that webpage. It is a must read!

Temporary subsets

Needless to say, but these subset definitions using MDX syntax can be used in the function SubsetCreatebyMDX. That function can also create temporary subsets as of TM1 10.2.2 FP4 (see here).

TM1ELLIST

The function TM1ELLIST in a Planning Analytics For Excel spreadsheet can return an array of elements from a hierarchy. Interesting twist is that the list can be driven by an MDX. This opens many doors that were previously hard to open. The workaround of having a dynamic report (active form) was not elegant. Nor the workaround of having to array-enter the SUBNM formula in "enough" cells, knowing that it would need the MDX dynamic subset be part of the hierarchy. A fully dynamic MDX in a spreadsheet to drive a selection… nada ! Now we can.

You can just enter in a cell like A1:
=TM1ELLIST("CXMD:}Clients",,,,,"Head( TM1SubsetAll( [}Clients] ), 10 )")
and the result will spill down in the cell and 9 cells below - unless you have less than 10 users in the model. No need for array-entering the formula with Ctrl-Shift-Enter to receive the curly braces left and right of the formula. You now enter the world of dynamic arrays in Excel.

For those using VBA to generate the formula, consider:

Sub create_ellist()

    Dim vMDX As String

    vMDX = "TM1FilterByLevel( TM1SubsetAll( [Year] ), 0 )"

    'creating a TM1ELLIST function as a dynamic array
    Range("A1").Formula2 = "=TM1ELLIST(""servername:Year"",,,,,""" & vMDX & """)"
    Range("B1").Formula2 = "=""Y"" & A1#"

End Sub

The result are Year elements in column A, and the same elements prefixed with a "Y" in the corresponding cells in column B.

IS

The IS keyword can be used as well. If you remember the dynamic subsets we created to filter elements by levels, named appropriately in the }HierarchyProperties control cube, this can be written with IS as well:
Filter( TM1SubsetAll( [Period_MDX] ), [Period_MDX].CurrentMember.Level IS Semester )

In case the name of the level is a reserved keyword or there happens to be a dimension with the same name, you should fully qualify the name:
Filter( TM1SubsetAll( [Period_MDX] ), [Period_MDX].CurrentMember.Level IS [Period_MDX].[Year] )

Another secret found by George Tonkin.

Count

We have used the function Count before, but it's a good idea to list some of the many use cases (input by Philip Bichard):

• how many bad debtors do we have ?
• how many cost centres are late with their budget submission
• how many customers did not buy from us in the last 12 months
• etc.

Escape character

Caveats

A few caveats apply. If you create a dynamic subset in TI (the function is called SubsetCreatebyMDX('NAME OF THE SUBSET', 'MDX EXPRESSION'), make sure you always have at least 1 element that matches. If not, the process errors out. Unless you use TM1 10.1 or later, where an undocumented 3rd parameter to the function equal to the dimension name, will avoid the error in case no elements satisfy the expression. As an alternative, in that case you should loop over the dimension elements and insert the matching elements in a static subset SubsetCreate('DIMENSION NAME', 'SUBSET NAME') and SubsetElementInsert('DIMENSION NAME', 'SUBSET NAME', 'ELEMENT NAME', POSITION). The latter technique will always work (unless you are stuck in an endless loop like me on some Monday mornings), whichever you will use depends on your liking and coding skills.

Please do NOT use the function TM1SubsetBasis() (unless you know what you are doing). Even though the MDX expression recorder generates such code, it is in fact not to be used. It refers to a relative selection of elements ('at the time of using the function') rather than an absolute selection of elements ('it will always be the same selection/result, all else being equal').

Excel

In Excel spreadsheets, we can work with MDX queries to return a subset: for example in active forms. There, you could store the MDX query in a (hidden) cell. The query expression could be chopped up to include cell references. For instance, the user can choose a Product family in cell H10, and the active form returns the data on the lowest-level children of that Product family. Mind that there could be a 256 characters limit for the MDX expression for older versions of TM1. You COULD leave out the dimension names BUT then the element names must be unique throughout the ENTIRE TM1 model (over all dimensions).

MDX Views

Now that you are a full-blown expert on MDX in Planning Analytics, the party's only started. You can have cube views based on MDX expressions too. Since this is a broad topic on its own, here is an example of what an MDX view might look like:

Thank you

One of the many nice emails I received over the years !

• « Previous TM1 article
• Next TM1 article »