Subsets with commentary text

Introduction

In TM1, you can create dynamic subsets. These are subsets that return 0 or more dimension elements based on an MDX statement, a kind of definition of what elements to take from the dimension. This is interesting for, for example, a public default subset or a public subset containing a dynamic list of all n-type elements (level 0).

{TM1Sort( TM1FilterByLevel( TM1SubsetAll( [Wine_Color] ), 0), Asc)}

The above MDX expression returns the list of all lowest-level accounts in the dimension Wine_Color.

You can also include commentary text to the MDX definition.

The syntax possibilities for comments are as far as I know:

// This subset filters out all wine colors
{TM1Sort( TM1FilterByLevel( TM1SubsetAll( [Wine_Color] ), 0), Asc)}


-- This subset filters out all wine colors
{TM1Sort( TM1FilterByLevel( TM1SubsetAll( [Wine_Color] ), 0), Asc)}


/* This subset filters out all wine colors */
{TM1Sort( TM1FilterByLevel( TM1SubsetAll( [Wine_Color] ), 0), Asc)}

While I would not recommend it, you can add commentary text in between the code itself.

TI code

For a programmatic solution to create the dynamic subset with a comment, see below please:

# Wim Gielis # http://www.wimgielis.com
##### # TI code to create a dynamic subset with commentary text # 10/06/15 #####
vComment = 'This subset filters out all wine colors'; vDimension = 'Wine_Color'; vSubset = 'test'; vMDX = '{TM1Sort( TM1FilterByLevel( TM1SubsetAll( [' | vDimension | '] ), 0), Asc)}'; SubsetCreateByMDX( vSubset, '// ' | vComment | Char(10) | vMDX, vDimension ); SubsetCreateByMDX( vSubset, '-- ' | vComment | Char(10) | vMDX, vDimension ); SubsetCreateByMDX( vsubset, '/* ' | vComment | ' */' | Char(10) | vMDX, vDimension );

As you can see, you can use Ascii character 10 to write to a new line in the MDX expression window. The last parameter in the SubsetCreateByMDX function is used to avoid errors in case no elements would apply to the MDX definition. Change the names accordingly to suit your TM1 model. So good news, with this knowledge you can now automate the creation of subsets using custom Turbo Integrator coding !




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links