Creating Reference Variables

Reference variables enable you to reference a source in another document within the report package, such as a single Excel cell or selected Word text, and use them as reference variable value sources in your report package doclet. You can then insert the reference variable into one or more doclets. When the reference variable source value is updated, all inserted instances of the variable are automatically updated.

Note:

  • Images and shapes are not supported as reference variable values.

  • Reference variable values cannot be sourced from other reference variables.

  • Reference variable values cannot be sourced from embedded content, where a range from an Excel reference doclet is embedded in Word or PowerPoint.

  • Reference variable values sourced in Excel can have a maximum size of 255 characters.

  • Reference variable values sourced in Word can have a maximum size of 2,000 characters. Reference variable values sourced in Word cannot be inserted into Excel if the number of characters is greater than 255.

  • Reference variable values must be single line only. In an Excel spreadsheet, select only one cell. In Word, select text from only one line or one table cell.

  • Reference variables cannot be sourced from any PowerPoint content.

Use Excel formulas in Oracle Smart View for Office to generate reference variable values, such as the following examples:

  • Use an IF function to return a string of "increase" or "decrease," or "above" or "below", if a data value increases or decreases between the current and prior period. Insert the Excel-based directional word variable or results into a Word paragraph.

    =IF(B8<0,"below","above")

  • Use an IF function, or simple subtraction, to compare the occurrence of a data value (such as "Revenues" or "Summary Income Statement") that is displayed multiple times throughout a report package.

    ='Summary Income Statement'!B4

  • Use Excel formulas to calculate the variance or variance percent of changing data values from period to period, and insert that variance into a Word paragraph.

    ='Summary Income Statement'!B4-'Summary Income Statement'!C4

If the value source is updated, each instance of the variable will reflect the current information when the variable is updated in Smart View.

The Name and Description of the reference variable can also be created in the Oracle Enterprise Performance Reporting Cloud web interface; however, you must add the variable source and value in Smart View, and the variable must be inserted into the doclet using Smart View.

Note:

Before you begin, ensure that you have opened a report package, and opened and checked out a doclet.

To create a reference variable:

  1. Open a report package, and then open and check out a Word doclet or Excel reference doclet.
    You can just open a report package to create a reference variable, without checking out a doclet, and define the reference variable name and description; but the doclet needs to be checked out to define a source value from the doclet as the reference variable value.
  2. In the doclet, select the Excel cell or Word text or table cell to reference in your new reference variable.

    For example, in the following grid, cell B13 is selected. The value in cell, including the data source POV, is the value for our new reference variable.


    A grid with the B13 cell selected. B13 is the total of the Actual Expenses column

    Note:

    You can always change the reference variable value later.

  3. In the drop-down list in the Report Package panel, select Variables.

    Shows the options available in drop-down list in the Report Package. Options are Report Center and Variables
  4. In the Report Package panel, click the Create New Variable button, The plus symbol signifying the Create New Variable button in the Report Package panel, and then select Reference Variable from the drop-down list.

    The report package panel displays Create Reference Variable, where you enter the variable name and value, and an optional description. As you can see, the value is already pre-populated with the value we selected in step 2


    The Report Package panel, where an area called Create Reference Variable is now displayed with Name, Description, and value fields. The value field reflects the value of the data cell that was selected when the Create Reference Variable command was invoked.
  5. Define the reference variable as follows:
    1. Enter a Name for the reference variable.

      The name must be unique across all variables within the report package, and should be descriptive of the variable to make it easier to find.

    2. Optional: Enter a Description for the reference variable.
    3. Click The Use current selection as reference value button. The button is a blue arrow that points to the right, located just under the Value field in the Create Reference Variable area of the panel to use the value of the currently selected cell as the reference value.

      Optionally, you can select another cell on the sheet, verify that the value is changed in the Value field, and click The Use current selection as reference value button. The button is a blue arrow that points to the right, located just under the Value field in the Create Reference Variable area of the panel

  6. Click the OK button, The OK button, a green check mark, to add the variable to the report package.
  7. Upload and check in the doclet for the variable value to be added to the system.

    Note:

    If the doclet is not checked in, the value will continue to be displayed in the variables panel as Not Committed for the current doclet and other doclets in Smart View. In the Oracle Enterprise Performance Reporting Cloud web interface, the value is displayed as #Missing.