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, “T” translated balances or Total
- Pick “A” actual, “B” budget or “E” for Encumbrance. If you select “B” or “E” then you mist populate the Bud / Enc Name parameter with the budget version or encumbrance type. Please note this must be spelt correctly. Also note, Budgets are currently not supported.
- Populate the segment values.
- Change the Ledger if required.
- Enter a journal source, category or movement type (debit or credit) to limit the results if necessary.
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.
To create a custom drilldown template see Edit Template Layout.