Creating Functions Manually
See Microsoft documentation and support site for information about character and other Excel function limitations.
To create a function manually:
Example 25-1 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 Essbase on-premises only; Oracle Analytics Cloud - Essbase 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"
-
Do not mix a dimension name and its aliases in functions. However, for members, you can either use the member name or its alias from the selected alias table.