Overview
=GL (What, Account, Start Date, End Date,..., Data Source)
Purpose: Returns the budget, balance, net change, debits, or credits of the Dynamics NAV or Business Central G/L Account of a given company, based on filters.
Interactive Tutorial
This interactive tutorial provides a step-by-step introduction to the Jet GL function for Dynamics NAV.
GL Function Reference (for NAV and BC)
| Dynamics NAV/BC Parameter |
Description |
| What |
Determines what the GL function returns. Options are:
|
| Account |
Specifies filters for the G/L Account Number. If you specify a single, totaling account, you will get totals. If you specify multiple accounts or a range of accounts, totaling accounts will not be included in the returned number even if the other account(s) have nothing to do with the specified totaling account(s). |
| StartDate |
Specifies the starting date of transactions to include. If you are interested in the balance of an account on a given date, leave StartDate blank. If you are interested in the net change of an account, use Balance and specify both the StartDate and EndDate. |
| EndDate |
Specifies the ending date of transactions to include. Specifying a start period and an end period will give you the net change between the first day of the start period and the last day of the end period. Specifying a start period with no end period will give you the net change between that start date and the present. Specifying no start period will give you the balance/budget as of the end period. Specifying no start period or end period will give you the present balance/budget. |
| View |
The G/L Analysis View to use. Leave this blank to use balances from the G/L directly. |
| Dim1 |
Filter for the first dimension of the analysis view. If View is blank, this is the filter for Global Dimension 1. Dimension totaling is handled the same way as Account totaling. |
| Dim2 |
Filter for the second dimension of the analysis view. If View is blank, this is the filter for Global Dimension 2. |
| Dim3 |
Filter for the third dimension of the analysis view. |
| Dim4 |
Filter for the fourth dimension of the analysis view. |
| Business Unit |
Filter for the business unit |
| Budget |
Specifies the filter for the Budget. This is unused unless returning budgets (in the "What" parameter) |
| Company |
Company Name. This must be spelled the same as it appears in NAV/BC, including case, spaces and punctuation. If this parameter is empty (""), the default company in the Jet Reports Options/Data Sources Screen is used. |
| Reserved | |
| Exclude Closed |
“True” to exclude closing date transactions. Defaults to “False”. |
| Reserved | |
| Reserved | |
| Reserved | |
| Cache Tables |
When the value of the Filter argument is “True”, GL data from the source table for this function will be retrieved and cached before expanding any of the report's replicators. This can improve performance for some reports. |
| ShowQuery |
"True" to show the FinHlink string that will be used for drilldown. Defaults to "False". |
| Reserved | |
| Data Source |
Data source name. If this parameter is blank, the default data source is used |
Examples of the GL function
To retrieve the balance of G/L account 44100, you would use the following:.
=GL("Balance","44100")
This GL returns the net change of account 44100 between 1/1/2002 and 1/31/2002...
=GL("Balance","44100","1/1/02","1/31/02")
For GL balances with standard NAV/BC:
- you can filter on the two Global dimensions, or...
- if using NAV/BC Analysis Views, you can filter on up to 4 dimensions that are tied to that view
For the balance of account 40100 with Global Dimension 1 of USA and Global Dimension 2 of COPPER, you can use the following:
=GL("Balance","40100",,,,"USA","COPPER")
Comments