Skip to main content

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

Syntax

=GXL("Balance Type","Budget/EnType","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 Oracle ledgers:

Balance Type

Identifies the balance type. Valid types are:

  • A (Actual)
  • B (Budget)
  • E (Encumbrance)
  • P (Project)

Budget/En Type

Identifies the budget or encumbrance name. This value is required if Balance Type is either E or B.

Key 3

Optionally, identifies translation type, currency, ledger, account type, and translated flag criteria. Enter 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.

  • "TRANSLATED=X": where X is the translation type. Valid types are:
    • E (Entered in the selected currency)
    • T (Translated to the selected currency)
    • M (Multiple reporting currencies).

    Note:
    • E must be used if the selected currency is the database's functionality currency or STAT.
    • Prior to selecting T, ensure the currency translation has been processed in Oracle for the selected currency and reporting period.
  • "CURRENCY=XXX": where XXX is the name of the currency to be used.
  • "BOOK=XXX" where XXX is the name of the ledger, or ledger set to be used. If not specified, the system uses the default ledger identified in the Reporting Intelligence Ribbon (Tools > Select Responsibility).
  • "SUMMARY=XX": where XX indicates which account types to use. Valid values are:
    • NA (use the Summary Accounts option from Reporting Intelligence Settings > Ledger Specifics to determine whether to include Summary Accounts with Non-Summary Accounts)
    • Y (include only Summary Accounts)
    • N (include only Non-Summary Accounts)

    If not specified, NA is assumed.

  • "TRANSLATEDFLAG=X": where X is the name of the translated flag to be used. Enter * to retrieve all records, regardless of their assigned translated flag.

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.

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

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 Masks

=GXL("A",,"TRANSLATED="E";"&"CURRENCY="USD";"&"BOOK="VISION OPERATIONS (USA)";"&"TRANSLATEDFLAG="*";","2003","PER","6","01","*","1110","*","*")

Retrieves the actual activity for period 6 of 2003, for an account mask using a Vision Operations (USA) set of books.

Formula Example: Using a Range

=GXL("A",,"TRANSLATED="E";"&"CURRENCY="USD";"&"BOOK="VISION OPERATIONS (USA)";"&"TRANSLATEDFLAG="*";","2003","PER","6","01","*","1110.1999","*","*")

Retrieves the actual activity for period 6 of 2003, for accounts 1110 through 1999, using a Vision Operations (USA) set of books.

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

Recently Viewed