Creating Functions in the Function Builder

In the Function Builder, you select a function and specify the connection and members that you want the function to use.

The Function Builder then creates the function using the proper syntax and enters it into the selected cell. You can edit these functions.

The selections available to you in a given Function Builder field are limited by your selections in other fields of the Function Builder. For example, only the connections supported by the selected function are displayed, and only the dimensions supported by the function you select are displayed.

A cell reference can be selected for each function argument. Type-in functionality is available for each argument.

To create functions using the Function Builder:

  1. Connect to a data source using the instructions in Connecting to Smart View (Mac and Browser).
  2. Select a cube and, in the connection dialog that displays, select Set as Connection for Functions, and then type a name for the connection.

    In Figure 17-1, the connection name is MyVision.


    You must create a connection name for each new workbook.

    The connection name is saved with the workbook. When you reopen the saved workbook, you will not be prompted to enter a connection name.

    Figure 17-1 Connection Option and Name

    Connection dialog showing three options, Ad hoc analysis, Set Active Connection for this Worksheet, and Set as Connection for Functions. Also, a Name field where you enter the connection name to use for functions
  3. In the sheet, select the cell in to which you want to enter the function.
  4. In the Smart View ribbon, click Functions and then click Build Function, Build Function button, to launch the Function Builder panel.
  5. In the Function Builder panel, choose a function from the list and then click Select.


    Currently, Oracle Smart View for Office (Mac and Browser) supports these functions:

    • HsGetValue
    • HsSetValue
    • HsAlias

    In the example in Figure 17-2, the HsGetValue function is selected.

    Figure 17-2 Function Builder Panel, Supported Functions List

    The Function Builder showing the supported functions list, where you select a function to work with. HsGetValue is selected

    After clicking Select, the Function Arguments panel of the Function Builder is displayed.

    Figure 17-3 Function Builder, Function Arguments Panel

    Default Function Builder panel, all dimension and member argument fields are empty
  6. In Connection, enter the connection name you specified in step 2.
  7. In Function Arguments, add an argument for each dimension in the cube using any of the following methods:
    • Click the Select Member button, Member Selector button, opens the Member Selection dialog box, next to the argument text box, and in the Member Selector, select a dimension and member. Repeat for each dimension in the cube.

    • Enter the argument manually by typing the dimension and member pair in the text boxes using the format: dimension#member. For example, Year#Qtr1 or Year#Jan. Repeat for each dimension in the cube.

    • To use cell references, follow the procedure in Using Cell References.

    Figure 17-4 shows a completed Function Arguments dialog box for the HsGetValue function.

    Figure 17-4 Function Builder Panel Function Arguments for HsGetValue Based on the Planning Vision Sample Database

    The Function Builder panel for the HsGetValue function. There is a Connection field and fields for entering dimensions and members for each function argument.
  8. Click Validate and correct any errors.

    Errors are noted in the Function Builder panel, next to the problem areas.

    Some of the errors that you may see are:

    • Connection offline or invalid
    • Invalid selection
    • Member name is invalid or not matching selected alias
    • Dimension name is invalid or not matching selected alias
    • Incomplete Dimension#Member combination
    • Generic "Error" in cases of missing quotation marks or other minor syntax errors

    For example, in Function Builder Validation Errors, we show simple spelling errors that resulted in an invalid Connection name, an invalid dimension name in one argument, and an invalid member name in another argument.

    Figure 17-5 Function Builder Validation Errors

    Spelling errors in the Connection name, a dimension name, and a member name resulting in an inline message next indicating the issue.


    Validate only works with dimension#member combinations that are hand-typed into the text boxes and does not apply to those dimension#members that are added using the Member Selector dialog. If all dimension#member combinations are entered using the Member Selector, then no validation messages regarding those combinations are displayed. You may still see an error regarding the connection name if it is misspelled. If the connection name is correct and all the other dimension#member combinations were added using the Member Selector dialog, then a "Successful Validation" message will not display. If the connection name is correct and one or more dimension#member combinations were hand-typed correctly, then a "Successful Validation" message will display.

  9. Correct the errors and click Validate again.

    When the function validates correctly, a "Validation successful!" message is displayed; for example:

    Validation successful message
  10. To execute the function, follow the procedure in Running Functions.