String Formula

[tryinghard]

Hello,

I am new to the forum and the relatively new to the tool. I have written a formula but getting an error

"Syntax error on or before !SelectValue)

Invalid String Expression"

['Planning Cycle']= S:DB('Planning Cycle','Planning Cycle',!Select Value);

Both the source and the target cells are string; target cube has 6 dimensions in it and as you can see source has only three.

Can someone please help me with this?

thanks..

[Andy Key]

Given that error message, I would guess that your target cube doesn't have a dimension called 'Select Value'. In any reference of the form !<dimension name> the dimension name in question has to be in the target cube.

Also, you say your source cube has three dimensions, but your DB() reference only shows 2. The first argument in a DB() reference is the name of the cube.

[tryinghard]

Thanks for the reply Andy. My Source cube has 2 diemensions and target cube has 6.

You are correct the target cube does not have a Select value dimension; that being said, now how do I transfer this data from one cube to another, when the dimensions are not the same, basically I want to move the Planning cycle into every single cell of the target cube, the source cube is like a look up cube that holds only this value.

[Andy Key]

My first question is Are you sure?

Do you really want or need to have the same value repeated so many times? Can you not just refer to it from the other cube when you need it?

If you still want to rule the string through to your target cube you have a number of choices.

1) If it is a single value of the Select Value dimension that you want to copy, then refer to it directly as you have done with 'Planning Cycle'. For example

Code: Select all

['Planning Cycle']= S:DB('Planning Cycle','Planning Cycle','Value from the Select Value dimension');

As you say the Planning Cycle cube is a lookup cube and only holds this value, then this is probably the solution for you.

2) If you have a dimension in your target cube that has the same elements as the Select Value dimension (or a subset or superset of them) then you can use that dimension name instead of !Select Value. Wherever the elements of the two dimensions match, the DB reference will still work. For example

Code: Select all

['Planning Cycle']= S:DB('Planning Cycle','Planning Cycle',!dimension from the targe cube that shares elements with the Select Value dimension);

3) Set up an attribute (for a many to one link) or an alias (for a one to one link) on the dimension that links your target cube to the Select Value dimension and reference that in the DB instead. For example

Code: Select all

['Planning Cycle']= S:DB('Planning Cycle','Planning Cycle', AttrS( 'Target cube dimension', !Target cube dimension, 'Attribute that links Target cube dimension to Select Value dimension') ); 

[tryinghard]

Andy, thanks again for the detailed response. You are correct that I do not need to repeat the value in the cube; however I am tryiing to write a conditional formula and getting an error message.

['Fx Rate'] = IF(DB('Planning Cycle','Element','Planning Cycle') @ = 'Final',1,0); here in the db formula element, and planning cycle are the elements of the 2 dimensions of the planning cycle cube. This db formula works as the first method specified by you. The error is logical expression error.

Basically I have 2 rate types and based on the planning cycle I want to get the correct rate type from another cube; however this simple conditional formula above is not working and giving a logical expression error.

If you have better ideas to deal with fx rates then please let me know. If this formula works I was thinkign of attaching rate type to attributes and then using attributes in the formula since the above formula will be applicable to different measures in the dimension.

[Andy Key]

I think the only problem you have there is an extra space between the @ and =

[tryinghard]

Thanks Andy, you are right.