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.

Note:

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.

Video

Your Goal	Watch This Video
Learn about using multiple ad hoc grids in a worksheet in Smart View.	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:

  • Use the Rename Range command in the Document Contents pane of the Smart View Panel. See Renaming Ranges on EPM Cloud Multiple-Grid Worksheets

  • Use the VBA function, HypModifyRangeGridName, described in Oracle Smart View for Office Developer's Guide.

• 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.

• While working on multiple-grid worksheets, if a member name and its alias do not match, an error message appears on refreshing the grid stating the row number where the mismatched details are present. In some cases, this row number may not reflect the exact Excel row number where the mismatch occurs. This happens because grids can be placed anywhere on multiple-grid worksheets, thus making the row numbers relative.

• 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