Skip to main content

Create or Maintain GL Formulas

Quickly create or modify formulas in a Spreadsheet Server spreadsheet with Formula Builder. Formulas available in Formula Builder are based on the user's licensed features and the selected ledger.

Note: See JD Edwards Formula Maintenance for GXSALES for JD Edwards-specific GXSALES formulas. See Create and Maintain Query Formulas for query-related formulas.

  1. In Excel, click the cell where the formula is to be inserted, or the containing the formula to be updated, and, from the Spreadsheet Server Ribbon, click the Formula Builder button. The Formula Builder panel appears.
  2. If updating an existing formula, and the active cell contains a Spreadsheet Server formula, Formula Builder launches in the corresponding formula panel. To create a new GXC or GXD formula, select the appropriate option.

    3. Note: For efficient processing, use cell references to identify individual formula parameters. Where applicable, literal values are displayed and can be typed.

    Cell References and Literal Values for Existing Formulas

    No Cell References

    • If there are no cell references in the formula, the cell reference fields appear blank. The formula uses only the hardcoded (literal) values.
    • If updating literal values but not cell references, only the literal values in the formula are updated.

    With Cell References

    • If there are cell references in the formula, the formula shows both the value and the reference to the cell containing said value, in the cell reference boxes.
    • If updating the cell references, the cell containing the value changes to the new reference, and the value is inserted in said cell only, if the cell is blank.
    • If selecting a cell with a pre-existing value, the value in the formula is updated.

    Note: If necessary, press F4 to lock either the row and/or column value of the cell reference.

  3. Some fields in Formula Builder > GXL panel vary based on the selected ledger; basic processing is the same for all ledgers, however. Use the following table as an overview for entering or modifying data in the Formula Builder > GXL panel. Links are available at the bottom of the table to access ledger-specific GXL information. Click here to watch a video that walks you through this feature.
    4. Field Description

    GXL:

    Returns a single balance from the ledger (see GXL Formula for Account Values for more information).

    Note: This section assumes that the parameters will be occur top-down in a single column. If entering a cell reference in the first field, then moving to the next field, the system will populate the other fields sequentially through the Period field.

    Invert Sign

    If selected, the displayed account balance sign is reversed.

    Key 1

    Header and footer values based on the requirements of the selected ledger (for example, OracleKey 1 = Balance Type).

    Key 2

    Header and footer values based on the requirements of the selected ledger (for example, OracleKey 2 = Budget/En Type).

    Year

    Specify the reporting year.

    Format

    Click the drop-down list, and select the time range for which to retrieve data. Basic valid formats include the following, however, some ledgers may support additional formats:

    • PER
    • QTR
    • DQTR
    • YTD
    • LTD
    • RANGE

    Period

    Click the drop-down list, and select the corresponding period, quarter number, or range of periods (for example, 1.5 for periods 1 through 5) for the specified format.

    Key 3 Data

    Header and field values based on the requirements of the selected ledger (for example, OracleKey 3 Data = Translated, Currency, Ledger (Book), and Summary Account

    Account Segments:

    Specify the cells containing the required account segments. It is assumed the account segments will be in a row next to each other, so entering a value and clicking the down arrow will populate the rest of the fields (for example, if the first account segment cell reference is A13, clicking the down arrow for the parameter populates the following fields: B13, C13, etc.).

    Cell

    Specifies the cell reference for the account segment.

    Value

    Specifies the literal value of the cell referenced for the account segment

    Use the following links to access ledger-specific GXL information:

  4. Use the following table to enter or modify data in the Formula Builder > GXE panel:
    5. Field Description

    GXE:

    Inserts detail account balances or journal details in the specified worksheet (see Expand to Account Balances and Expand to Journals for more information).

    Worksheet

    Displays the worksheet where the formula resides.

    Starting Cell

    Specify the starting cell for the template.

    Source Worksheet

    Click the drop-down list, and select the name of the worksheet that contains the summary formulas to be expanded.

    Source Detail Row

    Specify the row number on the source worksheet that contains the summary formulas.

    Target Worksheet

    Type the name of the worksheet, or click the drop-down list, and select the name of the new or existing worksheet that contains the final formatted worksheet and will receive the expanded detail.

    Target Type

    Specify whether the expanded detail should be inserted into a table, range, or row on the target worksheet.

    Range/Table/Row

    Specify the target table, range name, or first row in the target worksheet to begin inserting the expanded detail.

    Headings

    Select whether to include column headings when expanding details. If not specified, N (no) is assumed.

    Autofit

    Select whether to autofit columns when expanding details. If not specified, N (no) is assumed.

    Clear Sheet

    Click the drop-down list, and select whether to clear the target worksheet before expanding details. If not specified, N (no) is assumed.

    Note: This value must be N if a TABLE or RANGE is specified for the Range/Table/Row option.

    Journals Only Column

    Specify the column for which to generate journal detail (see Expand to Journals for more information).

    Note: This option is not applicable to Analyst ledgers.

    Filter Adhoc by GL

    Limits the queries listed in the Journals Ad Hoc Query drop-down list to the specified ledger.

    Note: This option is not applicable to Analyst ledgers.

    Journals Ad Hoc Query

    If desired, click the drop-down list, and select the ad hoc query to be used to generate journal detail.

    Note: This option is not applicable to Analyst ledgers. An ad hoc query is required to expand journal detail for Data Warehouse and Designer GL ledgers.

    Summarize Balance Level

    If desired, specify the account segment number (break level) to be used for summarizing data when expanding account balances (for example, Account String = Company, Department, Account, and Sub-Account). To summarize balances at the account level enter 3 (third account segment).

    Note: Any ACCTSEGnn literals entered in the Source Detail Row will override this value.

    For JD Edwards ledgers only, if desired, specify an @ field alias or field name (as defined in Application Configurator) for an AcctCatCode or BUCatCode to control how to group expanded data. This replicates the legacy SSJD EdwardsGXECC formula.

    Total Journal Level

    If desired, specify the account segment number for which to generate subtotals when expanding journal entry details. Subtotals are generated at the selected level and each previous level (for example, Account String = Company, Department, Account, and Sub-Account. Enter 3 (third account segment) to generate subtotals at the account, department, company, and grand total level.

    Note:

    • This option is not applicable to Analyst, Data Warehouse, or Designer GL ledgers. For SAP ledgers, the maximum allowable segment number for totaling is 4. If a segment level greater than the number of segments allowed by the ledger is entered, the system will use the maximum allowable number of segments.
    • For JD Edwards ledgers only, when the Summarize Balance Level is an AcctCatCode or BUCatCode, @ field alias, or field name, an additional account segment representing the category code is added to the beginning of the account string. This will need to be taken into consideration when specifying the account segment number for which to generate subtotals.

    Journals to Table

    Select whether to place expanded journal entry details into a table. When this option is enabled, the system creates a new table in the specified target worksheet and assigns the next available standard Excel table name. The table includes headers and grand totals, except when a journal's ad hoc query is used. This option is ignored if the specified target is a range or table, or if Total Journal Level is specified. If not specified, N (no) is assumed.

    Note:

    • This option is not applicable to Analyst ledgers.
    • If this option is set to Y and the workbook contains multiple GXE journals, set the Clear Sheet option to Y to avoid errors.

  5. Use the following table to enter data in the Formula Builder > GXC panel:

    6. Note: This panel is hidden if ad hoc processing is disabled from the setting in Application Configurator.

    Field Description

    GXC:

    Runs an ad hoc query and returns a single result. This is used to specify the values of a customized formula. Contact insightsoftware for configuration of the new formula.

    Worksheet

    Click the drop-down list, and select the name of the worksheet in which to insert the formula.

    Starting Cell

    Specify the starting cell of the formula.

    Adhoc Query

    Click the drop-down list, then select the custom ad hoc query to be used for the customized formula.

    Parm 01-10

    Specify the appropriate parameters for the customized formula. Field values vary based on the requirements of the customized formula.

  6. Fields in the Formula Builder > GXD panel vary based on the selected ledger; basic processing is the same for all ledgers, however. Use the following table as an overview for entering data in the Formula Builder > GXD panel. Links are available at the bottom of the table to access ledger-specific GXD information.
    7. Field Description

    GXD:

    Displays the description for an account segment or account string.

    Worksheet

    Click the drop-down list, and select the name of the worksheet in which to insert the formula.

    Starting Cell

    Specify the starting cell of the formula.

    Parms

    Header and footer values based on the requirements of the selected ledger (for example, OracleParms = Segment Number, Value, and Ledger).

    Account Segments:

    May or may not be applicable to the GXD formula.

    Use the following links to access ledger-specific GXD information:

  7. The Formula results box near the bottom-left of the panel displays the formula as it is being built. Click the down arrow to expand and view the formula. Verify the data in the Results Box is correct, then click one of the following buttons:
    • Update: inserts the formula to the previously-selected cell in the current worksheet, and closes the panel.
    • Cancel: closes the panel.

    To copy the formula to the Windows clipboard, right-click the Formula box, and select Copy.

Was this article helpful?

We're sorry to hear that.

Powered by Zendesk