Jack Henry GXL

Other ERPs - this article only applies to Reporting Intelligence for other ERPs

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

Syntax: Silverlake

=GXL("Data Type","Time Span","Key 3","Year","Format","Period","Account Segment 1","Account Segment 2","Account Segment 3","Account Segment 4","Account Segment 5")

Syntax: CIF 20/20

=GXL("Data Type","Time Span","Key 3","Year","Format","Period","Account Segment 1","Account Segment 2")

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

Parameters

The following are GXL formula parameters for Jack Henry ledgers:

Data Type

Identifies the type of data to retrieve. Valid data types are:

Data Type	Description	Silverlake	CIF 20/20
`ACTUAL`	The actual period to date data.	*	*
`AGGREGATE`	The sum of daily balances.	*	*
`AVERAGE`	The aggregate total divided by number of days. Note: This data type is only applicable when `Format` = `PER`.	*
`BUDGETA`	The budget balances.	*	*
`BUDGETB`	The budget balances.	*	*
`CBAL`	The current daily balance.		*
`CURBAL`	The current daily balance.	*
`YESBAL`	Yesterday's balance.	*	*

Time Span

Identifies the time span for data to retrieve. Valid time spans are:

Time Span	Description	Silverlake	CIF 20/20
`CURRENT`	The current year.	*	*
`PRIOR1`	The prior year.	*	*
`PRIOR2`	Two years prior.	*
`FUTURE`	The future Year	*

Key 3

Identifies the level and the optional alternate library to be used. 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 ( ; ).

"LEVEL=X": where X is the account posting level. 0 indicates detail level account, 1-8 indicates roll up level account, and blank includes all account levels.
"LIBRARY=NNNN": where NNNN is the name of the alternate library.

Year

Year identifier. For informational purposes only (not used in the actual calculation). May enter 0.

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

Enter one parameter for each segment of the account string. The number of account segments vary based on the account structure of the selected ledger (for example, Silverlake = Bank, Branch, Account, Cost Center, or Product Code; CIF 20/20 = Branch or Account). 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 (Silverlake)

=GXL("ACTUAL","CURRENT","LEVEL=0","0","PER","8","1","1","2102100","0","0")

Retrieves the actual balance for period 8 of the current year, for a single account string.

Formula Example: Using Single Values (CIF 20/20)

=GXL("ACTUAL","CURRENT","LEVEL=0","0","PER","8","1","11200")