Skip to main content

Expand to Journals

Note: The GXE expand to journals option is not applicable to Analyst ledgers.

  1. Create a standard Spreadsheet Server report for the GXL formula using Formula Builder (see Build a Template with Formula Builder for more information). The single balance becomes the basis of the journal entry detail. When the process is executed, the single column balance is expanded to detail journal lines and placed in the target worksheet.

    2. The following example shows a GXL formula totaling on an account mask. In the example, the account string parameters are shown in cells B10:B14. The balance parameters of the GXL (for example, type, budget name, year, format, period, translated, currency, etc.) are entered in cells B1:B7. The actual GXL formula is contained in cell B17.

  2. Using Formula Builder (see Build a Template with Formula Builder for more information), for the GXE formula, define the starting cell and parameters of the formula.
    3. Field Description Example

    Worksheet

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

    GXE Source

    Starting Cell

    Specify the starting cell of the template.

    A19

    Source Worksheet

    Specify the name of the worksheet containing the summary formulas that will be expanded.

    GXE Source

    Source Detail Row

    Specify the row number in the source worksheet containing the summary formulas.

    17

    Target Worksheet

    Specify the name of the worksheet containing the final formatted worksheet, and will receive the expanded detail.

    GXE Target

    Target Type

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

    ROW

    Range/Table/Row

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

    1

    Headings

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

    Y

    Autofit

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

    Y

    Clear Sheet

    Select whether to clear the target worksheet before expanding details. If not specified, N (no) is assumed.

    Y

    Journals Only Column

    Specify the column containing the GXL balance for which to generate journal detail.

    Note: If a GXE is configured to go to the journal level but the incorrect column is selected, results are still obtained as long as there is only one GXL in the row.

    B

    Journals Ad Hoc Query

    If desired, identify the ad hoc query to be used to generate journal detail.

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

     

    Summarize Balance Level

    For JD Edwards ledgers, 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. Otherwise, this field is not applicable when using the GXE to expand journal detail.

    (leave blank)

    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.

    Note:
    • This function is not applicable to 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, if the Summarize Balance Level is an AcctCatCode or BUCatCode @ field alias or field name, an extra account segment representing the category code is added to the front of the account string. This will need to be taken into consideration when specifying the account segment number for which to generate subtotals.

    4

    Journals to Table

    Select whether to place expanded journal entry details into a table. If this option is used, 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 journals ad hoc query is used. This option is ignored if the Range/Table/Row value is a RANGE or TABLE, or if Total Journal Level is not specified. If this option is not specified, N (no) is assumed.

    Note: If this option is set to Y and there are multiple GXEs to journals in the workbook, set the Clear Sheet option to Y to avoid errors.

    N
  3. After the GXE data has been entered and verified, click the Insert button. The parameters and GXE formula are inserted in the selected worksheet.

    4. Formula Example:
    =GXE(B20,B19,B22,B21,B23,B24,B25,B26,B27,B28,B29,B30)

  4. To generate detail reports for all GXE formulas in the workbook, from the Spreadsheet Server Ribbon, select Execute Reports > All Detail Reports (GXE Formulas). The results of all GXE formulas are expanded to the appropriate formatted sheets.

    5. To generate detail reports for selected GXE formulas, select the cells containing the GXE formulas, then, from the Spreadsheet Server Ribbon, select Execute Reports > Selected Detail Reports (GXE Formulas), or, right-click and select Spreadsheet Server > Execute Selected Detail Reports (GXE). The results of the selected GXE formulas are expanded to the appropriate formatted sheet.

Was this article helpful?

We're sorry to hear that.

Powered by Zendesk