1. Working with Oracle Smart View for Office
  2. Functions
  3. Creating Functions
  4. Function Syntax Guidelines

Function Syntax Guidelines

See Function Descriptions for the syntax of individual functions.

  • To work with a shared connection, you must add to the function the WSFN identifier, which specifies a workspace function, along with a connection string. The format is:

    "WSFN|ProviderType|Server|Application|Database","POV"

    For example, in the HsGetValue function for a shared connection to an Oracle Essbase data source, the WSFN identifier and connection string is added to the function as follows: 

    =HsGetValue("WSFN|Essbase|myserver|Sample|Basic","Market#South")

    For ProviderType, use one of the following case-sensitive strings:

    • Essbase (for Oracle Essbase on-premises only; Oracle Essbase cloud requires private connection syntax)

    • HP (for Planning, Planning Modules, Financial Consolidation and Close, Tax Reporting)

  • To work with a URL connection, you must add to the function the URLFN identifier, which specifies a URL function, along with a connection string. The format is:

    URLFN|URL|Server|Application|Database

    For example the syntax for the HsGetValue function using the URL for a connection to a Planning business process:

    HsGetValue("URLFN|URL|Server|Application|Database","POV")

    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 Oracle Enterprise Performance Management Cloud business processes, such as Planning, the Server token can be blank (or invalid)
    • The Essbase Server token can be blank only
    • Oracle Hyperion Financial Management requires the Server token parameter

  • Private connection parameters can have these values:

    • Empty: the default connection

    • HsActive: the active associated connection

    • The user-defined name for a private connection

    If you specify a private connection, it must precede the POV.

  • The POV is composed of dimension#member pairs, for example: 

    Entity#Connecticut

  • Parent-child relationships are designated by a period, (.); for example: 

    Entity#UnitedStates.Maine

  • The connection and each POV dimension#member pair can be split into separate function parameters, each parameter enclosed in quotation marks, ("), and separated by a comma, (,); for example: 

    "My_connection","Entity#UnitedStates","Account#Sales"

    Alternatively, the connection can be separated from the POV by a comma, (,), with the entire POV enclosed in quotation marks, ("), and with each dimension#member pair separated by semicolon, (;); for example: 

    "My_connection","Entity#UnitedStates;Account#Sales"

  • Aliases are supported in functions as follows:

    • Do not mix a dimension name and its aliases.
    • For members, you can either use the member name or its alias from the selected alias table.
    • The same alias table must be used for all aliases.
    • If using a named private connection, ensure that the correct alias table is set for the connection.
    • If an alias label has the same value as another member name, an "invalid member" message is displayed.