EPBCS Groovy and use of Templates 5

I’m working on something bigly, which I cannot reveal right now 🙂 Or I might have spilled the beans to a bunch of people ;).

I know that I’m not writing as I used to, part of that is because of the bigly item I mentioned above. I believe that could change the way we code in Essbase (or it might not 😉 )

Part of that is because of the move; I moved to a new city – Fort Mill, South Carolina.

Part of that is because of a new job that I accepted – I took a position at a manufacturing company (well I could be a potential client for some of you now 🙂 ), more update will follow as a LinkedIn profile or in a post.

Part of that is because of arranging the new house and new kid.

Well, a friend/ex-colleague  of mine asked me this “What were you thinking, just throw your life up in the air and see whether everything will fall in place?” I smiled and said, “Yeah that summarizes it pretty much.” For those who are wondering – my wife is still with me 😉 and I’m still alive 😉

Back to Hyperion 🙂 When I started writing about Groovy in EPBCS, I did mention that it is a game changer (if I didn’t, I’m saying that now 😉 ). I could see many people embracing it and using that to perform some of the steps that were unimaginable using natural calculation scripts.

That doesn’t mean Calculation scripts are less powerful. They are here to stay.

I’ve talked to numerous people and found that there is that silver lining where you should decide to Groovy or not to. 

You are the one who should make that call. I’ve worked on both and now have a clear idea when to make that call.  However, it can be compelling to go with the one that you like. It is good to have options!!!

A friend of mine came up with this question, and it was pretty simple, I’ve always used templates in Calc Manager so that I can reuse code, how can I do that in Groovy?.

For those who moved from the HBR world and are still getting acclimatized to Calc Manager world, that will be macros for you.

You can use templates in Groovy and here is how you can do it. (Calculation shown here is only for demonstration purpose, not a real world calc)

Creating a Groovy template

DTP in groovy

What you’ll find is (or what you’ll not find) there are no groovy variables in DTP designer when you start creating one for Groovy. However, you can create them as strings.

I’ve created 3 DTPs as strings and here is my template designer.

All I’m saying in the template script is to add to stringbuilder DTP.

Now the template is ready. Let’s get to our Groovy script.

Some of the comments are self-explanatory.

Line 5 is where the brilliance of Groovy comes in you can run code on just about anything (that’s a stretch. However, you can run that on edited cells, read-only cells and so on)

Line 7 is where you can define your runtime prompts. Earlier version that must be the first line of code (that changed recently, and you can have multiple lines, and anywhere you want to place it)

In this flavor of Groovy, we are still generating a calc script to run against BSO, and some people were struggling with what happens when the user didn’t change anything or what if my Groovy conditions didn’t produce anything that created a calc script. Well as you know an empty calc script is going to fail in BSO world and here is how I trick it (all the time 😉 )

I run a bunch of SET commands, and my scripts are never empty 😉

So that’s what lines 13 to 18 are

Line 21, this is where Groovy and Essbase starts to comingle. Did I mention that it is not for the light-hearted 😉

Groovy process it everything as cells. However, for BSO you’ve to be considerate of blocks, and that’s why you need to think like an Essbase guy and code like a java guy, that’s where HashSets are used. It will not allow you to store duplicates and all I’m doing is to store my sparse combinations (which appears in rows) in this HashSet and then use them to generate my script.

Lines 23 to 27 is commented out; I kept that there to show you that instead of using Sub vars I can now pick up the Year, Scenario, and Version from the form itself.

Lines 30 to 37 is where I’m iterating through my edited cells and populate my HashSet.

Lines 39 to 54 is where the Calc Script is generated; I’m now looping through the HashSet and generating my calc script.

Line 43 is where I’m getting the values from my run time prompts Version and Scenario (remember we didn’t use the Groovy approach – it is commented out)

Line 47 is where I’m populating which user is editing that line, this is not possible in the normal calc script because I don’t know which line is edited!!!!

Line 49 is where I’m calling my template and passing DTPs. I passed

• curyr as &CurrYear – Essbase Sub Var
• nxtyr as &NextYear – Essbase Sub Var
• stringbuilder as scriptBuilder – Groovy StringBuilder object

Line 56 I’m printing the script to the console – completely optional

Line 57 is where you are passing the generated calc script to Essbase to execute.

Let’s see a video of what happens in real time

If you look at the job status, you’ll see that my name will show up there. This is from user variable calc we did in line 47.

If you’ve print statements in your groovy script that will show up in the status and you can view them by clicking the “Completed” link.

That’s our good old calc script 🙂

Same script in Notepad++