On the fly Calculation Scripts in EPBCS with Groovy!

So the most exciting thing recently (at least for me) was the introduction of Groovy scripting for EPBCS. I wrote an introductory post here and another LinkedIn article Along Came Groovy that shows how powerful this thing could be.

In this post I want to show how powerful Groovy scripting can be, and how can we ON THE FLY perform focused calculations on a specific data combination that is not possible with normal calculation scripts. The example given below is quite simple and straightforward, in more complex scenarios you need to take into considerations a couple of things when it comes to Groovy, most importantly it is Groovy/Java after all and not Essbase calculation script! you don't want to write a script that takes ages to execute. The concept of block/cell calculation mode is not really applicable here.

I will start with my Product Revenue data form, a simple form that allow users to enter product volume and average selling price. and calculates product revenue based on the entered assumptions (Volume * Avg price).

I also have a business rule (Groovy rule) attached to the form to run after save. Typically a business rule will simply perform the calculation (Volume * Price) for all cells regardless if data was changed or not, so in this case I want to show how to perform the calculation (in this instance Volume * Price) based on dirty cells (cells that has been changed by the user). I entered some data in the form prior to attaching the business rule because I want to show how the rule will only calculate the revenue for changed cells.

Ok, let's start with the interesting stuff; my full Groovy script which is pretty much tailor scripted to the data form I'm testing with, but it should be enough to demonstrate what is this post is all about.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

// iterate over edited cells def uniquePeriodNames = operation.grid.dataCellIterator{DataCell cell -> cell.edited}.collect([] as Set,{ DataCell cell -> cell.getPeriodName(MemberNameType.ESSBASE_NAME) }) if (uniquePeriodNames.size() == 0){ println("No cells were edited") } else{ List<String> fixMemberNames = operation.grid.pov*.essbaseMbrName List<String> editedPeriods = uniquePeriodNames String calcScript = """ Fix("${fixMemberNames.join('", "')}") Fix("${editedPeriods.join('", "')}") "OFS_Calculated" ( "OFS_Product Revenue" = "OFS_Volume" * "OFS_Avg Selling Price"; ) EndFix; EndFix;""" println("The following calculation script was executed by: " + operation.user.fullName + "\n" + calcScript) return calcScript.toString() }

I'll briefly describe what the script is doing for those of you who are not familiar with programming.

Iterate grid and get edited Periods.

// iterate over edited cells
def uniquePeriodNames = operation.grid.dataCellIterator{DataCell cell -> cell.edited}.collect([] as Set,{ DataCell cell ->
    cell.getPeriodName(MemberNameType.ESSBASE_NAME)
})

Performing the calculation for the selected POV and edited cells, if the user did not enter any data then nothing will happen.

if (uniquePeriodNames.size() == 0){
println("No cells were edited")
}
else{
List<String> fixMemberNames = operation.grid.pov*.essbaseMbrName
List<String> editedPeriods = uniquePeriodNames
String calcScript = """ 
Fix("${fixMemberNames.join('", "')}")
Fix("${editedPeriods.join('", "')}")
"OFS_Calculated"
(
"OFS_Product Revenue" = "OFS_Volume" * "OFS_Avg Selling Price";
)
EndFix;
EndFix;"""
println("The following calculation script was executed by: " + operation.user.fullName + "\n" + calcScript)
return calcScript.toString()
}

Now, let us get back to the data form, I'll save the form without entering data.

As you can see above, it looks like the business rule was run after save "Rule was run successfully", but in my script I instructed the rule not to do anything if there are no changes, and here is the job console to show that is exactly what happened.

So now we know the rule will only work if only there is a change in the data, so I'll change the Volume value in "Jan" and save.

And the job console:

Taata! that's on the fly calculation scripts in EPBCS, the first fix will contain the POV combination, and the next fix will contain the edited periods.

Now, I will change the values of four more periods and confirm the rule will work with more than one edited period.

The job console:

The calculation script is dynamic and changing based on the changes in the data form. Ok so what happens if I enter data in "Average Selling Price" account instead of "Volume".

You can see the Product Revenue Account is getting calculated for "Jun" to "Dec", that's mainly because in my Groovy script I'm iterating through the available data cells without specifying any member (for more complex cases you can iterate a specific member's data cells in the grid), and here is the script that got executed from the job console in case you're interested

Finally, just to confirm the POV selections are getting selected properly, I'll change the year POV selection from FY16 to FY17 and enter data in all periods

And finally the job console to show the calculation script,,,

Voila! that's it, I know the example given here is quite simple, but it should be enough to give you a taste of Groovy & EPBCS (in the near future, I hope PBCS). And you can see how powerful this stuff can really be! It is simply AWESOME! and frankly, I'm happy to say...


Update:

Celvin Kattookaran was kind enough to highlight I was using an alpha version of the API library, and he hooked me up with the Oracle Dev team responsible for Calculation Manager/Groovy, I had a call with Ujwala Maheshwari from Oracle and she was kind enough to go through my code and highlight the changes, and recommended another and better way to write the same code which I will elaborate in future posts. If you look again at the code in this post you'll notice some changes from the initial version (the changes and recommendations will be explained in a separate post in the very near future)

I 💓 EPBCS