Surprising FIX statement – Surprises in Essbase Part XIII 5


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

  1. Became father of a lovely daughter (our second daughter)
  2. Sold my existing home (It was really hard to part with, felt like someone so close passed away 🙁 )
  3. 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 🙂


About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

5 thoughts on “Surprising FIX statement – Surprises in Essbase Part XIII

    • Celvin Kattookaran Post author

      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.