Get Balance
The Get Balance function is probably the most important function in GL Wand. It returns a General Ledger balance individually or summed based on the parameters that you enter. Clicking the Get Balance function on the GL Wand toolbar will open the Get Balance 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 or Business Unit Tree Node as configured in PeopleSoft. The available Business Unit Trees will be displayed in this selection. To change the Business Units available to select, use the Change Business Unit function in the GL Wand toolbar.
When using a Business Unit Tree Node, the following criteria must match:
If your Business Units do not meet these criteria, you can either build separate GetBalance functions in different columns and Sum them using Excel functions or you can add multiple GetBalance functions within a single cell by placing a between the functions. The |
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 Type & Currency Code | Currency Type: Enter BASE or TRN all in upper case.Currency Code: The currency code as configured in PeopleSoft all in upper case e.g. “USD”.
Examples:
Where BASE is used along with multiple Business Units, the defined Ledger must share the same Base 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. Where Multiple Business Units or Business Unit Tree Nodes are entered, each of the queried ChartFields must share a common SETID. Any ChartFields with security applied must also share SETIDs. Running the Configuration Sheet (available in version 1.2.6) will assist with identifying the SETIDs for Business Unit and ChartField combinations. It will also advise whether ChartFields have been secured in PeopleSoft.
|
Budget Period | Optionally, enter the Budget Period as configured in PeopleSoft. This field will only become available when selecting a budget ledger. |
Scenario | The scenario 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 multiply 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. |