Stupid Programming Tricks #16 -- Special characters in Substitution Variables with MaxL

Shouldn’t I be able to find this?

I was working on an upgrade project this week from Essbase 9.3.1 and as part of this process I needed to add a bunch of Essbase substitution variables to my test server.  This should have been No Big Deal, especially as the client was going to give me a MaxL script with the variables and their values all ready to go.  Simply change the username, password, and server and everything will be tickety-boo, right?

So how do you add a variable, anyway?

I don’t know how the variables got onto the client’s server, but I suspect they have been there for a good long time, given that this is a 9.3.1 instance of Essbase.  What I got from the client was code that looked like this:

alter database appname.dbname set variable variablename value ;

What oh what is wrong with this approach when said variable (I am calling it CLTest) doesn’t exist?  Simply this:

After a bit (okay, a lot) of headscratching it made sense – the variable has to exist before it can be set.  In other words, set variable only works when the variable already exists.  Uh oh, as I had 76 variables to value.  For sure I didn’t want to set that in EAS.  So, I thought in my sometimes-logical mind, surely if I can set it, I can add it, right?

However, a review of the alter command’s 11.1.2.2 (and yes, I am finally on an 11.1.2.2 project, only eight months or so after its release, Huzzah!) documentation at the system, application, and database level, I couldn’t find how to add (I hope with the repetition of that word I have clued you in by now) a variable.

So I went to the great false god Google and searched for how to do this and got lots of hits but got…nothing.  Lots and lots and lots of ways to set the values, but not to add them.

And then it hit me

What oh what oh what would happen if I modified the alter command, took out set and put in add, as adding a variable is what I want to do?

And so it was:

A bozo is what I am for struggling with that for 15 minutes.

MaxL functionality through the ages

I don’t have a 9.3.1 instance to prove this out on, but the client said this (see below) worked and it most definitely did not in 11.1.2.2, so I am going to guess that it changed.  In any case, this stuff is not all that well documented, so I thought I’d illustrate these so you don’t have to go out of your mind (Question:  If I am already out of my mind, and I claim that these things drive me out of my mind, does that – make it worse, who can tell, or, and  most likely, does anyone care?  But I digress.) trying to figure it all out.

Doesn’t work #1 – variable as a numeric value

I got this (changed to something generic lest I get sued) and it supposedly (I have no reason to doubt them, I just can’t see it for myself) works in 9.3.1:

alter database sample.basic set variable CLTest 3 ;

In 11.1.2.2, that resolves to:
Hmm, I can’t remember setting a substation variable in any version of Essbase where the variable value is a number.  How oh how oh how do we do it in 11.1.2.x?  Careful (well, lucky) observation shows single quotes around the 3 above.  Could that be the answer?  Why yes it could.  

alter database sample.basic set variable CLTest '3 ' ;


And yes, you can reference CLTest as a value in a calc script.  A very interesting approach and one that I’ve never seen nor thought of.

Doesn’t work #2 – double quotes around the member name

Again, I don’t know how this works in 9.3.1; I suspect maybe not given the date of this Network54 thread that shows the solution.  In 11.1.2.2, simply sticking double quotes around a value will result in “success”, but it will also mean that the variable value will not resolve in BSO (or ASO, for that matter, although there [ and ] need to surround the member name) as BSO Essbase needs that " and " around the variable value.  What do I mean?
 

So fix it with special escape characters (this one I did not think up on my own as per above, but I thought I would illustrate it anyway).

alter database sample.basic set variable CLTest "\"Opening Inventory\"" ;


Btw, have did you notice how the leading and trailing double quotes are different?  Why?  Why doesn’t this work?
 
alter database sample.basic set variable CLTest "\"Opening Inventory"\" ;

Dunno, but this is the result.  
Go on try it.  You won’t get MaxL to execute that line.  My guess is that once "\" starts off a string, it needs to get closed with \"".  Or at least it appears that way.

