Active forms have been in use since version 9.4 and many users have tried them out. Some have enhanced or replaced their existing reports using this exciting new feature. One of the most powerful functions in all active forms is the TM1RptRow function. This function allows you to refer to a dimension subset to build a row set for your reports. These subsets can be dynamic so that your reports update automatically when new elements are added to a dimension. One possible problem with this solution is that using public dynamic subsets can affect performance of your server in some circumstances. So … what can we do about this?

Mulitdimensional Expressions or MDX is a query language used for multidimensional cubes. There are only a few places in TM1 where you can use MDX. One place where MDX can be used is in the TM1RptRow function in Active Forms. An MDX statement in an active form can produce similar results to using a dynamic subset but without the performance hit that you can get with public dynamic subsets. So.. how do we do it ?

This is the syntax of the TM1RptRow function. An MDX statement can be used as the seventh parameter of this function.

TM1RptRow (ReportView, Dimension, Subset, SubsetElements,

Alias, ExpandAbove, MDXStatement, Indentations, ConsolidationDrilling)

A requirement for a subset might be to expand all elements below a certain consolidation point. For example let’s say you wanted your active form to open with all elements expanded below the ‘Total Revenue’ consolidation of the ‘Account’ Dimension. You can do this with MDX using this syntax:

{TM1DRILLDOWNMEMBER( { [Account] . [Total Revenue] }, ALL, RECURSIVE )}

So inserting the MDX statement into the TM1RptRow function your TM1RptRow function might look something like this:

=TM1RPTROW($B$9,"TM1Server:Account","","","",0,"{TM1DRILLDOWNMEMBER( { [Account] . [Total Revenue] }, ALL, RECURSIVE )}")

(Note $B$9 is the cell reference of the Report View in this case, “TM1Server” is the server name and “Account” is the dimension for the rows of the report )

This is just one example of MDX syntax. There are many other things you could do including filter and sort and other variations of the drill down.

This syntax will drill down to the immediate children of total revenue:

{TM1DRILLDOWNMEMBER( { [Account] . [Total Revenue] }, ALL )}

This syntax will filter All accounts with an Account Type attribute of ‘Revenue’:

{FILTER( {TM1SUBSETALL( [Account] )}, [Account].[Account Type] = 'Revenue')}

This syntax will retrieve all level 1 consolidations in the dimension and sort in ascending order:  

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

The MDX statement can also be parameterized so you can link it up to a selection list. In the first example above you might setup a selection of other account types.

Using MDX in Active forms is a powerful tool to make your reports dynamic while avoiding possible performance problems with public dynamic subsets.<-->