The last one in the Groovy series. (I don’t want this to end 🙁 )
I know I been teasing everyone (in OTN forums, KScope sessions, OOW, and Network54) saying Groovy is coming to PBCS.
To give you a background on how this all started. I think it was 1 year (or maybe 2) I was at a client who had a special PBCS version and there were 3 things made their PBCS release special.
- Calc Manager variable which can populate User Name (this is now available for everyone) I did write about it, however I’m not able to find the link 😉
- Segment drop down – When you open the webform in Smartview row member selection will now show everything as a list, Segment drop down is available for everyone.
- Segment rules – you can attach a rule to each segment and it’ll execute the rule only for segments where data is changed.
We had issues with the 3rd and eventually stopped using that. The good thing happened later (I think part of that was because of the release of EPBCS), Oracle gave us a new feature where I can write a groovy rule in Calc Manager!!!!!
It has all the limitations that you can expect in a cloud product. However it is awesome!!!!
I did present about this in OOW and I was really proud seeing that my older brother from completely different set of parents was awestruck.
Some of the things that you are do with Groovy (these are from the OOW presentation)
- Intelligent calc – not the Essbase intelligence, Groovy can sense which cells were changed on the webforms and then create a calculation for only those changed cells. (Now it’s up to you to decide whether a cell level calc is good or a block 😉 )
- Self changing scripts based on user input- we’ll see a glimpse of that in this blog.
- Create a script which can use the Validation rules that you created on the webforms. Now you don’t have to replicate the same logic in Essbase using IF conditions. I can use the colors and create a rule based on that color!!! (Might change in a later release where I don’t even need color)
Those were some of things that I did at a different client (I guess there are/were only two early adopters for this feature). We had a fair warning from Oracle that this might change in later releases and we were OK with it.
Come 17.02 PBCS release I did see lot of changes, I was really happy that Oracle listened to our suggestions and gave us this great feature. OK enough of this, let’s get into action.
If you are following this series, you have a fair idea of what we are doing, if you don’t please go through
I’m not sure about when this will be released to everyone, Oracle is going through testing and receiving feedback from a large group of individuals. This might take time.
Once groovy is enabled in your PBCS POD, you’ll see a new option in Calc Manager (Script mode of Calc Manager)
Before I talk about anything else here is what I did in PBCS for this post.
Isn’t it pretty neat!!
I created a calculation script based on what the user did and it changed the rule.
Log messages : SET AGGMISSG ON; SET EMPTYMEMBERSETS ON; SET NOTICE LOW; SET UPDATECALC OFF; FIX(&NextYear, "BaseData", "Plan", "No Version", "P_000", "LII_1", "OrgCheck", "421") "BegBalance"( 2; "FlexDateStamp"=@CalcMgrGetCurrentDate(); ) ENDFIX FIX("Plan", &NextYear, "BaseData") FIX("Working", @LEVMBRS("Period",0), @REMOVE(@DESC("Account"),@RELATIVE("FlexAccounts",0)), @IDESC("P_TP"), "LII_1") DATACOPY "421" TO "460"; CLEARDATA "421"; ENDFIX ENDFIX FIX(&NextYear, "BaseData", "Plan", "No Version", "P_000", "LII_1", "OrgCheck", "440") "BegBalance"( 1; ) ENDFIX
If you look at the script I selected Sales Central as my Smartlist value (so now Sales Central is source and Sales Central is target). It doesn’t make sense to copy from source to source again, with the Power of Groovy I can stop the execution.
If this was a normal script (well it is not possible in a normal script still), I’ll still copy source to source.
This is the self changing scrip that I was talking about.
Here is my groovy script.
Line 2, I’m asking the groovy script to get what is there on my form. It gets all members that are used in the form.
Line 5, I’m asking the script to build a string.
Lines 8 to 13, I’m adding some set commands to the script builder.
Line 16, that’s what makes this calc intelligent. It tells the groovy engine to pick only the cells that got edited.
Line 22, I’m asking groovy to iterate through all the items in the GridIterator
Line 24, this is the one which gives me the member name of my Destination Smartlist (pretty neat, huh)
Line 27, I’m checking whether source and destination are equal (hey IF conditions before FIX statement 😉 )
Line 77 (completely optional), I’m asking the engine to print the calc script. This is how the output was passed to Job console.
Line 78, I’m asking the engine to run the script that was created.
There are a lot of features that are introduced (well limited for now) and some of those are used in EPBCS.
I can even run a logic, just by using Groovy (by pass Essbase calc engine). Don’t worry Essbase is going to stay and you’ll be able to write the regular calc scripts, Groovy is completely optional.
However with that powerful engine, I don’t think I’m going to loose a chance.
Great Groovy series! always extremely informative. How do you go about enabling the groovy script editor in PBCS as you have mentioned.
Oracle will have to do that for you. (at least for now)
Can we write to a file using groovy.. I tried PrintWriter printWriter = new PrintWriter(); but it gives me error Expression [ConstructorCallExpression] is not allowed: new java.io.PrintWriter() Rule PMR.OEP_FS.test’
No you cannot write to a file using groovy.
These commands which are available on PBCS/EPBCS like operation.getGrid(), can these be available on on-premise Hyperion planning once we configure groovy? Following your article, Write CDFs in Calc Scripts Using Calculation Manager Groovy Functions
No they are EPBCS specific and are not available on On-Premises. There you only get the CDF
Great post Celvin! I am beginner to Groovy. Just wonder if I would like to assign @currmbr(dim) to string variable, could you please give me some guidance. If I try assigning inside scriptBldr it does not consider as variable.
Not sure how you are doing it. You can add any string to the string builder.
Post the sample here, and I can take a look.