1. Working with Oracle Smart View for Office
  2. Ad Hoc Analysis
  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 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.