Rule for internal cell ref and alternate hierarchies

Post Reply
User avatar
PavoGa
Community Contributor
Posts: 443
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.09
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Rule for internal cell ref and alternate hierarchies

Post by PavoGa » Thu Oct 15, 2020 8:40 pm

So, I want to reference a cell within a cube located at the intersection of several alternate hierarchies. Do not want to use DB() to an external cube or on this internal reference because speed of [element1, element2, ..., elementN] is faster.

However, the only thing that seems to work is DB(). Have not seen anything in documentation that indicates what I want to do will not work. In fact, the examples for CellValueN provides an explicit example the intended way I plan (and tried!) to use it.

Again, this is attempting to retrieve a value within the same cube, just different hierarchy intersections.

Code: Select all

# this saves, but does not return a value.
['MappingTestMeasure'] = N:['Organization:hier_Mapping:MappingElement', 
    'HR Level:hier_Mapping:MappingElement', 
    'Test_Cube Measure:hier_Mapping:53PostYearFactor'];
    
# this saves, but does not return a value.
['MappingTestMeasure'] = N:['Organization':'hier_Mapping':'MappingElement', 
    'HR Level':'hier_Mapping':'MappingElement', 
    'Test_Cube Measure':'hier_Mapping':'53PostYearFactor'];
    
# this errors when saving.
['MappingTestMeasure'] = N:['Organization:hier_Mapping':'MappingElement', 
    'HR Level:hier_Mapping':'MappingElement', 
    'Test_Cube Measure:hier_Mapping':'53PostYearFactor'];

## this returns the desired value.
['MappingTestMeasure'] = N:DB('Test_Cube',
    !Version,
    'Organization:hier_Mapping':'MappingElement',
    'HR Level:hier_Mapping':'MappingElement',
    !Fiscal Period,
    'Test_Cube Measure:hier_Mapping':'53PostYearFactor');

## Errors when saving.
['MappingTestMeasure'] = N:DB('Test_Cube',
    !Version,
    'Organization':'hier_Mapping':'MappingElement',
    'HR Level':'hier_Mapping':'MappingElement',
    !Fiscal Period,
    'Test_Cube Measure':'hier_Mapping':'53PostYearFactor');
    
# This saves, but returns nothing.
['MappingTestMeasure'] = N:CellValueN('Test_Cube', 
    'Organization':'hier_Mapping':'MappingElement', 
    'HR Level':'hier_Mapping':'MappingElement', 
    'Test_Cube Measure':'hier_Mapping':'53PostYearFactor');


### created distinct hierarchies and elements for each dimension.

### this returns the desired value.
['MappingTestMeasure'] = N:DB('Test_Cube',
    !Version,
    'Organization:hier_Mapping_Org':'MappingElement_Org',
    'HR Level:hier_Mapping_HR':'MappingElement_HR',
    !Fiscal Period,
    'Test_Cube Measure:hier_Mapping_Measure':'53Factor');
    
# This saves, but returns nothing.
['MappingTestMeasure'] = N:CellValueN('Test_Cube', 
    'Organization':'hier_Mapping_Org':'MappingElement_Org', 
    'HR Level':'hier_Mapping_HR':'MappingElement_HR', 
    'Test_Cube Measure':'hier_Mapping_Measure':'53Factor');
So, am I doing something wrong and, if so, what is it?
Ty
Cleveland, TN

Post Reply