TM1 subset trick: client-specific dynamic selections and sums with one single subset

von | 24.08.2023 | Planning Analytics TM1

Imagine you want to replicate the filter functionality of Excel in workspace, TM1-web or a JavaScript-based frontend. The filter should be user-specific and aggregate in real-time any selection in that dimension.
In free-analysis you would normally use the rollup-functionality of TM1, which is only available there.

So, is there a way to replicate this behaviour and be able to use it in a variety of frontends? Yes, it is.
You can use dynamic-subsets that look onto a reference-cube and then use that dynamic-subset inside of a „subset in subset“ or inside of an MDX-virtual-element. This works for 1 or n dimensions of a given cube!

Let me simplify the idea and show you how it is done with an example:

First off you need a cube that consists of the dimension you want to filter and the }Clients dimension or a copy of it.

When you have created the cube and authorized it, users should be able to write something into the cube from a selector, websheet or widget or whatever you like to program. The idea is to be able to select multiple n-elements from the dimension.
You simply write a value to the selected elements in the cube from your websheet or whatever frontend is used.

Now you need one (!) dynamic subset (let’s call it FILTER_USER_SPECIFIC) on the dimension that shall be filtered. Here is the MDX-statement for the dimension:

{FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL([DimensionToFilter])}, 0)}, [_ClientFilterDimensionToFilter].(StrToMember("[}Clients].["+USERNAME+"]"),[DimensionToFilter].CurrentMember)<>0)}

„DimensionToFilter“ is the dimension you want to filter, „_ClientFilterDimensionToFilter“ is the name of the cube you created in the steps above.

So now, when you enter a selection in the „_ClientFilterDimensionToFilter“, the MDX will only select these dimension-elements for this specific user. This is already a nice solution if you wanted to just control the selections in a control-subset and would not want to create a subset per user that would need maintenance.

To go even further you could create a so-called “subset in subset”, which is an older functionality currently only available in Architect, Perspectives and TM1-Web in the dimension-editor. Call the subset “FILTER_USER_SPECIFIC_SUM” and assign the dynamic subset “FILTER_USER_SPECIFIC” to it. You will create a static subset with a sum-element that contains descendants that are user-specific and real-time calculated. Users can control the descendants directly by themselves by simply changing values inside of the “_ClientFiterDimensionToFilter” cube.

Of course, you can elaborate on this idea to make it more complex. You would also need an MDX-aggregate-statement and virtual dimension-elements if you would like to use the same method in workspace or REST-API.

Grischa Rehmer

Grischa Rehmer

Vereinbaren Sie jetzt einen Termin für ein persönliches Gespräch.

Weitere Beiträge

EXPO REAL 2023

EXPO REAL 2023

Vor einer Woche durfte ich das erste Mal in München an der EXPO REAL, der größten Messe der Immobilienbranche im deutschsprachigen Raum, teilnehmen. Tatsächlich sind wir als GMC² GmbH schon seit 2010 regelmäßig als Branchen-Experten für Business Intelligence und...

mehr lesen

Nehmen Sie Kontakt auf

GMC² GmbH
Joseph-Schumpeter-Allee 29
53227 Bonn

info@gmc2.de
+49 228 30497700

13 + 3 =

Hinweis: Die abgesendeten Daten werden nur zum Zweck der Bearbeitung Ihres Anliegens verarbeitet. Sie können Ihre Einwilligung für die Zukunft jederzeit per E-Mail widerrufen. Weitere Informationen finden Sie in unserer Datenschutzerklärung.

Datenschutz
GMC² GmbH, Inhaber: Holger Gerhards (Firmensitz: Deutschland), verarbeitet zum Betrieb dieser Website personenbezogene Daten nur im technisch unbedingt notwendigen Umfang. Alle Details dazu in der Datenschutzerklärung.
Datenschutz
GMC² GmbH, Inhaber: Holger Gerhards (Firmensitz: Deutschland), verarbeitet zum Betrieb dieser Website personenbezogene Daten nur im technisch unbedingt notwendigen Umfang. Alle Details dazu in der Datenschutzerklärung.