Calculating Modified Data Using Groovy Rules

In this topic, we’ll show you how to improve business process calculation performance by using Groovy rules to calculate only the data that’s been modified.

After you update data in a form, it needs to be calculated. When you use a business rule to calculate a standard calculation script, all the data for the form is calculated. With large forms or calculations that affect large areas of the database, you might see slower calculation performance.

You can manually calculate one row at a time by right-clicking on the row. This can provide faster calculation performance, but isn’t a good option when making changes to multiple rows of data.

Groovy rules can dynamically focus on the cells that you update.

You can use Groovy rules on forms in two ways:

• When you calculate data on save, the Groovy rule generates a calculation script with a FIX statement based on the cells you updated. Then the calculation is run on the server against the modified data instead of all records on the form. As a best practice, use this option with larger forms where you are updating a lot of data.

• When you calculate data before saving, the Groovy rule calculates modified data in memory and displays the results on the form before saving them to the database. As a best practice, use this option when you have a small number of calculations to perform, or when you want to perform complex procedural calculations that are difficult to build into the outline.

In this example, bonus calculations are calculated as a percent of salary.

Let’s look at calculating on save. Notice that this form is defined with a Groovy rule to calculate on save.

After updating some of the salary data and some of the phone numbers on the form, the phone numbers are saved but don’t affect the calculation.

When you save the form to run the associated Groovy rule, the calculated bonus data is displayed on the form.

Viewing the job details, here’s the calculation script that the Groovy rule created. Notice that the FIX statement includes only the modified employees.

Let’s open Calculation Manager to see the Groovy rule. This first section iterates over the grid in the form to find Salary cells that have been edited and collects the related employee names.

This section generates the calculation script; it finds the POV members from the form, then creates a FIX statement on the POV members and the edited employees.

Now let’s take a look at calculating before save.

This is the same form, but now it’s defined with a different Groovy rule to calculate before save.

After updating the salary data and some of the phone numbers again, save the form to run the associated Groovy rule. The calculated bonus data is displayed on the form, but when you look at the job details, you can see that some of the bonus data was updated, but this time no calculation script was generated.

Open the Groovy rule in Calculation Manager.

Similar to the first rule, the script iterates over the grid in the form to find Salary cells that have been edited and collects the related employee names. Then it calculates bonus data for just the edited employees. Notice that no Oracle Essbase calculation script is generated.

Groovy rules also work with Planning forms in Oracle Smart View for Office. Smart View gives you all the power of Microsoft Excel to make mass adjustments your data, and any Groovy rules for the form set to run before or after saving are executed when you submit data.