1. Working with Oracle Smart View for Office
  2. Smart View and EPM Cloud
  3. Performing Calculations Without Having to Submit Your Changes (Calc on the Fly)
  4. Performing Calc on the Fly
  5. Performing Calc on the Fly on Ad Hoc Grids
  6. Performing Calc on the Fly - Example

Performing Calc on the Fly - Example

Let's take an example to understand how to perform calc on the fly using member formulas on an ad hoc grid.

For this example, let's take a Planning form showing salary calculations for the three months of Q1 and perform ad hoc analysis. The Jan column has data values and you want to fill in the same for Feb and Mar to see the total salary calculations for Q1.

Figure 22-1 Ad hoc grid before inserting member formulas


Shows an ad hoc grid with Jan, Feb, Mar, and Q1 columns. For the Jan column, the values are filled for Over Time Hours, Payroll Tax %, Salary, Overtime, and Bonus cells. The Total Salary row for all three months and Q1 shows #Missing as no calculations are done yet.

To perform calc on the fly, click Insert Member Formula in the Planning Ad Hoc ribbon.

Figure 22-2 Insert Member Formula option in the Planning Ad Hoc ribbon


Shows the Insert Member Formula option in the Planning Ad Hoc ribbon in the Analysis group.

The grid is refreshed and the calculated value appears in the Total Salary cell for Jan. The green triangle indicators also appear on the cells that contain member formulas. You can view the formula in the tooltip and in the formula bar when you click the cells with formulas.

Figure 22-3 Ad hoc grid updated after inserting member formulas


After inserting member formulas, the calculated value is displayed in the Total Salary cell for the Jan column. The cell also shows a tooltip with the member formula as Total Salary = Salary + Overtime + Bonus. All cells containing member formulas appear with green triangle indicators.

Now, enter some values in the Feb column for the Over Time Hours, Payroll Tax%, Salary, and Bonus cells. As soon as you tab out of these cells, the calculated value appears in the Total Salary cell for Feb. The calculations also roll up to the parent dimensions and you can see the updated totals in the Q1 column.

Figure 22-4 Changing values after inserting member formulas


Values are entered in the Feb column for Over Time Hours, Payroll Tax %, Salary, and Bonus cells. These cells appear dirty. Based on these values and the inserted member formulas, the calculated value is displayed in the Total Salary cell for Feb along with the member formula visible in the tooltip.

You can continue adding and modifying values in the grid to perform calc on the fly, without having to wait to save or submit the data or run business rules to fetch the calculations.