BPCS GXA

The GXA formula is a legacy formula used to retrieve account balances for a specific format or reporting period. The following is an overview for the GXA formula for BPCS ledgers:

Note: the GXA formula has been retained for backwards compatibility with prior Spreadsheet Server releases. The GXL formula has been created to replace the GXA formula (see BPCS GXL for more information. Use the Upgrade Workbook function to convert GXA formulas to GXL formulas (see Upgrade Workbook for more information).

Syntax: Version 4.05

=GXA("Account String","Datagroup","Format","Period","Key 3")

Syntax: Version 6+

=GXA("Account String","Ledger","Book","Year","Format","Period","Key 3")

Note: it is common practice to use cell references within GXA formulas to identify parameters.

Parameters

The following are GXD formula parameters for BPCS ledgers:

Account String

Represents the account segments delimited by a dash ( - ). The number of account segments vary based on the account structure for the selected ledger. May be a single value, mask, range, value list, or segment list (see Account Segment Syntax for more information.

Datagroup (Version 4.05)

Identifies the data type. Valid options are:

CURRENT
PRIOR
FUTURE
BUDGETA
BUDGETB
CBnn (where nn equals the budget number)

Ledger (Version 6+)

Identifies the CEA ledger.

Book (Version 6+)

Identifies the CEA book.

Year (Version 6+)

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.
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.
PERJ: the period activity for the selected month, including unposted JVs.
QTRJ: the period activity for periods included in the selected quarter number, including unposted JVs.
YTDJ: the activity for periods 1 thru the designated period number excluding or including the opening balance on the option in the Ledger Specifics panel.

Period

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

Key 3

Optionally, identifies statistical data, alternate library, 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 ( ; ). Enter 0 or leave blank if not used.

"STAT": indicates the retrieval of statistical balances.
"XXX": the name of the alternate library to be used. If left blank, Spreadsheet Server uses the library specified during sign in.
"BUDGET:NNNN:XX": where NNNN is the budget name and XX is the budget revision.

Formula Example: Using Single Values (Version 6+)

=GXA("50-000-31112","JRH","JRH-E ACT","2001","PER","4")

Retrieves the monetary balance for period 4 of 2001 for a single account string.

Formula Example: Using Statistics (Version 6+)

=GXA("50-*-*","JRH","JRH-E ACT","2001","PER","4","STAT")

Retrieves the statistics balances from the actual book within the JRH ledger, for period 4, for an account mask.

Formula Example: Using Alternate Library Name (Version 6+)

=GXA("50-000-31112","JRH","JRH-E ACT","2001","PER","4","BPCS")

Retrieves the monetary balance for period 4 of 2001, for a single account from the alternate library, BPCS.

Formula Example: Using Budget Manager (Version 6+)

=GXA("50-000-31112","JRH","JRH-E ACT","2004" "PER","4","Budget:Fiscal2005:003")

Retrieves the budget balance from budget Fiscal2005 revision 003, for period 4 of 2004, for a single account string.