Hello,
For once, I'm not going to bitch about IBM trying to replace "old" tools with "new" tools that don't cover existing functionalities, nor am I going to bitch about the fact that TM1 still can't read xlsx or xml files (for example).
I'm simply going to share with you a little trick I use regularly to address the following problem:
At different levels of a dimension, I need to enter values, rates and percentages in order to calculate management fees, cascading rebates.
Of course, if you enter numbers at an aggregated level, they will be spread over all the sheet cells below.
Two different solutions are proposed :
1 - Store our amounts as character strings (we can input them at any level !)
2 - Create a "flat" copy of our dimension (without ascendant nor descendant) and use it in a cube to enter our famous values.
In the first case, we'll need to read the (string) amounts at each level and convert them into numbers:
['Fees'] = N : ['TurnOver'] * ( Numbr ( Db('input_cube', ....... !Dimension, ....))
+
Numbr ( Db('input_cube', ....... Elpar('Dimension',!Dimension,1), ....))
+
Numbr ( Db('input_cube', ....... Elpar('Dimension',Elpar('Dimension',!Dimension,1),1) , ....))
+ ......
) ;
In the second case, we'll need to read the member amounts of the relevant flat dimension:
['Fees'] = N : ['TurnOver'] * ( Db('input_cube', ....... !Dimension, ....
+
Db('input_cube', ....... Elpar('Dimension',!Dimension,1), ....)
+
Db('input_cube', ....... Elpar('Dimension',Elpar('Dimension',!Dimension,1),1) , ....)
+ ......
) ;
In terms of representation and input form, I confess I'm partial to solution 1, which also allows dynamic reporting (folded-unfolded) without the annoyance of "child" lines.
Kind Regards,
Philippe
------------------------------
Philippe CHAMPLEBOUX
EPM Project director
Next Decision
NANTES
------------------------------