GetBalance KK
The Get Balance KK function is currently in Beta. Please use caution when applying this. It returns a Commitment Control balance individually or summed based on the parameters that you enter. Clicking the Get Balance KK function on the GL Wand toolbar will open the Get Balance KK Wizard.
The following options are available in the Function Wizard:
- Select a value from the drop-down list. These lists are populated with the available values for each parameter or with the configuration data read directly from PeopleSoft.
- Click the button to link the parameter to an Excel cell.
- With an Excel range reference in a parameter, use the F4 key to toggle the absolute and relative addressing as you would in Excel.
Parameter | Description |
---|---|
Business Unit | The Business Unit as configured in PeopleSoft. A list of the currently activated Business Units will be displayed in this field. To change the Business Units available to select, use the Change Business Unit function in the GL Wand toolbar. |
Ledger | The Ledger as configured in PeopleSoft. The list of ledgers available for selection will be limited to those linked to the value selected in the Business Unit field. |
Fiscal Year | The Fiscal year as configured in PeopleSoft. |
Accounting Period | The Accounting Period as configured in PeopleSoft. |
Balance Type |
YTD, QTD, PTD all in upper case.
|
Currency Code | The currency code as configured in PeopleSoft all in upper case e.g. “USD”. Use the “%” wildcard character with Currency Type BASE to return the balance from all transactions in the chosen Ledger’s base currency, regardless of the currency the transaction was captured in. |
Currency Type | Enter BASE or TRN all in upper case. Use BASE to retrieve the balance in the selected ledger’s Base Currency. Use TRN to express the balance in the Transactional currency. |
ChartFields | The ChartField values as configured in PeopleSoft. Enter the ChartField value here. You must enter it as a text value including any leading zeros e.g. enter ChartField value “10” as “0010” if this ChartField is configured as four characters with leading zeros in PeopleSoft. You may enter your ChartField values in a variety of flexible ways including Tree Nodes, ChartField values, ranges, wildcards, excludes etc. To include all values simply enter “%”.
When creating Get Balance formulas always use “%” to denote all values as this allows more efficient SQL to be generated. Do not use a range like “000-ZZZ” as they will require unnecessary processing. The segment value parameters can be entered as:
The GetBalance will only show the ChartFields that are set as active in PeopleSoft. The wizard will only show the ChartField values for the selected Business Unit and Ledger. |
Source Type | Optionally, enter the Source Type as configured in PeopleSoft. |
Budget Period | Optionally, enter the Budget Period as configured in PeopleSoft. This field will only become available when selecting a budget ledger. |
Budget Type | Optionally, enter the Budget Type as configured in PeopleSoft. This field will only become available when selecting a budget ledger. |
Multiply | Use this parameter to switch the sign of the balance by multiplying by -1. Also useful to show the number in a factor e.g. thousands by multiplying by 0.001.
Using this parameter, rather than equivalent Excel functionality, ensures that when the Get Balance function is returning a message, this message will be displayed in the cell. For example, if you capture your Get Balance function as =GLW_PSFT_Get_Balance(…) * -1 and the Get Balance result is Error (Period) due to an invalid period being entered, the cell will display #VALUE. If you use this parameter to multiple the balance by -1, the cell will return the
Error (Period) message. |
Decimal Places | Round the balance to the specified number of decimal places. Useful to use in conjunction with the Multiply parameter to show the number in a factor e.g. thousands by multiplying by 0.001 and then round to zero decimals. See the Multiply parameter for an explanation of why it is recommended to use this parameter rather than Excel functionality to round the balance. |