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. Type-in functionality is available for each argument.
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:
Connect to the appropriate data source.
Function Builder supports shared or private connections.
In the sheet, select the cell in which you want to enter the function.
In the Smart View Panel, navigate to the database on which you want to base the function.
From the Smart View ribbon, select Functions, and then Build Function.
Alternatively, in the Smart View Panel, right-click the selected database name and select Build Function.
In Select Function, select a function from the list and click OK.
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.
For private connections:
PrivateConnectionName
If you want to manually enter the connection info, use the syntax above.
In Function Arguments, for each argument in the selected function, do one of the following:
Click the right-most button,
.For example, for HsGetValue, click
to select members from Member Selection; for HsLabel and HsGetVariable, select from drop-down lists of labels or variables. 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 40, Function Builder, Function Arguments Dialog Box for HsGetValue Based on the Essbase Sample Basic Database shows a completed Function Arguments dialog box for the HsGetValue function.
Figure 40. Function Builder, Function Arguments Dialog Box for HsGetValue Based on the Essbase Sample Basic Database
The arguments listed in Function Arguments vary depending on the function selected in step 5.
Notes:
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.
To execute the function, follow the procedure in Running Functions.
You can enter references to single cells that contain member names, connection names, variable names, and labels. References cannot be made to cell ranges.
To use cell references:
In Function Arguments, for each argument in the selected function, click the
button next to the MemberList text field to open Cell Reference:In the grid, click the cell that contains the member name, then click OK in the Single Cell Reference dialog box.
In Function Arguments, the text field for the argument contains the referenced cell in the form, ""&A3&""
If the member name that you selected in step 3 is displayed as dimension#member in the grid, then the argument selection is complete. For example, if the member is displayed in the grid as Year#Qtr 2 in cell A3, then ""&A3&"" is complete.
If only the member name is displayed in the grid, then you must manually enter the dimension name followed by # between the first two sets of double quotation marks. For example, if the member is displayed as Qtr2 in cell A3, then you must enter Year# between the quotation marks: "Year#"&A3&""
Note:
If an argument text field contains text before you select a reference cell, the cell reference text is appended to this text. Therefore, delete any unwanted text in the field before selecting a cell for reference.
