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

Nehmen Sie Kontakt auf

GMC² GmbH
Joseph-Schumpeter-Allee 29
53227 Bonn

info@gmc2.de
+49 228 30497700

9 + 9 =

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.