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:
„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.
