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. Guidelines and Considerations

Guidelines and Considerations

Consider the following guidelines while performing calc on the fly on ad hoc grids and Forms 2.0.

  • On inserting member formulas, the cells containing the formulas appear read only and their cell styling color also changes accordingly. Further, if you click Refresh, the same cells turn dirty displaying the assigned cell style color. To get a clean refresh without cell style color changes, click Options on the Smart View ribbon and under the Member Options tab, clear the Preserve Formulas and Comments in Ad Hoc Operations check box.
  • As a best practice, Oracle recommends that you insert member formula only once the ad hoc grid layout is set or finalized. If you perform other ad hoc operations such as Zoom In, Zoom Out, Keep Only, and Remove Only, the formula context becomes invalid and it is removed from the grid. If a formula is missing after performing ad hoc operations, you can manually enter the formula again where required.
  • After inserting formulas, if you make any changes to the alias table by refreshing or by clicking Change Alias and changing the alias from Default to None, or vice versa, the formulas are no longer shown in the tooltip when you click cells with formula.
  • Careful consideration should be given to combining the use of cell-level security and member formulas. If cell-level security is enabled, the inserted member formulas can give different results in the web application and in Oracle Smart View for Office. To perform calculations, access is required to all cells. So, the cells restricted by Deny Read rule when cell-level security is enabled may give inconsistent results. The #No Access label is shown for the restricted cells. If any formula contains reference to such access restricted cells, the results display either #Missing or an error.
  • The operation of inserting member formulas cannot be undone using the Undo option on the Smart View ribbon. If you click Undo after inserting formulas, the #No Access label appears in cells containing member formulas and the formulas are no longer shown in the tooltip when you click such cells. Instead, to refresh the grid and remove the inserted member formulas, you can use the Refresh option on the Smart View ribbon instead. Also, clear the Preserve Formulas and Comments in Ad Hoc Operations check box to get a clean refresh. This check box is present in the Member Options tab under Options.
  • If you remove member formulas from an ad hoc grid, you cannot reinsert them back using the Redo option on the Smart View ribbon. This is because member formulas are not preserved during the Undo or Redo operations. To insert member formulas, you can open the ad hoc grid again and click Insert Member Formula.
  • When the Time Balance property is set to Flow with the Skip option set to Zeros or Missing and Zeroes, the results are not coming as expected. In a grid with January, February, and March columns, the first row has 0, 0, and 0 values, and the second row has 0, #Missing, and #Missing values respectively. In the fourth column of Q1, member formulas are inserted such that in the first row Skip is set to Zero and in the second row Skip is set to Missing and Zeroes. The expected result is #Missing for both rows, but the actual result appears as zero.
  • Non-unique or duplicate aliases present in a grid and referenced in formulas can cause duplicate alias error while performing ad hoc in Smart Forms. This happens when you insert member formulas in an ad hoc grid which has duplicate aliases, save it as a Smart Form, and open the Smart Form again in ad hoc mode. On clicking Refresh, Smart View shows an error message "Found duplicate aliases or member name conflicting with alias under parent Account. One or more of these aliases or member names need to be modified to resolve the ambiguity".
  • For data with #Missing and zero values, @MAX function calculation may give inconsistent results in web application and in Smart View. This is due to the difference in the way Oracle Essbase and Excel handle these values. In Essbase, a comparison between zero and #Missing might not always be the same, so using @MAXS function is recommended. For more information, see the Notes for @MAX function in Calculation and Query Reference for Oracle Essbase. @MAX behaves like @MAXS (SKIPNONE), so when only #Missing and negative values are present in data, #Missing value is considered to be greater than negative value. #Missing is considered as zero while comparing with non-blank values. Smart View uses the #Missing label to denote missing data values in the database, which is different from a zero data value. Excel treats a non-blank label as a string, and strings are treated differently from empty cells and zeros. Due to this difference between Excel and Essbase, the comparison of #Missing and zero data values remains indeterminate.
  • When you sort a grid in ascending or descending order, the structure of the grid is changed. This makes the member formulas and functions present in the grid invalid. Now, if you enter or modify values in the cells referenced in a formula or a function, the calculations are not refreshed immediately in the cells containing the formula or function. You need to save the grid to see the calculated or modified values.
  • If the cells referenced in a formula contain text strings or dates, then the results obtained from calc on the fly calculations may be different from those calculated in Essbase. This difference happens because Planning stores a numeric index at the cell location for non-numeric data (like text, date, or Smart List) that Essbase uses for calculation, whereas calc on the fly uses the actual displayed value of text, date, or Smart List for calculation and treats non-numeric data as missing.
  • Any non-numeric values like text, date, and Smart List options present in cells are treated as missing for the purpose of calculation in Excel. So, if such cells are referenced in functions and formulas, then the result is returned as #missing. This happens because while Essbase can interpret such cells and calculate results correctly, the same ability is not available for calc on the fly calculations.
  • If a cell has a formula due to a member formula or outline math, the non-level 0 cell will be writable, because an entered value can be back spread to the writable source cells while performing calc on the fly calculations in the web application. Note that back spreading of formulas is not supported in Smart View.
  • For member formulas to display for a cell for calc on the fly, the members contributing to the formula must exist within the grid. Otherwise, the formula does not display in the tooltip.
  • If a formula's evaluation always results in a constant value (for example, multiplying by zero or #missing), then the formula is not generated.
  • For Forms 2.0, calc on the fly is supported only for BSO and Hybrid BSO cubes. For Smart View, the application setting, Forms Version, must be set to Forms 2.0.
  • Ensure that design-wise, forms using calc on the fly use only a reasonable and relevant number of members on the grid to ensure quick turnaround time from the ensuing calculations.