TM1 Rule DB with IF Statement

[pberg]

I am busy with a rule and getting a Syntax error for invalid string expression. The rule needs to populate an element, using an IF statement based on an element attribute on a dimension in the cube where I will be DB from, but not in the cube where the rule will be. The rule is as follows,

['_3552','GIP Value']=N:if(DB('}ElementAttributes_MET Cost Codes',!MET Cost Codes,'GIP RI')@='_3552',DB('MET Cost',!Fisical Period,!Versions,subst(!MET Cost Codes,1,3),'X200');

and the error:
---------------------------
TM1 Error
---------------------------
Line 97: Syntax error on or before:
!MET Cost Codes,1,3)
invalid string expression
Rule could not be attached to the cube, but changes were saved.
---------------------------
OK
---------------------------

I have looked at examples in the help function and on the net and I can't find a difference between how I wrote the rule and the examples that work, need some help on this.

Thanks.

[declanr]

First of all do the dimensions "MET Cost Codes", "Fisical Period" and "Versions" all exist in the cube for which the rule sheet is attached to?

Second of all have you got the spelling correct on the one you list as "Fisical Period"? I would imagine that this should be fiscal.

[pberg]

The MET Cost Codes doesn't exist in the cube where the rule is attached, there is a location dimension that is where the substring later in the rule comes in.

I miss spelled the Fisical Period when creating the dimension. Running with it as is for now, will fix it once everything works.

[declanr]

Also on closer inspection you haven't got enough closing brackets.

Code: Select all

['_3552','GIP Value']=N:if(DB('}ElementAttributes_MET Cost Codes',!MET Cost Codes,'GIP RI')@='_3552',DB('MET Cost',!Fisical Period,!Versions,subst(!MET Cost Codes,1,3),'X200');

4 opening, 3 closing. Is "X200" the last element in the "MET Cost" cube? Or is it what you want the rule to derive if the if statement isn't achieved?

If its the prior you need to include an else to your If.
If its the latter you need an extra closing bracket after the subst to close the DB.

[declanr]

The MET Cost Codes doesn't exist in the cube where the rule is attached, there is a location dimension that is where the substring later in the rule comes in.

Your SubST is still using !MET Cost Codes; any dynamically reference dimension (i.e. those starting with a !) need to exist in the cube the rule is attached to otherwise it doesn't have any way of knowing which element you want to use from that dimension.
Just to clarify by the cube that the rule is attached to I don't mean the cube that DB statement references but rather the cube which you can expand in architect and see this rule sheet underneath it.

[pberg]

I thought that might be the problem by trying to reference to a dimension that doesn't exist in the cube. We didn't want to go the process way, but I am going to do that now as I know I will be able to make it work. Thanks for your help.

[David Usherwood]

If you know how to link the relevant dimension in your cube to the Met Cost Codes dimension using TI then you can do it within a rule - the logic will be the same.

[Duncan P]

Also on closer inspection you haven't got enough closing brackets.

My guess is that you haven't given a value for "if fails condition". I'm also guessing that this is because it should be 0. If that is the case try this :-

Code: Select all

['_3552','GIP Value']=N:if(DB('}ElementAttributes_MET Cost Codes',!MET Cost Codes,'GIP RI')@='_3552',DB('MET Cost',!Fisical Period,!Versions,subst(!MET Cost Codes,1,3),'X200'),0);