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.

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.

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

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.

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.