1. User's Guide
  2. Ad Hoc Analysis
  3. Refreshing Ad Hoc Grids in Saved Worksheets

Refreshing Ad Hoc Grids in Saved Worksheets

You can refresh saved worksheets or workbooks while retaining the updates done to ad hoc grids outside Oracle Smart View for Office or by using Smart View Admin Extension version 23.210 or earlier.

Cloud data source types: Oracle Essbase, Planning, Planning Modules, Enterprise Profitability and Cost Management, Financial Consolidation and Close, FreeForm, Tax Reporting

In Smart View Options, the Refresh Saved Sheet as is (Ad Hoc Standard Mode) check box, when selected, helps retain data when ad hoc grids (EPM Cloud Standard mode or Essbase) in saved worksheets are refreshed in Smart View.

This option is useful in the following scenarios:

  • Data modified outside Smart View:

    Often, users who do not have Smart View installed in their Office applications, update data in saved sheets and send them to users having Smart View, who then refresh and submit the updated data back in their data sources. When a sheet containing ad hoc grid (EPM Cloud Standard mode or Essbase) is modified outside Smart View, that is updated and saved in Microsoft Excel without installing Smart View, and then refreshed in Smart View, the updates are retained as is if the Refresh Saved Sheet as is (Ad Hoc Standard Mode) check box is selected in Smart View Options.

    For example, a non-Smart View user modifies the year from 2020 to 2023 in the saved sheet. When you, as a Smart View user, open the saved sheet in Smart View and refresh it, the sheet is refreshed with the data values of 2023, instead of reverting back to 2020.

  • Dimension sheets updated using Smart View Admin Extension version 23.210 or earlier:

    When dimension sheets or workbooks, that were updated using the older versions of Smart View Admin Extension version 23.210 and earlier, were refreshed in the later versions, the updates would be lost and certain display issues were observed. Selecting the Refresh Saved Sheet as is (Ad Hoc Standard Mode) check box before refreshing such saved dimension workbooks ensures that the updated data is retained without any issues.

  • Formulas localized in different languages:

    Excel automatically translates localized formulas as per the locale of the Office where the Excel sheet is opened. For example, VLOOKUP in English is translated as BUSCARV in Spanish. When a saved ad hoc sheet containing formulas in different languages is refreshed in Smart View, the translated formula may not resolve properly sometimes. Selecting the Refresh Saved Sheet as is (Ad Hoc Standard Mode) check box before refreshing such sheets helps in resolving localized formulas correctly.

The Refresh Saved Sheet as is (Ad Hoc Standard Mode) check box is present in the Smart View Options dialog, in the Advanced tab under the Compatibility section. It is selected by default. So the updates in your saved sheets will be retained on refresh.

Note:

When the Refresh Saved Sheet as is (Ad Hoc Standard Mode) check box is selected, sheets with very large ad hoc grids may sometimes display performance issues or slowness during refresh. To improve performance of such large grids, deselect the Refresh Saved Sheet as is (Ad Hoc Standard Mode) check box.