Jet Reports Homepage |  Community Forum |  Downloads |  Submit A Ticket |  Jet Express Support
Feedback

Jet Express - GL Function for GP


=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.

Help Topics

NL Function
GL Function

 

Examples

To retrieve the balance of G/L account 000-1100-00, you would type the following:

=GL(,"Balance","000-1100-00")

 

If you wanted to know the net change of account 000-1100-00 for the 1st quarter of 2002, you could type the following:

=GL(,"Balance","000-1100-00","2002/1","2002/3")

 

With GP, you can filter on the account number as a whole or you can filter on the individual segments of the account number. So you could also create the last GL function to find the 2002 1st quarter net change for account 000-1100-00 like the following:

=GL(,"Balance",,"2002/1","2002/3",,"000","1100","00")


Note that while some numbers such as 2002 and the start period and end period do not need quotes, "000" and "00" must be quoted so that Excel does not interpret them as numbers and change them to single zeros.

__________________________________________________________________

 

GL Function
Introduction
GL Function
Next Steps
 
 

__________________________________________________________________

 

GL Function Reference (for GP)

Dynamics GP Parameter

Description

Where

Determines where the GL function returns its data. Options are "Rows", "Columns", "Sheets", and "Cell".

  • "Rows" will insert rows, 
  • "Columns" will insert columns, 
  • "Sheets" will insert sheets, and 
  • "Cell" will put a single value in the current cell

Additionally, you can use "Rows=x" and "Columns=x" to insert multiple rows or columns for each result, which works just like the NL function. If you leave this argument blank, it defaults to "Cell".

Note that the options available for the What argument depend on the Where argument. See the next argument for more details.

 What  

Determines what the GL function returns. If the Where argument is "Cell", then the What options are:

  • "Balance" which returns a G/L balance, 
  • "Budget" which returns a G/L budget, 
  • "AccountName" which returns the name of a G/L account, or 
  • "CategoryName" which returns the name category name for a specified category.

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.
Account

Traditional segmented account number filter.

Start Period

Specifies the start period or date in one of several formats:

  • YYYY/PPP where YYYY is the 4 digit fiscal year and PPP is the 1 to 3 digit period number
    (note: this is the fastest and, thus, preferred format)
  • YYYY/M/D where YYYY is a 4 digit year, M is a month, and D is a day; 
  • DDDDD.DD which is a 5+ digit number interpreted as an Excel serial date, and 
  • M/D/YY or D/M/YY (depending on your computer's locale settings) where M is a month, D is a day, and Y is a 2 or 4 digit year.

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.

You cannot use a range or other special filter here. For information on Period 0, see Fiscal Year Beginning Balances in the GP GL function.

End Period

Specifies the end period in the same format as start period. You cannot use a range or other special filter here.

Category Number

Specifies filters for the category number(s).

Seg1Filter

Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

Seg2Filter

Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

Seg3Filter

Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

Seg4Filter

Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

Seg5Filter

Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

Seg6Filter

Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

Seg7Filter

Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

Seg8Filter

Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

Seg9Filter

Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

Seg10Filter

Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

Budget

Specifies the budget filter, blank for all budgets.

Note that budgets are associated with a specific year in Dynamics GP, so if your budget and fiscal year filters do not coincide you will get a 0 value.

Include Unposted

"True" to include unposted transactions in Balance queries.  Default is "False"

ShowQuery

"True" to show the finhlink string that will be used for drilldown. Defaults to "False".

Company

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.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments