Guidelines for Working with Server-Initiated Refresh

Related Topics

Enabling Server-Initiated Refresh

Server-initiated refresh is enabled in the Narrative Reporting web application by default.

Requirements

To use the server-initiated refresh feature, the following components are required:

Oracle Smart View for Office 11.1.2.5.810+
Narrative Reporting extension for Smart View 19.04+
Oracle Enterprise Performance Management Cloud 19.07+

Once enabled, the server-initiated refresh options appear in the Narrative Reporting web interface. In Smart View, once connected to the enabled service, the Opt In tab is available in the Properties dialog, where you specify sheets to refresh.

Data Sources

Administrators set up data sources in the Data Sources folder in Narrative Reporting. See Creating and Editing Data Source Connections.

Workbook Size Limit

The Excel doclet or reference doclet workbook must not exceed 3 megabytes in size. If the workbook exceeds 3 megabytes in size, all check boxes in the Opt In tab of Properties dialog are disabled.

Grid Design Considerations and Guidelines

In order for server side refresh to work properly, certain considerations need to be designed in to your workbook. You may need to redesign or rebuild to fit these considerations.

Keep in mind the following considerations and guidelines when designing your grids:

Lay out worksheet elements as follows:
- Data query (ad hoc grid or form) is at the top of the sheet.
- Formulas, functions, and comment cells are placed below the data query area, unless member or data cells that are part of the query reference a formula or function.
The server-initiated refresh only recalculates and refreshes sheets containing Smart View content from the supported EPM Cloud sources.

From the Narrative Reporting web, you will not be able to select on-premises data sources for refresh, even if you selected an on-premises sheet in Smart View. On-premises data source content and non-Smart View content will be skipped.
Report package variables may be inserted into the query POV directly or through a formula that references a cell location in the same sheet or in a different sheet within the workbook.
HsGetValue functions, function grids, and dynamic data points must be created using Smart View 11.1.2.5.900 or later and refreshed once to save the connection information. Otherwise, the server-initiated refresh will not be able to evaluate the functions, function grids, or data points and a #No Connection message will be returned.

If you're not using Smart View version 11.1.2.5.900 or later, then HsGetValue functions, function grids, and dynamic data points should not be included on selected sheets.

Grid Design Limitations

Keep in mind the following limitations when designing your grids:

Do not include external formula references on sheets selected for refresh.

For example, do not include references to formulas in other network or local locations.
VBA functions are not executed.
User-defined macros are not executed.
When a refresh is initiated, cell styles defined in the workbook are preserved.

During server-initiated refresh, only the latest data values are refreshed. Existing Smart View cell styles are preserved, and no cell styles modifications are made during the server-initiated refresh.

Supported and Unsupported Grid Types

This section contains descriptions and illustrations of supported and unsupported grid types. Consider the supported grid types when designing grids in your reference doclets.

Basic Ad Hoc Grid

In the example below, the sheet contains only a basic ad hoc grid. There are no formulas, functions, pasted data points, referenced cells, or other Smart View or Excel elements on the sheet.

Supported by: EPM Cloud, Narrative Reporting

Ad Hoc Grid with Formula

You can place formula cell outside of the grid. In the example below, cell B9 contains a formula.

Supported by: EPM Cloud, Narrative Reporting

An ad hoc grid with a formula cell outside of the grid.

Ad Hoc Grid with Comment

You can place text comments in cells outside of the grid. In the example below, cell A9 contains a formula.

Supported by: EPM Cloud

An ad hoc grid with a comment cell outside of the grid.

Ad Hoc Grid with Blank Rows and Columns

You may use blank rows and columns with a grid.

Supported by: EPM Cloud, Narrative Reporting

An ad hoc grid with a blank row and a blank column running through the center of the grid. There is no data on the grid.

Multiple Ad Hoc-Grids on a Single Sheet

The multiple ad hoc grids on a single sheet scenario is not supported.

Shows two ad hoc grids on a single sheet, which server refresh does not support.

Grid with Referenced Member

This grid contains a member (cell A4) referenced as a formula from another sheet.

Supported by: EPM Cloud, Narrative Reporting

An ad hoc grid in cells A1 to C3, with a referenced member in cell A4.

Grid with Members Referenced as Narrative Reporting Variable

This grid, based on an EPM Cloud data source, contains a member (cell C1) referenced as an Narrative Reporting variable.

Supported by: EPM Cloud, Narrative Reporting

An ad hoc grid in cells A1 to C3, where cell C1 is a member cell referenced as a variable.

Grid Containing Pasted Data Points

