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, currently supported versions of the following components are required:
-
Oracle Smart View for Office
-
Narrative Reporting extension for Smart View
-
Oracle Enterprise Performance Management Cloud
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.
Workbook Layout
When a workbook is refreshed, the service will process the worksheets in a sequential order. The initial pass will perform a data refresh on all selected worksheets within the workbook—Sheet1, Sheet2, Sheet3 (working left to right). A secondary pass will perform a recalculation on the selected sheets.
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 sheet.
-
If you plan to use HsGetValue functions with cell references that point to a connection name with server-initiated refresh, see Example 17-4 in HsGetValue.
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
![A basic ad hoc grid. A basic ad hoc grid.](img/eprcs_svr_ref_basic_adhoc_102x28d85789.jpg)
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. An ad hoc grid with a formula cell outside of the grid.](img/eprcs_svr_ref_adhoc_w_formula_104x28d8aa91.jpg)
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. An ad hoc grid with a comment cell outside of the grid.](img/eprcs_svr_ref_adhoc_w_comment_100x2930556d.jpg)
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. 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.](img/eprcs_svr_ref_adhoc_w_blank_row_col_102x29311d5c.jpg)
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. Shows two ad hoc grids on a single sheet, which server refresh does not support.](img/eprcs_svr_ref_multigrid_104x293160fb.jpg)
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. An ad hoc grid in cells A1 to C3, with a referenced member in cell A4.](img/eprcs_svr_ref_adhoc_w_referenced_mbr_106x293238bb.jpg)
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. An ad hoc grid in cells A1 to C3, where cell C1 is a member cell referenced as a variable.](img/eprcs_svr_ref_w_refd_eprcs_var_108x2932c6b3.jpg)
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. An ad hoc grid with pasted data points. Hover over a data cell and we see the connection and POV info for the cell.](img/eprcs_svr_ref_paste_data_points_110x29351cff.jpg)
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. Grid contains only Smart View functions. Cell A1 is selected and the formula bar shows the formula for an HsGetValue function.](img/eprcs_svr_ref_func_only_grid_112x29389462.jpg)
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 Shows a combination of an ad grid and function cells outside of the grid](img/eprcs_svr_ref_adhoc_with_func_114x293aa219.jpg)
Form-only Sheet
Supported by: EPM Cloud
![Shows a simple form. Shows a simple form.](img/eprcs_svr_ref_forms_116x293bc587.jpg)
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. An ad hoc grid with both comment cells and formula cells below (outside) the grid.](img/eprcs_svr_ref_forms_w_formula_comments_118x294365e1.jpg)
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. An ad hoc grid using some Excel formatting, such as bolding, dollar signs, commas for the thousands separator, and decimal points.](img/eprcs_svr_ref_formatted_grid_100x294f61f7.jpg)
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.