1. User's Guide
  2. Functions
  3. Creating Functions
  4. Creating Functions in the Function Builder

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. If you know argument input values, you can create functions in offline mode; that is, when not connected to a data source. Type-in functionality is available for each argument. For more information, see Function Descriptions.

Note:

You can use functions that were created in the Function Builder prior to Release 11.1.2.2.310. However, starting with Release 11.1.2.2.310, the Function Builder uses commas (,) to separate member list arguments rather than semicolons (;). Both characters are supported by the new Function Builder, but when you modify a function created in the previous version, you are prompted to convert to the new comma-separated format. If you choose not to convert, then none of your modifications to the function are applied.

To create functions using the Function Builder:

  1. Connect to the appropriate data source.

    Function Builder supports shared, private, and URL connections. As a best practice, ensure that you are connected at the cube level. If you are connected at the application level, you may get a "Cube Not Selected" message when you use Smart View functions such as HsGetValue or HsSetValue. However, for Oracle Hyperion Financial Management, you can continue to connect at application level to use Smart View functions.

    Oracle Essbase: If you are using a private connection, and your function is going to use an alias table, you must verify that the correct alias table or the "Default" alias table is set for the connection. In the Smart View Panel, right-click the database name, and select Set alias table. Ensure that the correct alias table is selected, and then refresh the sheet.

  2. In the sheet, select the cell in which you want to enter the function.
  3. In the Smart View Panel, navigate to the database on which you want to base the function, right-click the database name, and then select Build Function.

    Accessing the Function Builder in this way will prepopulate the Connection field of the Function Arguments dialog box with specific connection information, as described in step 4.

    Alternatively, from the Smart View ribbon, select Functions, and then Build Function.

    When you access the Function Builder in this way, the Connection field in the Function Arguments dialog box is blank. You may provide connection information, as described in step 4. Or, if there is an active connection on the sheet, select HSACTIVE from the drop-down list.

  4. In Select Function, select a function from the list and click OK.

    Figure 17-1 Function Builder, Select Function Dialog Box


    The Function Builder, Select Function dialog box, with HsGetValue, selected as described in this section

    Note:

    Not all functions are available to all providers. See Table 17-1 for a list of functions and the supported providers.

    The Function Arguments dialog box is displayed with the Connection field automatically populated with connection information in one of the following formats.

    • For shared connections:

      WSFN|ProviderType|Server|Application|Database

      The WSFN parameter above signifies that this function is a workspace function and uses a shared connection. Workspace functions support Oracle Hyperion Enterprise Performance Management Workspace or Oracle Fusion Cloud Enterprise Performance Management connections.

    • For private connections:

      PrivateConnectionName

    • For URL connections:

      URLFN|URL|Server|Application|Database

    All the information needed to make a connection is part of the token, as shown above. If the token contains any invalid information, then generally a #NoConnection error is shown, with the following exceptions for the Server token:

    • For Cloud EPM business processes, such as Planning, the Server token can be blank (or invalid)
    • The Essbase Server token can be blank only
    • Financial Management requires the Server token parameter

    To manually enter the connection info, use the syntax above.

  5. In Function Arguments, for each argument in the selected function, do one of the following:

    • Click the right-most button, Member Selection button, opens the Member Selection dialog box.

      For example, for HsGetValue, click Member Selection button, opens the Member Selection dialog box to select members from Member Selection; for HsLabel and HsGetVariable, select from drop-down lists of labels or variables.

      Essbase: Note that when variable names are duplicated, their fully qualified name is displayed in the drop-down list. This helps to identify variables defined at the global, application, and database levels.

    • To type arguments manually, enter the argument in the text box. For example, to type a member, use the format: dimension#member; such as Year#Qtr1 or Year#Jan.

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

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

    Figure 17-2 Function Builder, Function Arguments Dialog Box for HsGetValue Based on the Essbase Sample Basic Database


    The Function Builder, Function Arguments dialog box for the HsGetValue function. There is a Connection field and fields for entering dimensions and members for each function argument.

    The arguments listed in Function Arguments vary depending on the function selected in the previous step.

    Note:

    Consider the following best practices:

    • For HsGetSheetInfo, use a cell reference. See Using Cell References for more information.

    • For HsSetValue only: Select Data or Cell Reference and enter the value to submit.

    • For HsGetText and HsSetText only:

      • Select Comments or Cell Reference, and then enter the cell text to submit

      • Select Cell Text Label, and then select a label from the drop-down menu

    • If an active worksheet connection is available, you can select HSACTIVE from the Connection drop-down list.

  6. Click Validate and correct any errors that may be noted.

    Some of the errors 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

    Figure 17-3 shows errors in the Function Arguments dialog box after clicking the Validate button. In the following example from the Financial Management Simple application, there are errors in the syntax for two of the Dimension#Member combinations. The first error is that the Dimension name is invalid or not matching the selected alias. The second error is that the member name is invalid or not matching the selected alias.

    Figure 17-3 Function Builder Validation Errors


    The Function Builder, Function Arguments dialog box for the HsGetValue function. There are errors in the syntax for two of the Dimension#Member combinations. The first error is that the Dimension name is invalid or not matching the selected alias. The second error is that the member name is invalid or not matching the selected alias.
  7. When the function validation is successful, click OK to insert the function in the selected cell.
  8. To execute the function, follow the procedure in Running Functions.