Drill Criteria – Balance
A drill down can be performed by selecting a cell containing a Get Balance formula and clicking the drill down button on the toolbar. However, you may wish to bypass the requirement for a Get Balance formula and simply extract data based on parameters that you set. The Drill Down Criteria Sheet functionality allows you to very quickly extract the detailed general ledger balances and journals that meet certain criteria. This is useful when large numbers of individual GL balances are required as the values are returned faster than by using many individual “Get Balance” formula.
To create a drill criteria sheet navigate to GL Wand | Execute Drilldown | Drill Criteria. This will create a new drilldown workbook with a criteria form on the first sheet.
Enter the parameters for the data that you wish to extract on the criteria sheet of this new workbook. The criteria are exactly the same as the parameters of the Get Balance function and operate in the same manner.
You can enter values manually by typing them in to the field, or select them from a list of values. To use the list of values simply double click the cell where you want the parameter cell or select the applicable cell and click the list of values button on the GL Wand toolbar. The list of values form will open (see the Segment List of Values section for more details).
- Enter a period
- Choose the balance type. Please note that selecting “YTD” will extract all the journal lines for the year when executing a journal drill down and similarly “QTD” will extract the journals for the quarter.
- Enter the required currency code
- Pick “E” entered or “T” translated balances (Or Total if using Oracle R12)
- Pick “A” actual, “B” budgets or “E” encumbrance for the primary balance type. If you select “B” or “E” then you must populate the Bud/Enc Name parameter with the budget version name or encumbrance type. Please note this must be spelt correctly.
- Populate the segment values.
- Change the Ledger if required.
- Enter a journal source, category or movement type to limit the results to only those criteria.
Double click Execute Drilldown, or use the balance drill down button on the GL Wand toolbar to execute the drill down.
This workbook operates in exactly the same manner as a normal drill down. See the Execute Drilldown section for more details.
Secondary Balance
Optionally populate the secondary balance parameters. They work in the same manner as the primary balance parameters.
To view the secondary balances, they will need to be added to a custom drilldown template. The columns available are:
- Opening Balance Secondary
- Debit Movement Secondary
- Credit Movement Secondary
- Net Movement Secondary
- Closing Balance Secondary
To create a custom drilldown template see Edit Template Layout.