I recently submitted a brand new TI process to the open source Bedrock library.
What is my process about? Multiple processes in Bedrock make use of filter strings for elements in a dimension. For instance:
vFilter = 'Year ¦ 2025 + 2026 & Period¦Jan + Feb + Mar + Apr & Version¦Actual';
That is all fine for small, hardcoded selections. Or we use a parameter value from the process or a different variable value:
vFilter = 'Year ¦ ' | vLastYear | ' + ' | vThisYear | ' & Period¦Jan + Feb + Mar + Apr & Version¦Actual';
What happens when we also need to filter in a non-trivial way? Like the level 0 companies that are located in a certain region 'pRegion' ? Or all assets accounts.
Then we will need to loop over subset elements, dimension elements, execute an MDX statement and loop over its results... This solution does not make me very happy.
What if we separate this logic in a different process? We want to supply:
- a dimension name
- either a subset name or an MDX statement
This new process takes care of all the rest. A global string variable is populated with the resulting elements from either the subset, either the MDX.
An example in the calling process:
StringGlobalVariable('sFilter_String');
vFilter = 'Year¦2025 + 2026 & ';
ExecuteProcess('}bedrock.dim.filter.fromsubset',
'pSelection_1', 'Company\mdx:Filter( TM1FilterByLevel( TM1SubsetAll( [Company] ), 0 ), [Company].CurrentMember.Properties("Region") = "' | pRegion | '" )' );
'pSelection_2', 'Account\ subset: assets accounts',
vFilter = vFilter | sFilter_String;
That's all. vFilter will then be:
vFilter = 'Year¦2025 + 2026 & Company ¦ Company A + Company G + Company M & Account¦ 1000 + 1010 + 1020 + 1100 + 1150 + 1200 + 1300 + 1500 + 1510'
for instance. No more hardcoding. No more spending time writing loops in the main processes.
Have a nice weekend!