TM1 Rule using ELPAR

[dan.kelleher]

My rule is as follows:

['dimTier':'Non Tier Item', 'dimCSRPeriod':'07 CTD'] =
N: IF(DB('cbmCSRWBS', !dimProject, !dimCSRWBS, '07', !dimYear) = 0, ['dimTier':'Non Cost Centre Item', 'dimCSRPeriod':'07 CTD'],
IF(ATTRS('dimCSRWBS', !dimCSRWBS, 'Dummy') @= 'True' , -[ELPAR('dimCSRWBS', 'A.10 Dummy',1), 'dimTier':'Non Cost Centre Item', 'dimCSRPeriod':'07 CTD'],0));

The problem stems with the use of ELPAR when nested above, i.e. when I replace ELPAR('dimCSRWBS', 'A.10 Dummy', 1) with 'A.10',which is its only parent, the rule works fine. Does anyone have any ideas?

The error I get states that the dimension name cannot be found. Are you not allowed to derive the element reference in this manner?

Thanks in advance

[mykill]

hi dan,

try something like this:

['dimTier':'Non Tier Item', 'dimCSRPeriod':'07 CTD'] =
N: IF(DB('cbmCSRWBS', !dimProject, !dimCSRWBS, '07', !dimYear) = 0, ['dimTier':'Non Cost Centre Item', 'dimCSRPeriod':'07 CTD'],
IF(ATTRS('dimCSRWBS', !dimCSRWBS, 'Dummy') @= 'True' , DB('CubeName', ELPAR('dimCSRWBS', 'A.10 Dummy',1), 'Non Cost Centre Item', '07 CTD')*-1,0));

[kpk]

Hello,

When you are referring for an element using any function (ELPAR, DIMIX, ATTRS, e.g.) then you have to use a full DB(.............. FUNCTION ...............) reference both in internal and external cube rules.

Regards,
Peter

[Steve Rowe]

Also just to sound a note of caution on using Elpar in rules since it's not that safe.

Elpar requires you use an index to specify which hierarchy you are talking about. If you have multiple hierarchies then there is no way of controlling which is hierarchy "1" and which is "2". It's possible for the same hierarchy to be a different index for elements in the hierarchy and for the index number to change after a dimension is saved.

A way round this is to set up an attribute that holds the parent reference you need. I usually do this as follows. If the top of the hierarchy which I'm searching for the parent in is "Total Profit" then I have two string attributes in my dimension. One ruled as below

[P and L Parent Calc]=S:
#Is the first parent in the correct hiearchy
If ( ElIsAnc (Dim, 'Total Profit', elpar ( dim, !dim,1)) =1, elpar ( dim, !dim,1),
#Else is the second parent
If ( ElIsAnc (Dim, 'Total Profit', elpar ( dim, !dim,2)) =1, elpar ( dim, !dim,2),
#Repeat as many time as you have hierarchies.

'Not Found'));

This would give the result I need but I would not reference this directly in my main rule since a nested if like this with many elpars and elisancs in it would be slow (in TM1 terms) to calculate. I then have a TI process that copies the attribute 'P and L Parent Calc' into the attribute 'P and L Parent', this is easy for me as I have good control over when dimensions are updated.
HTH

[dan.kelleher]

Thanks mykill, Peter and Steve for your sound advice and prompt responses.

Dan

[dan.kelleher]

My rule now appears to be working for N-level elements, but the consolidations do not appear to recognise the calculation if it is derived from the parent, i.e.

Element Dummy Attr cbmCSRWBS Non Cost Centre Item Non Tier Item Desired NTI
A 'False' 3 60 60 0
A.10 Dummy 'True' 1 0 -60 -60
A.10.01 'False' 0 10 10 10
A.10.02 'False' 0 20 20 20
A.10.03 'False' 0 30 30 30

If I comment out the SKIPCHECK; line it works fine, which leads me to believe my rule is not being fed properly:

['dimTier':'Non Tier Item', 'dimCSRPeriod':'07 CTD'] =
N: IF(DB('cbmCSRWBS', !dimProject, !dimCSRWBS, '07', !dimYear) = 0,
['dimTier':'Non Cost Centre Item', 'dimCSRPeriod':'07 CTD'],
IF(ATTRS('dimCSRWBS', !dimCSRWBS, 'Dummy') @= 'True' ,
DB('cbcCSRFacts',!dimChartOfAccounts, !dimCounterParty, !dimFunction, !dimCSRMeasures, !dimOrganisation,
!dimCSRPeriod, !dimProject, 'Non Cost Centre Item', !dimYear, ELPAR('dimCSRWBS', !dimCSRWBS, 1), !dimCSRRBS, !dimCSRProfitMargin)*-1, 0));

FEEDERS;
['Non Cost Centre Item'] => ['Non Tier Item'];

[Steve Rowe]

Hi Dan,
Yes it sounds like a feeder issue.
You seem to be only feeding from the false part of your IF statement, is that always populated even when the condition is false? If not then your feeder fails when you are on the Elpar part of your rule....
Doing that bit properly is a bit of a challenge. Off the top of my head something like this.

#Subset style list of all the possible L1 parents that could be references in your ElPar
[{ 'L1 Parents'} ,....{'L1 Parents'}, 'Non Cost Centre Item']=>
#Conditionalise the Feeder as the LHS is quite large and general.
DB ( IF( (~DB('cbmCSRWBS', !dimProject, !dimCSRWBS, '07', !dimYear) = 0) & ATTRS('dimCSRWBS', !dimCSRWBS, 'Dummy') @= 'True' ,
#Put the cube name in the DB
'cbcCSRFacts' ,
#make the DB fail by putting a blank in
''),
#Rest of DB
dimChartOfAccounts, !dimCounterParty, !dimFunction, !dimCSRMeasures, !dimOrganisation,
!dimCSRPeriod, !dimProject, 'Non Tier Item', !dimYear, ELPAR('dimCSRWBS', !dimCSRWBS, 1), !dimCSRRBS, !dimCSRProfitMargin));

Note that we retain the elpar in the RHS of the feeder since the feeders only execute at the N level on the LHS so when we say Parent1 on the LHS side of the feeder this means 'all the children of Parent 1', so when the feeder executes you would have the same !dimCSRWBS on both sides of the feeder. This is not what we want as this is the same as the feeder you have currently. What we need to do is say all the children of Parent 1 feed all the children of Parent 1, which is achieved by putting the elpar in the RHS too.

Note that I'm not saying this is the best approach, without knowing alot more about your data and what you are trying to achieve, this is the best I can do (noting my previous point about not using Elpar in rules and feeders.

Anyway HTH.
Cheers