Infinium 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 Infinium ledgers:

Syntax

=GXL("Balance Type","Budget/Actuals","Key3","Year","Format","Period","Account Segment 1","Account Segment 2","Account Segment 3","Account Segment 4","Account Segment 5"...)

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

Parameters

The following are GXL formula parameters for Infinium ledgers:

Balance Type

Balance types are:

M (Monetary balances)
S: (Statistical balances; CURRENCY must be blank)
D: (Debit balances)
C: (Credit balances)
1: (Numeric User Field 1)
2: (Numeric User Field 2)

Budget/Actuals

Identifies whether to retrieve actual or budget data. Valid values are: ACTUALS, or the budget name. The value in Key 2 is ignored if the Key 3 parameter contains references to Budget Manager.

Key 3

Optionally, identifies currency, alternate database library, append user list (AND or OR), and user fields, rather than account segments, and/or Budget Manager name or revision. Type 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. Must be blank if retrieving statistical balances. Enter * to retrieve all currencies.
"LIBRARY=XXX": where XXX is the name of the alternate library which overrides the default library in Application Configurator, or the library specified during the sign in process.
"ANDUSERLIST:NNNN": where NNNN is the user list name. The account must meet both the account string and user list criteria.
"ORUSERLIST:NNNN": where NNNN is the user list name. The account must meet either the account string or user list criteria.
"INFINIUMUSERFIELDS=X": where X is NA (account segments), Y (user fields), or N (account segments). If not specified, NA is assumed.
"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 or including the opening balance, based on the option in the Ledger Specifics panel.
LTD: the activity for periods 1 through the designated period number, including the opening balance.
RANGE: the activity for the range of specified periods.

Period

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

Account Segment 1-9

Enter one parameter for each segment of the account string (for example, Company, Segment 2-9). The number of account segments are based on the number specified in the user's Ledger Specifics Settings. 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("M","Actuals","CURRENCY=USD";"LIBRARY=","2001","PER","1","001","000","1070","001")

Retrieves the actual monetary activity for period 1 of 2001, for account string 001-000-1070-001.

Formula Example: Using User Fields

=GXL("M","Actuals","CURRENCY=USD";"LIBRARY=";"INFINIUMUSERFIELDS=Y","2001","PER","1","001","BEN","4000","DIST","PROD")

Retrieves the actual monetary activity for period 1 of 2001, for company 001, using user fields 1, 2, 3, and 4 criteria.

Formula Example: Using a User List

=GXL("M","Actuals","LIBRARY=";"CURRENCY=USD";"ANDUSERLIST=@Tax","2002","YTD","11","001","310","6*","*")

Retrieves actual monetary year-to-date activity through period 11, for accounts beginning with 6, which also meet the user field criteria in the TAX segment list.

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