Product:
Planning Analytics 2.1.11
Microsoft Windows 2022 server
Issue:
How many element in a dimension subset?
Solution:
Use the function SubsetMDXSet to get the number of elements in the selection, it will also convert the MDX subset to a static subset.
SubsetMDXSet applies a specified MDX expression to a public or temporary subset.
If the passed MDX expression is valid, the specified subset is saved as a dynamic subset defined by the MDX expression.
If the passed MDX expression is an empty string, the subset is converted to a static subset that contains the elements that are in place when SubsetMDXSet is executed.
The SubsetMDXSet returns the number of elements that the subset contains.
In below example the subset becomes static as we use ” as parameter.
We can use the number of elements, to count them and add them to a new dimension, with a total element first.
DimensionElementInsertDirect( tDim, '', 'Total', 'C');
sSubset = 'temp_Account';
sMDX = '{TM1FILTERBYLEVEL(TM1SUBSETALL([Account]) , 0)}';
SubsetCreatebyMDX(sSubset, sMDX, 1 );
vNumElems = SubsetMDXSet( tDim, sSubset, '' );
i = 1;
while (i<=vNumElems);
vElem = SubsetGetElementName( tDim,sSubset, i );
DimensionElementComponentAdd( tDim, 'Total',vElem, 1 );
i = i +1;
end;
More Information:
SubsetMDXSet – IBM Documentation
Slow MDX Subsets? Best Practice Guide to Maintain Sets in TM1
Wim‘s Excel, TM1 & soccer site
User Guide > Using the Manager with TM1 > Managing MDX Library
How to order/sort data in cube view used as a data source in Turbo Integrator (TI)? – Ever Analytics