TM1 Forum

So I have a need to write C level rules which apply across multiple hierarchies of a dimension. Cubewise have this valuable information on using DB and multiple hierarchies (I cannot see an equivalent in IBM documentation?)

https://cubewise.com/blog/how-to-refere ... -in-rules/

Specifically:
"
Further hierarchies can be included within the brackets with the syntax ‘Hierarchy’:’C Level Element’ in a comma separated list.

= DB ( ‘Sales’ , ‘Actual’, ( ‘Color’ : ‘Red’, ‘Product’ : ’T Series’, ‘Engine Type’ : ’Diesel’ ) , ‘Units’ ) ;
"

And with CellValueN I can write a formula which omits the dimension entirely which can make things a bit easier to maintain. eg:

= CellvalueN ( ‘Sales’ , 'Version':‘Actual’, 'Measure':‘Units’ ) ;

And this is what generally works for me and it potentially future proofs as we may add more hierarchies to that dimension in the future (except it throws warnings in the server log)

But if I want to use CellValueN and specifically reference the dimension with multiple hierarchies something similar to what can be done in the DB what is the syntax?

I too have seen the message log errors you mention. Although it seems to work ok, it does get upset on the server side that there are too few arguments.

Are you asking how to reference the alternate hierarchies from within the CellValueN function? You add them just like the other dimension arguments, BUT they need to be hierarchy qualified and you don't need to wrap them in parentheses like with DB.

Here is the cubewise DB example rewritten as a CellValueN:

Thanks, this is how I though it worked but I had tried that and I thought initially it had worked but was not working for me later, I cannot see any syntax issues in my rule but maybe there is......

You can "intersect" multiple hierarchies of the same dimension using this syntax:
BTW, it can be all dynamic using variables, you can even "omit" some hierarchies by specifying an empty variable, you can even use hierarchy subsets
I just typed the example above, so forgot any typos
I use this approach to calculate complex report mappings based on the attribute hierarchies

vovanenok wrote: ↑Mon Sep 12, 2022 7:05 pm You can "intersect" multiple hierarchies of the same dimension using this syntax:

Code: Select all

CellGetN( 'cubeName', 'dim1Elm', 'dim2Elm', ( 'dim3Name:hier1Name:hier1Elm', 'dim3Name:hier2Name:hier2Elm', ... ), dim4Elm, ... );

BTW, it can be all dynamic using variables, you can even "omit" some hierarchies by specifying an empty variable, you can even use hierarchy subsets

Thanks, that's useful but CellGetN is a TI function only. My challenge is with CellValueN

JohnO wrote: ↑Sun Sep 11, 2022 11:57 pm Thanks, this is how I though it worked but I had tried that and I thought initially it had worked but was not working for me later, I cannot see any syntax issues in my rule but maybe there is......

I have tested it again and am getting the same result, nothing returns. I cannot see any explanation for it, I'll raise it with IBM.

So in summary, it's a C: level rule (Time based override for balance measures).

1) db with bracketed reference to the multiple hierarchies works
2) CellValueN works if any reference to the dimension in question is omitted
3) CellValueN does not works if references to the dimension in question are included.

Hi,

This might be an appropriate time to actually show us your code.

It might not help with your query but it will be useful for the record!

Maren