This content has been marked as final. Show 12 replies
@LIKE is an 11.1.2 new feature. I've seen issues before where Calculation Manager won't validate something that's perfectly valid. If it, helps I'm fairly sure that @MATCH (which is a much older function) could be used to do the same thing, @LIKE just seemed more elegant for your particular problem.
The silly question first:
Why not user dynamic calc for the members you need? When updating the structure, you create the formula. Your verification if a member exists will be done in, for example, a select statement that create the dimension.
Now the complex idea:
Is it possible in your application recreate the script every time you update the dimension?
Here we have a cube that change the account dimension very often. So the cube have an CalcScript that need to be updated every time the dimension changes.
For that purpose, we have a process that read the database and recreate the calc with the correct members.
I never tested that solution with calc manager, but i think it's less complex than recognize the member itself during calculation.
I'm using two options in my calcs
1. @MEMBER(@CONCATENATE(@NAME(@CURRMBR("Account")),"%") perform that match condition in Relation DB and attach UDA to those accounts which match this. In my calc then use @ISUDA (this will only fetch the members which match the condition.
2. Create a dummy member in Essbase and perform a if logic and populate some dummy values
IF(@MATCH("Account",@MEMBER(@CONCATENATE(@NAME(@CURRMBR("Account")),"%"))) ->"Some member" == #Missing)
now your main calc will change to
IF(Dummy-> Some member = 111111)
You can use any one of these options
Edited by: Celvin Kattookaran on Mar 21, 2013 10:07 AM
Thanks Celvin, I cannot use @MATCH function as it shows syntax errors and am assuming its not capable of handling a function inside a MATCH function. Adding a UDA would be my last option as I dont want to add a UDA and maintain it just for one calc to be performed. I was looking for an option to flag all the accounts which have % accounts corresponding and make calcs based on this flag.
What error are you getting?
This is an example (I wrote this in Sample basic, create a member 100%10)
I got 1 for 100%10->Jan->Sales->"New York"->Actual
FIX(@RELATIVE("Product",0),"Actual", "New York","Jan") Sales( IF(@MATCH("Product",@NAME(@CONCATENATE(@CONCATENATE(@SUBSTRING(@NAME(@CURRMBR("Product")),0,3),"%"),@SUBSTRING(@NAME(@CURRMBR("Product")),4,6))))->"New York" == #Missing) 1; ENDIF ) ENDFIX