BPCS GXL

As fields in the Formula Builder > GXL panel vary based on the selected ledger, use the following table to enter unique data unique to BPCS ledgers:

Syntax: Version 4.05

=GXL("Datagroup","Format","Period","Account Segment 1","Account Segment 2","Account Segment 3")

Syntax: Version 6+

=GXL("Ledger","Book","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 BPCS ledgers:

Datagroup(Version 4.05)

Identifies the data type. Valid types 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.

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 variables with a semi-colon ( ; ). Enter 0 or leave blank if not used.

"STAT=Y": indicates the retrieval of statistical balances.
"LIBRARY=XXX": where XXX is 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.

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

Account Segments 1-14

Enter one parameter for each segment of the account string. The number of account segments vary based on the account structure for the selected ledger. 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 Single Values (Version 6+)

=GXL("JRH","JRH-E ACT","0","2001","PER","4","50","000","15130")

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

Formula Example: Using a Mask and a Range (Version 6+)

=GXL("JRH","JRH-E ACT","0","2001","PER","4","50","*","21454.22999")

Retrieves the monetary activity for period 4 of 2001, for all departments, for the account range of 21454 through 22999.

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