About Multiple Grids on a Worksheet

Data source types: Planning, Planning Modules, Financial Consolidation and Close, Tax Reporting

When connected to supported Oracle Enterprise Performance Management Cloud data sources, you can create multiple grids on one worksheet. These grids can be connected to the same data source or to different EPM Cloud data sources. For example, one grid can be connected to Planning and another can be connected to Tax Reporting. They can be sourced from cubes or from forms. You can retrieve data in these grids and shift them on the worksheet.


Administrators: You enable multiple-grid ad hoc for Oracle Smart View for Office users by setting the Smart View Ad Hoc Behavior option to Standard in the service application settings. See the administration documentation for your service for more information.


Your Goal Watch This Video

Learn about using multiple ad hoc grids in a worksheet in Smart View.

Video icon Setting Up Multiple Ad Hoc Grids in Smart View in Oracle Planning and Budgeting Cloud

The video references Planning as the data source, but the functionality shown is common to all EPM Cloud providers specified above.

Guidelines and Limitations for Multiple-Grid Worksheets

Note the following guidelines and limitations in worksheets that support multiple grids:

  • In order to create ad hoc grids from forms, you must have at least the User role. Check the role requirements for your individual cloud service.

  • You can submit data for only one grid at a time.

    If you try to submit data for more than one grid at a time—that is, if you have selected cell ranges in more than one grid—the first range returned by Excel will be used to determine the selected grid and the submit will be performed only on that grid.

  • If the member option, Preserve Formulas and Comments in ad hoc operations (except pivot and undo) is selected, you can preserve formulas on the sheet using the Undo option on the right-click Smart View context menu from within the grid. This option is only supported after a refresh has been performed once formulas are added to the sheet, and for only one undo action. The Undo option on the Smart View ribbon is not enabled for multiple-grid sheets. See Preserving Excel Formulas in Ad Hoc Grids.

  • Do not use Microsoft Excel's Name Manager or Name Box to rename named ranges. Instead, use one of the following methods to rename named ranges:

  • On multiple-connection, multiple grid worksheets, ensure that there is enough space between grids to accommodate pivot and zoom operations on the individual grids, especially if working with large grids.

    A loss of metadata may occur on sheets with three or more database connections and where grids are placed too close together.

  • To ensure that you are refreshing the desired range, use the Document Contents pane to first select the range, and then click the Refresh link in the pane. After refresh, select the range again from Document Contents to highlight the updated range.

  • Smart View does not support mixing Admin Extension grids and ad hoc analysis grids on a single sheet. When mixing the two grid types on a single sheet, the menu items on the ribbon reflect the last opened grid, regardless of the grid selected in the sheet.

  • These items on the Smart View ribbon are disabled:

    • Undo

    • Redo

    Instead, to access the Undo and Redo commands when working with a multiple-grid sheet, ensure that your cursor is in the affected grid. (Use the Document Contents pane to ensure that you have selected the correct grid.) Then, right-click and select Smart View from the context menu, and then select Undo or Redo, as required.

  • These items on the applicable provider ribbon are disabled:

    • Pivot to POV

    • POV

    • Preserve Format

    • Smart Slice

    • Save Ad Hoc Grid

    • Save As Smart Form

    • Insert Attributes

    • Grid Spread

    • Mass Allocate

    • More

    • Approvals

    • Copy Version

    • Run Report

    • Data Filter