This grid, based on an EPM Cloud data source, contains a cell range (cells C3 to E5) consisting of pasted data points. The data points can be copied from another sheet within the same workbook or from a different workbook. The data source for the data points could be based on the same data source as the original sheet, or a different data source.

Supported by: EPM Cloud, Narrative Reporting

An ad hoc grid with pasted data points. Hover over a data cell and we see the connection and POV info for the cell.

Function-Only Grid

This grid contains two functions, both based on an EPM Cloud data source. Sheets may contain functions based on different support data sources.

Supported by: EPM Cloud, Narrative Reporting

Grid contains only Smart View functions. Cell A1 is selected and the formula bar shows the formula for an HsGetValue function.

Ad Hoc Grid with Functions

This grid, based on an EPM Cloud data source, contains two functions, also based on the current EPM Cloud data source. Notice that the two functions are placed below the grid.

Supported by: EPM Cloud, Narrative Reporting

Shows a combination of an ad grid and function cells outside of the grid

Form-only Sheet

Supported by: EPM Cloud

Form with Comment and Formula Cells

This grid, based on an EPM Cloud data source, contains both comment cells and formula cells in row 5. Notice that the comment cell and formula cells are placed below the form. Sheets containing a form may contain either comment cells, formula cells, or both.

Supported by: EPM Cloud

An ad hoc grid with both comment cells and formula cells below (outside) the grid.

Formatted Ad Hoc Grid

Supported by: EPM Cloud, Narrative Reporting

An ad hoc grid using some Excel formatting, such as bolding, dollar signs, commas for the thousands separator, and decimal points.

Workbook Use Case Scenarios

In this section, we'll show you example use case scenarios based on a reference doclet workbook. The workbook contains five sheets, with each sheet containing a data query from a different data source. Data sources are both cloud and on-premises. The scenarios show you the outcome of different combinations of sheet selection when performing a refresh from the Narrative Reporting web application.

The example workbook in the following use case scenarios contains the following sheets:

Sheet 1: Planning data query
Sheet 2: Narrative Reporting data query
Sheet 3: Oracle Hyperion Financial Management data query (on-premises data source)
Sheet 4: No data query
Sheet 5: Planning data query

Scenario 1—No sheets are selected for server-initiated refresh and recalculation

Upon inspection of the workbook, there should not be any data sources shown as refreshable as nothing has been selected.

From the server side, this is a non-refreshable doclet.

Scenario 2—Only Sheet 1 is selected

Upon inspection of the workbook, the only refreshable data source is Planning.

From the server side, only Planning is shown as a refreshable data source.

Upon refresh, only Sheet 1 will be refreshed and recalculated. Sheet 5, which is also an Planning sheet, would not be refreshed or recalculated.

Scenario 3—Sheets 1 and 2 are selected

Upon inspection of the workbook, the refreshable data sources are Planning and Narrative Reporting.

From the server side, both Planning and Narrative Reporting are shown as refreshable data sources.

Upon refresh, only Sheets 1 and 2 are refreshed and recalculated. Sheet 5 which is also an Planning sheet, is not refreshed or recalculated.

Scenario 4—Sheets 1, 2, and 3 are selected

Upon inspection of the workbook, the refreshable data sources are Planning and Narrative Reporting.

From the server side, both Planning and Narrative Reporting are as refreshable data sources.

Upon refresh, Sheets 1 and 2 are refreshed and recalculated. Sheet 3 is recalculated but the Financial Management query is not impacted. Sheet 5, which is also an Planning sheet, was not selected so it is not refreshed or recalculated.

Scenario 5—Sheet 1, 2, 3, and 4 are selected

Upon inspection of the workbook, the refreshable data sources are Planning and Narrative Reporting.

From the server side, both Planning and Narrative Reporting are shown as a refreshable data sources.

Upon refresh, Sheets 1 and 2 are refreshed and recalculated. Sheet 3 will be recalculated but the Financial Management query is not impacted. Sheet 4 is recalculated. Sheet 5, which is also an Planning sheet, is not refreshed or recalculated.

Scenario 6—All sheets selected

Upon inspection of the workbook, the refreshable data sources are Planning and Narrative Reporting.

From the server side, both Planning and Narrative Reporting are shown as refreshable data sources.

Upon refresh, Sheets 1, 2, and 5 are refreshed and recalculated. Sheet 3 is recalculated but the Financial Management query is not impacted. Sheet 4 is recalculated.

Scenario 7 – Sheets 3 and/or 4 are selected

Upon inspection of the workbook, there should not be any refreshable data sources returned to the server as the selected sheets do not contain any refreshable data sources.

From the server side, this is a non-refreshable doclet.