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 Configuration Sheet function available in version 1.2.6 will provide useful information to assess which Business Units can be used concurrently. |
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 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. |