JD Edwards GXL

Use the GXL formula to retrieve account balances for a specific format/reporting period. The following is an overview for the GXL formula for JD Edwards ledgers:

Syntax

=GXL("Configuration","Ledger Type","Key3","Year","Format","Period","Account Segment 1","Account Segment 2","Account Segment 3","Account Segment 4","Account Segment 5","Account Segment 6")

Note: It is common practice to use cell references within GXL formulas to identify parameters.

Parameters

The following are GXL formula parameters for JD Edwards ledgers:

Configuration

Identifies the host configuration from which to retrieve account balances. May specify either the full configuration name or just the number.

Ledger Type

Identifies the ledger type code containing the account balances. May be a single value, mask, or value list (no excludes allowed); however, a mask or value list is not supported when processing in local mode.

Key 3

Identifies currency and balance type, and optionally identifies the business unit status and/or Budget Manager name or revision. Enter the necessary literal and a value as defined below. If more than one variable needs to be defined, separate the variables with a semi-colon ( ; ).

• "CURRENCY=XXX": where XXX is the name of the currency to be used. If not specified, the system uses the base currency from the user's Spreadsheet Server Settings. Enter * to retrieve all currencies.

Note: JD Edwards currency amounts are stored in one of two fields: GBCRCX or GBCRCD. If the formula does not return values, insert an X before the currency. For example, =GXL("1","AA","CURRENCY=XUSD";"WEEKLY=FALSE","2012","PER","1","00001","1","1110","FIB").

• "WEEKLY=NNNN": where NNNN is either TRUE or FALSE.
• "BUSTATUS=NNNN" where NNNN is either BLANK, NOTBLANK, K, N, or P.
• "BUDGETNAME=NNNN" and "BUDGETREVISION=XXX": where NNNN is the budget name, and XXX is the budget revision.

Year

Year identifier.

Format

Format options are:

• PER: the activity of the selected period.
• QTR: the activity for the periods provided in the selected quarter number.
• DQTR: the activity for the periods provided in the selected daily quarter number.
• YTD: the activity for periods 1 through the designated period number, excluding the opening balance.
• LTD: the activity for periods 1 through the designated period number, including the opening balance.
• RANGE: the activity for the range of specified periods.
• BLTD: the activity for the period through the designated period number, including the original budget amount (Budget Ledger Type).
• BLTDAPR: the activity for the period through the designated period number, including the approved budget amount (Budget Ledger Type).
• BLTDREQ: the activity for the period through the designated period number, including the requested budget amount (Budget Ledger Type).

Period

Corresponds to period, quarter number, or range of periods (for example, 1.5 for periods 1 through 5) for the specified format.

Account Segments 1-6

Enter one parameter for each segment of the account string (for example, Company, Business Unit, Object, Subsidiary, Subledger Type, or Subledger). The company, business unit, and object are required elements. May be a single value, BLANK value, NOTBLANK value, mask, range, value list, segment list, hierarchy value, or @ field (see Account Segment Syntax for more information).

Formula Example: Using Single Values

=GXL("1","AA","CURRENCY=***";"WEEKLY=FALSE","1998","PER","6","00001","1","1110","FIB","*","*")

Retrieves the activity for period 9 of 1998, using ledger type AA, for account string 00001-1-1110-FIB, for all subledgers.

See Control Panel, GL Reporting, Create or Maintain GL Formulas, and GXL Formula for Account Values for more information.