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

Syntax

=GXL("Balance Type","Business Unit","Key 3","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 PeopleSoft ledgers:

Balance Type

Identifies the balance type. Valid types are:

A (Actual)
B (Budget)
E (Encumbrance)

Business Unit

Identifies the business unit.

Key 3

Optionally, identifies currency. Enter the necessary literal and a value as defined below:

0 or leave blank if not used.
"CURRENCY=XXX": where XXX is the name of the currency to be used.

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.
OBAL or OPE: the opening balance (for example, period 0).

Note: Period values are ignored.

CBAL or CLO: the year-to-date activity, including closing balances and/or adjusting entries (for example, period 0-54).

Note: Period values are ignored.

BP: the budget amount for the selected period.

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-10

Enter one parameter for each segment in the account string (for example, Ledger, Fund Code, Department, Class Field, Program Code, Chart Field 1, Account, Project, Affiliate, and Statistics Code). May be a single value, mask, range, value list, segment list, hierarchy value, or @ field (see Account Segment Syntax for more information).

Formula Example: Using Masks

=GXL("A","US005","CURRENCY=USD","2003","PER","6","LOCAL","*","20000","*","*","*","400000","*","*","*")

Retrieves the actual activity for period 6 of 2003, for business unit US005, ledger LOCAL, department 21400, and all accounts starting with 4.

Formula Example: Using a Range

=GXL("A","US005","CURRENCY=USD","2003","PER","6","LOCAL","*","21000.21999","*","*","*","400000","*","*","*")

Retrieves the actual activity for period 6 of 2003 for business unit US005, ledger LOCAL, departments in the range of 21000 through 21999, and account 400000.

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