=GL (What, Arg1, Arg2, Arg3,..., Arg22)
Purpose: Returns the budget, balance, net change, debits or credits of the G/L Account of a given company based on filters.
Interactive Tutorial
This interactive tutorial will walk you through how to use the Jet GL() Function for Dynamics NAV
(be sure to press the Play button in the lower-left after the tutorial is displayed)
Examples
To retrieve the balance of G/L account 44100, you would type the following:
=GL("Balance","44100")
If you wanted to know the net change of account 44100 between 1/1/2002 and 1/31/2002, you would type the following:
=GL("Balance","44100","1/1/02","1/31/02")
For G/L Balances with standard NAV:
- you can filter on the two Global Dimensions for G/L Balances or
- if using a NAV Analysis View, 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 Dim 2 of "COPPER", you can use the following function.
=GL("Balance","40100",,,,"USA","COPPER")
Please note that some NAV verticals that allow more than 2 Global Dimensions may not be compatible with the GL function.
______________________________________________________________
GL Function Reference (for NAV)
| Dynamics NAV 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. |
| 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, 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 |
Comments