Contact Us
Index
Version 20.3
Version 20.4 Version 21.1 Version 25.2
Contact Us
Wands for Oracle Cloud 20.3 – Home
insightsoftware
Installation
Logging On
Insert insightsoftware Function
Combine Text
Last Refreshed By
Last Refreshed Time
Split Text
Tools (insightsoftware)
Snapshot
Shared Templates Drive
Drive Types
Dropbox
File Server
Cloud Connector
Navigator
Create a File
Delete a File
Edit a File
Open a File
Common Options
Administrator
System Administrator Options
System Administrator User Options
System Administrator Delimiter Options
System Administrator Configuration Options
System Administrator Server Information
About
Exit
Help and Shortcuts
Excel Error
GL Wand
Ledger and Segment Selector
Discover
Discover Segment Values
Discover Level
Discover Period Names
Discover Segment Properties
Expand and Explode
Expand Segment Values
Expand Segment Value Hierarchy
Explode Segment Values
Explode Segment Value Hierarchy
List of Values
List of Values – Create
Insert GL Wand Function
Account Type
Enabled Flag
Functional Currency
Get Average Balance Summary
Get Balance
Get Balance Options
Multiple Ledgers
Ledger Name
Ledger Set
Next Segment Value
Parent Flag
Period Name
Period Offset
Previous Segment Value
Segment Description
Segment End Date
Segment Tree
Segment Start Date
Calculating
Execute Drilldown
Drill Criteria Sheets
Drill Criteria – Balance
Drill Using a Saved Template
Customize Drilldown
Edit Template Layout
Save Template Layout
Manage Templates
GL Wand Tools
Other Tools
Clean Template
Create a Template
Template Storage Requirement
Edit a Template
Delete a Template
Hide Zeros
GL Wand User Options
chat icon - ignore
Live Chat
Chat Offline

Get Balance

Play Video

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 Oracle Cloud.
  • 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
Period Name The period name as configured in Oracle. This parameter must be entered in exactly the same format as the period names in Oracle. For example if your period names are configured as follows “Jan-07” in Oracle and you enter “Jan-2007” or “January-07” or “jan-07” for this parameter, GL Wand will not return any data, and will instead return an Error (Period) message.

When used in conjunction with the special Balance Type CTD (Custom to Date) you can specify a custom period selection. For example “Feb-07-Aug-07” will add all the movement for the entered period range. You may enter multiple lists or ranges of periods and can use wild cards as well e.g. “Jan-0%” or “%-07”.

Balance Type YTD, QTD, PTD, PJTD, CTD, FYS or FYE all in upper case.

  • YTD – Summarizes the full years balance
  • QTD – Summarizes the balance for the quarter
  • PTD – Includes just the current period’s movement
  • PJTD – Project to date
  • CTD – Summarizes the custom period criteria entered
  • FYS – Financial year start, valid with actual balances only.
  • FYE – Financial year end
Currency The currency code as configured in Oracle Cloud all in upper case e.g. “USD”.
Translated Flag Enter E, T, C or Total. Use E to retrieve the entered amount and T for the translated amount. Use C to report on the converted amount i.e. the functional currency equivalent of a foreign currency amount.

Only Total is valid if requesting a balance for the functional currency of the current Ledger.

You must ensure that you have run the currency translation process in Oracle for the selected currency and period before reporting on translated balances. If you enter T to request the translated balance and there is no current translated balance available in the system a zero is returned.

If you use the E (entered) flag in conjunction with the functional currency, any converted amounts will be excluded. To report on the total balance (including values entered in functional currency and values converted to the functional currency) use the Total option.

Actual Flag A – Actual
Budget Currently not supported
Segment1 through Segment20 Enter the segment value here. You must enter it as a text value including any leading zeros e.g. enter segment value “10” as “0010” if this segment is configured as four characters with leading zeros in Oracle Cloud. You may enter your segment values in a variety of flexible ways including parents, child 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:

  • a single value representing a child or parent account e.g. “1500” might represent a parent account.
  • a range of values e.g. entering “1500-1600” in one of the segment parameters will sum all the segment values between 1500 and 1600 for that segment.
  • a list of segment values e.g. entering “1500,1510,1590” in one of the segment parameters will sum the specific segment values entered. If any of the segment values are parents GL Wand will sum the hierarchy for that parent.
  • wild card entries e.g. 1%00 will sum 1100, 1200, 1300, 1400 etc. or entering the value 1% will sum all values that start with 1.
  • values to be included and then specific exclusions e.g. “1000-2000,~1500” will sum the values between 1000 and 2000 but will skip 1500.
  • a combination of any of the above e.g. “1000-2000,2371,2375,2560-2580,3000,~3200,4000,~4161-4169”. In this way you can build up your own calculation definition in the segment parameter.
  • a list of values is always processed as an OR condition e.g. “1500,1510,1590” will return all accounts where the the segment is 1500 OR 1510 OR 1590. Should you wish to use an AND condition you can use the applicable delimiter (“+” by default) e.g. “1500-1600+1550-1800” would only return the intersection of those two ranges i.e. from 1550-1600. The AND operator can be very useful when combined with DFF values and can be used to return very complex sets of criteria.
Ledger This parameter can only be modified to another ledger using the same chart of accounts and calendar as the current ledger selected in the dropdown list on the toolbar. The Ledger defaults to the item selected in the dropdown list on the toolbar, or to the value in the function when editing an editing Get Balance. Enable the “Include the Ledger Id in the function” checkbox to select a different ledger.

You can extend this parameter to report on multiple ledgers in a single Get Balance function. See Multiple Ledgers for the detail on this feature.
Include the Ledger Id Choose to include the Ledger Id in the formula inserted in to Excel. This will also enable the Ledger list of values on the Ledger parameter.
Option – Movement Show only the debit or credit amounts. Leave this field blank to show both.
Option – 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 =OCL_GLW_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.
Option – 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.