Advertisements

Using GL Function in Jet Reports

Syntax: =GL (What,Arg1,Arg2,Arg3,..,Arg22)

Purpose: Returns the budget, balance, net change, quantity, debits or credits of the G/L Account of a given company based on filters.

Dynamics NAV Parameter Description
 What NAV: Determines what the GL Function returns. Options are Balance, Budget, Quantity, Credits or Debits Note that the options available for the What argument depend on the Where argument.
 Account NAV: G/L Account Number, Filter or Range. 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). If the Where argument is “Rows”, “Columns”, or “Sheets”, then the What options are “Accounts” which will give a list of account numbers, “Categories” which will give a list of account category numbers, or “SegX” where X is a segment number and which gives a list of that specific account segment.
StartDate NAV: 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 NAV: 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 NAV: The G/L Analysis View to use. Leave this blank to use balances from the G/L directly. Analysis Views are available in Navision version 3 and later. This field should be blank if you are using objects from an earlier version of Navision.
 Dim1 NAV: 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. In Navision versions before 3.0, Dim1 is used as the Department filter.
 Dim2 NAV: Filter for the second dimension of the analysis view. If View is blank, this is the filter for Global Dimension 2. In versions before 3, this is the Project filter.
 Dim3 NAV: Filter for the third dimension of the analysis view.
 Dim4 NAV: Filter for the fourth dimension of the analysis view.
 BusinessUnit NAV: Filter for the business unit.
 Budget NAV: Budget filter. This is unused unless returning budgets.
 Company NAV: Company Name. This must be spelled the same as it appears in Navision, including case, spaces and punctuation. If this parameter is empty (“”), the default company in the Jet Reports Options/Data Sources Screen is used.
 Reserved NAV: Blank. For backwards compatibility, a Data Source Name as defined in Jet/Options can be used.
 Reserved GP: Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.
 Reserved GP: Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.
 Reserved   GP: Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.
 Reserved GP: Specifies the budget filter, blank for all budgets. Note that budgets are associated with a specific year in Great Plains so if your budget and fiscal year filters do not coincide you will get a 0 value.
 ExcludeClose NAV: “True” to exclude closing date transactions. Defaults to “False”.
 ShowQuery NAV: “True” to show the finhlink string that will be used for drilldown. Defaults to “False”.
 Reserved GP: Company name. If this parameter is blank, the default company is used.
 Data Source Data source name. If this parameter is blank, the default data source is used.

Reports based on the G/L are easy with the GL function.

=GL(What, Account, StartDate, EndDate, View, Dim1, Dim2, Dim3, Dim4, BusinessUnit, Company, Reserved, ExcludeClose, Reserved, Reserved, Reserved, Reserved, Reserved, Reserved, ShowQuery, Reserved, DataSource)

NAV Cronus 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=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. 
  • For the balance of account “40100” with Global Dimension 1 of “USA” and Global Dim 2 of “COPPER”, you can use the following function.

Stay tuned for how to use GL Function in Jet Reports.

I will come up with more details on this in my upcoming posts.

Advertisements

Important

Most of the contents you find in this blog will be either inherited from MSDN or Navision Developer IT Pro Help. Some places images are also directly taken from these sites. Purpose is simple to try those stuffs and re-produce adding few things as per my understanding to make easy understanding for others and quick reference.

Here nothing under my own brand or authorship of the content. At any point of time we are just promoting Microsoft stuffs nothing personnel with same.

Hope stuffs used here will not violate any copyright agreement with them. In case by mistake or in-intestinally it happens and the Microsoft feels these should not be used Microsoft have full right to inform me about same and will be glad to take down any such content which may be violating the norms.

Purpose is to promote Navision and share with community.

FB Profile

Like FB Page

%d bloggers like this: