Skip to main content

Formula Builder

Formula Builder is a tool used to aid in quickly building templates, and creating or updating formulas in a Spreadsheet Server spreadsheet. Formulas are based on the user's licensed features and selected ledger. This tool populates the necessary columns and rows with required parameters and account segment values as provided by the user. Insert a new a GXL or GXE formula template using the parameters and account segment values. The default value for Row/Col buttons is based on the option in the user's Spreadsheet Server Settings.

Note: See Formula Builder for Queries for query-related formulas.

  1. In Excel, from the Spreadsheet Server Ribbon, click the Formula Builder button. The Formula Builder panel appears, opening to the last used formula.

  2. Select the desired formula. The selected formula panel appears.
  3. Some fields in the 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 data in the Formula Builder > GXL panel. Links are available at the bottom of the table to access ledger-specific Formula Builder > GXL information.
    4. Field Description

    GXL:

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

    Worksheet

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

    Starting Cell

    Specify the starting cell of the template.

    Clear/Shift Worksheet

    Specify when inserting data whether to:

    • Overwrite existing cell data in the worksheet.
    • Clear all cell data in the target worksheet.
    • Insert the GXL formula in the target cell with cell references to parameters.
    • Insert the GXL formula in the target cell, then to insert the ledger and segment-specific data to newly inserted columns and rows to the top and to the left of the existing worksheet data.

      • See Build a GXL Template Using Clear Worksheet and Build a GXL Template Using Shift Existing Data for more information.

    Invert Sign

    If selected, the displayed account balance sign is reversed.

    Ledger

    Click the drop-down list, and select the ledger, or ledger set for which to retrieve data.  After selecting a value, the system changes the parameters in the panel to match the selected definition.

    Ledger Specific Processing Options

    Header and footer values based on the requirements of the selected ledger (for example, OracleFSG Templates and Convert Missing Values to Parent Hierarchies.

    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

    Click the drop-down list, and select 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.

    Insert Column Data Only

    This option is enabled after data has been inserted using the Insert or Shift buttons in the toolbar. If selected, an additional column of parameters is inserted (see Build a GXL Template Using Insert Column Data Only for more information).

    Account Segments:

    If necessary, use the scroll bar to access additional account segments.

    Use/Segment

    If selected, the account segment is included in the formula.

    Row/Col

    Click this button to toggle between Row and Col. This determines if the account segment will be located in the template parameters column or, by default, in the template account segment row. The default value of the Row/Col buttons is based on an option in the user's Spreadsheet Server Settings.

    Display

    Specify the text to be used as the account segment title.

    Value

    Specify the default value in the template for the account segment, or click the ellipses button ( ... ) to select from a list of valid segment values. The value may be a single value, mask, segment list, hierarchy value, @ field, or range of values (see Account Segment Syntax) for more information.

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

  4. Use the following table to enter data in the Formula Builder > GXE panel:

    Note: Optionally, use Formula Part 2 to add a second GXE calculation in a single GXE formula, allowing for both account and journal details to be expanded from one GXE formula.

    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

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

    Starting Cell

    Specify the starting cell for the template.

    Clear/Shift Worksheet

    Specify when inserting data, whether to insert the GXE formula in the target cell with or without cell references for its parameters.

    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 in the source worksheet that contains the summary formulas. If using Formula Part 2 to add a second GXE calculation, the system uses the same value assigned to Formula Part 1.

    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. After the appropriate data has been entered and verified, click one of the following buttons:
    • Insert: inserts parameter labels, values, and the formula in the selected worksheet, then closes the panel.
    • Insert and GXE: inserts parameter labels, values, and the GXL formula in the selected worksheet, then opens the GXE panel.
    • Insert and Shift: generates and inserts the GXL template, but the panel remains open, allowing multiple columns to be inserted.
    • Cancel/Close: closes the panel.

Was this article helpful?

We're sorry to hear that.

Powered by Zendesk