I don’t know, some of you might be wondering why I was not active during a couple of months. (or maybe not)
Here are some updates that happened in my personal life
- Became father of a lovely daughter (our second daughter)
- Sold my existing home (It was really hard to part with, felt like someone so close passed away 🙁 )
- Moved to a new city
I think all those combined made me walk away from the Hyperion world for a while (well not entirely true 😉 ) and this is a post that was long due after the wacky post on shortcodes in Essbase. So here we go another surprising and fun twist to Essbase calc script language.
When you first learned to code in Essbase, what did you learn first?
IF on dense, FIX on sparse.
Well, even before that what did you learn? Well I’m looking for something really basic!!!!
Ok let me show you a script and then you can tell me.
SET MSG DETAIL; FIX(@ILSIBLINGS("Florida"), "Jan", "100-10", "Budget", &CurYr) "Sales"( 1.5 * "Actual"; ) ENDFIX
Still no clue, (well that is a really basic script) how are the members separated? They are separated using “,”s.
What if I tell you that the below-given script works?!!!!
SET MSG DETAIL; FIX(@ILSIBLINGS("Florida") "Jan" "100-10" "Budget" &CurYr) "Sales"( 1.5 * "Actual"; ) ENDFIX
Oh, you don’t believe me huh?
Here is what you’ll see in the logs.
What does that mean, you don’t really need commas to separate members in a FIX statement.
Now let’s add a new substitution variable to the picture. Let’s imagine that we are going to perform long range plan.
Script is updated to as shown below
Let’s run this script. As usual, it will calculate the following.
Here is where the magic starts, let’s imagine that next month you are not doing LRP. How can we still use the same script and just calculate current year? It is really simple!!!! Just remove the value from LRPYr sub var!!!!
Again I can see that disbelief on your face. Let’s see it!!
Here is a live video of me doing this.
There you go an easier way to reuse the scripts.
Imagine the possibilities 🙂
Hi Can you help me with some real time calc script examples and also real time outline details.
Excellent tip. I had long since forgotten we didn’t need commas and I never thought to pair that with blank SubVars. Well played, sir.
Thank you Ed
Celvin,
Are you saying that one *doesn’t* need to use SET EMPTYMEMBERSETS ON ; to make this work? If it abends, does it jump to the next valid calc block?
Here’s PeterN’s take on it. What you wrote is similar but definitely different:
http://essbasedownunder.com/2016/11/conditional-fix-statements/
Regards,
Cameron Lackpour
Cameron, No I’m not saying that you shouldn’t be using EMPTYMEMBERSETS. You should use that in all cases.
What is different about this is, when the script get executes it won’t even send &LRPYr to calc engine (since the subvar is blank) and so no need for EMPTYMEMBERSET (for this calc). What Pete is doing is making use of EMPTYMEMBERSET so that BUDGET AND Actual part of script will not be called.