Syntax for CellvalueN referencing a dimension with multiple hierarchies?

Post Reply
JohnO
Posts: 92
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.9.x
Excel Version: Office 365 16

Syntax for CellvalueN referencing a dimension with multiple hierarchies?

Post by JohnO »

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?
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Syntax for CellvalueN referencing a dimension with multiple hierarchies?

Post by ascheevel »

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:

Code: Select all

CellValueN('Sales', 'Version':'Actual', 'Product':'Color':'Red', 'Product':'Product':'T Series', 'Product':'Engine Type':'Diesel', 'Production Measure':'Units');
JohnO
Posts: 92
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.9.x
Excel Version: Office 365 16

Re: Syntax for CellvalueN referencing a dimension with multiple hierarchies?

Post by JohnO »

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......
User avatar
vovanenok
Posts: 88
Joined: Mon Jun 23, 2014 4:54 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: Office 365
Location: Toronto, Canada
Contact:

Re: Syntax for CellvalueN referencing a dimension with multiple hierarchies?

Post by vovanenok »

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

Code: Select all

hier1Elm = dimName |':'| hier1Name |':' | elm1;
hier2Elm  = dimName |':'| hier2Name |':' | subset2;
hier3Elm = '';
hier4Elm  = dimName |':'| hier4Name |':' | elm4;

CellGetN( 'cubeName', 'dim1Elm', 'dim2Elm', ( hier1Elm, hier2Elm, hier3Elm, hier4Elm ), dim4Elm, ... );
I just typed the example above, so forgot any typos
I use this approach to calculate complex report mappings based on the attribute hierarchies
----------
TeamOne Google Sheets add-on for IBM Planning Analytics
Let's connect on LinkedIn
JohnO
Posts: 92
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.9.x
Excel Version: Office 365 16

Re: Syntax for CellvalueN referencing a dimension with multiple hierarchies?

Post by JohnO »

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
Posts: 92
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.9.x
Excel Version: Office 365 16

Re: Syntax for CellvalueN referencing a dimension with multiple hierarchies?

Post by JohnO »

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.
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Syntax for CellvalueN referencing a dimension with multiple hierarchies?

Post by MarenC »

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
Post Reply