Woot, woot, woot!  Geek alert, jump to the bottom to understand why the above is true.  Thank  Jason Jones for the full explanation.  A summary of the conversation (I hope you are enjoying the Christmas-y colors) is that the double quotes  in green are MaxL’s and the red \" is the escaped Substitution Variable’s  double quote.

alter database sample.basic set variable CLTest "\"Opening Inventory\"" ;

And that’s the end

I have to admit that I debated (I do actually think, or what passes for thinking for me) about writing this one but then I was consoled by the thought that this is called a “Stupid Trick”, so it isn’t as though you weren’t warned.  

OTOH, the add functionality isn’t documented anywhere, nor are the single and double quote approaches, so I think this one is worthy of a “Stupid Trick”.

Be seeing you.

The totally geeky cool addendum
Jason and I were IM chatting as we are wont to do, and this blog post came up.  Follow along and excuse the misspelt words that are the product of geeky enthusiasm.  He’s giving me a pretty good lesson in why MaxL works the way it does.  See, this blog is educational.  

Herewith the conversation:

[15:53] Jason Jones: btw, just saw your newest blog article
[15:53] Jason Jones: seriously, hit me up if you have quotes issues -- its a programming thing that i know inside and out :)
[15:53] CameronLackpour: I was writing it.
[15:53] CameronLackpour: So why does the leading quote look like "/" and the trailing quote look like /""
[15:53] CameronLackpour: That is super weird.
[15:53] Jason Jones: not at all
[15:54] Jason Jones: man, i get to school you in this quotes stuff left and right
[15:54] Jason Jones: :)
[15:54] CameronLackpour: Doesn't bother me.
[15:54] CameronLackpour: Btw, those escape codes are 100% undocumented.
[15:54] Jason Jones: i've actually run into this same thing with sub vars
[15:54] Jason Jones: well, they are undocumented, kind of
[15:54] CameronLackpour: Oh, everyone has.
[15:54] Jason Jones: if you have, say, programmed in perl it's sort of implicit
[15:54] CameronLackpour: Hmm, but MaxL isn't perl.
[15:55] Jason Jones: it's not but it has Perl/PHPish quoting semantics
[15:55] Jason Jones: so the basic idea is this
[15:55] Jason Jones: sub vars are strings
[15:55] Jason Jones: right?
[15:55] Jason Jones: they don't have a type like numeric or whatever, they are arbitrary text -- a string
[15:55] CameronLackpour: Except of course when ' and ' surround it.  Then it's a (if numeric) value.
[15:55] CameronLackpour: I verified that the '3' worked as an assign in a calc script -- it does.
[15:55] Jason Jones: well that's sort of another issue you are talking about
[15:56] Jason Jones: so in scripting languages you can quote things in different ways
[15:56] CameronLackpour: I am seeing a JJ Essbase blog post, but go on.
[15:56] Jason Jones: :)
[15:56] Jason Jones: let's invent a scripting language right now called CLScript
[15:56] Jason Jones: and in CL script you can put values into variables and you can print things
[15:56] Jason Jones: variables shall be prefixed with a dollar sign
[15:57] Jason Jones: $favoriteColor = "Blue"
[15:57] Jason Jones: print $favoriteColor
[15:57] Jason Jones: output is Blue, with no quotes
[15:57] Jason Jones: so I'll denote that as this: > Blue
[15:57] Jason Jones: now, CLScript also allows us to use single quotes to denote variable values as well
[15:57] Jason Jones: $favoriteColor = 'Blue'
[15:57] Jason Jones: print $favoriteColor
[15:58] Jason Jones: > Blue
[15:58] Jason Jones: fair enough?
[15:58] CameronLackpour: Sure
[15:58] Jason Jones: $favoriteColor = "Dark Blue"
[15:58] Jason Jones: print $favoriteColor
[15:58] Jason Jones: > Dark Blue
[15:58] Jason Jones: we have a space in our value but that's okay because as far as the CLScript interpreter is concerned, it just takes everthing between the quotes and thats the value of the variable
[15:59] Jason Jones: so it doesn't care
[15:59] Jason Jones: let's also say that in CLScript we can write this:
[15:59] Jason Jones: $favoriteColor = Blue
[15:59] Jason Jones: print $favoriteColor
[15:59] Jason Jones: > Blue
[15:59] Jason Jones: it *works* but is kind of frowned upon
[15:59] Jason Jones: CLScript decided to be nice and said "well, I don't like it but there's only one thing there so I'll pretend it has quotes around it"
[15:59] Jason Jones: but saying $favoriteColor = Dark Blue doesn't work
[16:00] Jason Jones: because the CLScript interpreter is like: Well, it looks like shit but i'll either not take it or I'll just take Dark since that's the first thing I see
[16:00] Jason Jones: so that's a toss up
[16:01] Jason Jones: CLScript is smart and takes absolutely everything in between the double quotes as its value… but what if you want a double quote inside the double quotes?!
[16:01] Jason Jones: $favoriteColor = "Dark "Navy" Blue"
[16:01] Jason Jones: it can't figure it out
[16:01] Jason Jones: so for ease of use, CLScript says, okay, if you want double quotes INSIDE your value I will let you put them into single quotes
[16:01] Jason Jones: $favoriteColor = 'Dark "Navy" Blue'
[16:02] Jason Jones: print $favoriteColor
[16:02] Jason Jones: > Dark "Navy" Blue
[16:02] Jason Jones: but what if we really have to use double quotes to quote a string that has double quotes in it?
[16:02] CameronLackpour: Like "Beginning Inventory"
[16:03] Jason Jones: CLScript says, okay, you can "escape" the quote -- put a backslash in front of a double quote and I'll now that that isn't a double quote to indicate the start and stop of your value, but literally you want a double quote
[16:03] Jason Jones: (that's another issue actually -- one sec)
[16:03] Jason Jones: so we can "escape our quotes
[16:03] Jason Jones: $favoriteColor = "Dark \"Navy\" Blue";
[16:03] Jason Jones: print $favoriteColor
[16:03] Jason Jones: > Dark "Navy" Blue
[16:04] Jason Jones: so it's not that the backslash double quote combination was the special character sequence on the outside of the value we want, it is for all the internal occurrences of a double quote
[16:04] Jason Jones: and that's why you have "\"Opening Inventory\"" instead of "\"Opening Inventory"\"
[16:04] Jason Jones: in fact, if using the latter form is not an error, it results in a value of this:
[16:04] CameronLackpour: Ooooh, I see
[16:04] Jason Jones: "Opening Inventory
[16:05] Jason Jones: with no end quote
[16:05] Jason Jones: now, all that being said there is yet one more wrinkle here
[16:05] Jason Jones: which is probably what's really biting you
[16:05] Jason Jones: aside from the double quote thign
[16:05] Jason Jones: so, we can go into EAS itself and edit our sub vars
[16:05] Jason Jones: &CurrMeasure, for example
[16:05] Jason Jones: i can go into EAS and set the value to this: Opening Inventory
[16:05] Jason Jones: notice no quotes
[16:06] CameronLackpour: hmm, would just \"Opening Inventory\" work?  I wonder why not.  Ah, because the interpreter needs the outer " and " around the internal \" Opening Inventory \".
[16:06] Jason Jones: what you say would not work
[16:06] Jason Jones: because you have two issues here
[16:06] Jason Jones: issue 1: MaxL recognizing the value and 2: the value itself from an Essbase perspective
[16:06] CameronLackpour: No, I think i get it.  MaxL pukes when there is a value with spaces.
[16:06] CameronLackpour: It needs "
[16:06] CameronLackpour:  and "
[16:06] Jason Jones: right
[16:07] Jason Jones: but here's the rub
[16:07] Jason Jones: if you put quotes around Opening Inventory and MaxL reads that, MaxL goes "okay, the thing between the quotes is the variable value"
[16:07] Jason Jones: BUT
[16:07] Jason Jones: here's the rub
[16:07] Jason Jones: and why it's more complex than that
[16:07] CameronLackpour: But then to get the escaped " and " -- that needs to go inside.  So "\"Operating Income\""
[16:07] Jason Jones: from an ESSBASE SUBSTITUTION VARIABLE STANDPOINT -- you need quotes on the subvar itself!
[16:08] Jason Jones: that's the difference between going into EAS and looking at sub vars and seeing one of these
[16:08] Jason Jones: CurrMeasure --> Opening Inventory
[16:08] Jason Jones: or
[16:08] Jason Jones: CurrMeasure --> "Opening Inventory"
[16:08] Jason Jones: if the value of the substitution variable itself doesn't have the quotes on it, it wont work!
[16:08] Jason Jones: because it looks like this when the code goes to execute
[16:08] Jason Jones: FIX(Opening Inventory)
[16:08] CameronLackpour: It'll get assigned, but just won't work./
[16:08] Jason Jones: rightio
[16:08] Jason Jones: FIX (&CurrMeasure)
[16:09] Jason Jones: straightup text replacement
[16:09] CameronLackpour: I will amend my text and give you educational credit.
[16:09] Jason Jones: and you can't put the quotes in yourself because then the FIX wouldn't be able to interpolate the variable since it wouldnt see it
[16:09] Jason Jones: ie
[16:09] Jason Jones: FIX ("&CurrMeasure")
[16:09] CameronLackpour: I know that FIX("&CurrMeasure") doesn't work
[16:09] Jason Jones: doesn't work
[16:09] Jason Jones: however
[16:09] CameronLackpour: Right, it tries to read it as a member name
[16:10] Jason Jones: and this is due to this measure having a space in the name
[16:10] Jason Jones: if it was OpeningInventory you'd be fine and not notice the issue
[16:10] Jason Jones: so probably how this manifests itself every now and then is that someone ends up googling "Error when using substiution variable with space in the name" or something to that affect
[16:10] CameronLackpour: Yes, that makes sense.  As I noted, I will amend it before Glenn jumps on it.  :)  I will give you all credit.
[16:11] Jason Jones: it's just a doubly gnarly issue when you add in the MaxL piece since you gotta get the quotes exactly right
[16:11] Jason Jones: :)
[16:11] Jason Jones: and then very, very lastly to really beat the issue to death, note that only double quoted strings interpolate variables
[16:11] CameronLackpour: What is interesting is that the client stated that "Opening Inventory" worked perfectly.  And yet when we looked in EAS we say "Opening Inventory", not Opening Inventory.
[16:11] Jason Jones: ie, we have an environemnt variable of NAME or something
[16:11] Jason Jones: so you might be able to do alter server set whatever "Name_$NAME"
[16:12] Jason Jones: and $NAME is replaced with the value of NAME
[16:12] Jason Jones: but if you do single quotes
[16:12] Jason Jones: 'Name_$NAME'
[16:12] Jason Jones: then the value of the variable is literally the dollar sign
[16:12] Jason Jones: that's why you might have to escape double quotes
[16:12] Jason Jones: to get variable interpolation as well as the quotes in your variable
[16:12] Jason Jones: alternatively in your example you could to this:
[16:12] Jason Jones: '"Opening Inventory"'
[16:12] CameronLackpour: Yes, I have seen this in MaxL with parameters.  Generally, I can throw a $1 or $2 into a string (like for file names) and so long as there are no spaces, everything works.
[16:12] Jason Jones: (single quote, double quote, value, double quote, single quote)
[16:13] CameronLackpour: But I have to encapsulate all in double quotes.
[16:13] Jason Jones: well, now you know how to do it with spaces
[16:13] Jason Jones: so if you wanted to say, pass in a command line parameter to the script that is used in the variable, like say $1 = March, you could do this:
[16:13] CameronLackpour: Thanks.  Hey, I have to run (not to hide my face in shame, but to clean out a gutter before it gets dark), but thank you.  You can correct me any time.
[16:13] Jason Jones: alter foo set to "\"Opening Inventory for Month of $1\""
[16:14] Jason Jones: no problem
[16:14] Jason Jones: glad to help a fellow nerd :)