Expand to Account Balances

To expand to account balances:

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 report line becomes the basis for the expanded report.  When the process is executed, the single line is expanded to individual account lines, and placed in the final formatted worksheet. The maximum number of columns to be expanded is based on the option in the user's Spreadsheet Server Settings.

The following example shows a report based on an account mask:

• GXL formulas are created in a single line. In the example, the account string parameters are shown in cells B9:B13. The balance parameters of the GXL (for example, type, budget name, year, format, period, translated, currency, etc.) are entered in each column heading (B1:C7). The actual GXL formulas are contained in cells B16 and C16.
• A standard Excel formula is used to calculate the variance in cell D16.
• Various literals are entered in cells E16:K16. This designation generates the associated value to that particular column. Valid literals are ACCTNUM (account number), ACCTDESC (account description), and ACCTDESCnn/ACCTSEGnn (segment description and number respectively, where nn equals the account segment number, for example, 01, 02, 03, etc.).

Note: The GXE summarizes the generated results based on the account segment literals (for example, ACCTSEG01, ACCTSEG02, etc.) specified in the GXE source detail row. The account segment literals entered in the source detail row override the value entered in the Summarize Balance Level field, to determine how to summarize the results.

• Example:
  Account String = Company, Department, Account, Sub-Account
• Data:
  001-10-1000-A, 001-10-1000-B, 001-20-1000-C, 002-10-1000-A , 002-10-1000-B
• Ex 1:
  Source Row contains ACCTSEG03 only; GXE Results = 1000
• Ex 2:
  Source Row contains ACCTSEG01 and ACCTSEG03; GXE Results = 001-1000 and 002-1000

Note: The GXE also supports legacy SSInfinium user field literals (UF01, UF02, UF03, and UF04), in addition to legacy SSJDE account (ACCTCO, ACCTBU, ACCTOBJ, etc.) and category code (CATCODE and CATDESC) literals.


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.	A18
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.	16
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.	RANGE
Range/Table/Row	Specify the target table, range name, or first row in the target worksheet to begin inserting the expanded detail. Note: If using a target range, identify the Excel range as all cells under the column headings. In this example, RANGE1 is assigned to cells A5:J5.	RANGE1
Headings	Select whether to include column headings when expanding details. If not specified, N (no) is assumed.	N
Autofit	Select whether to autofit columns when expanding details. If not specified, N (no) is assumed.	N
Clear Sheet	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 in the Table/Range/Row parameter.	N
Journals Only Column	Not applicable if using the GXE to expand account detail.	(leave blank)
Journals Ad Hoc Query	Not applicable if using the GXE to expand account detail.	(leave blank)
Summarize Balance Level	If desired, specify the account segment number (break level) to be used for summarizing data when expanding account balances. Note: Any ACCTSEGnn literals entered in the Source Detail Row will override this value. Additionally, for JD Edwards ledgers, if desired specify an @ field alias or field name (as defined in the Configurator) for an AcctCatCode or BUCatCode to control how to group expanded data.	4
Total Journal Level	Not applicable if using the GXE to expand account detail.	(leave blank)
Journals to Table	Not applicable if using the GXE to expand account detail.	(leave blank)

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. Format the Target Worksheet with headings in rows 1-3, blank rows in rows 4-6, and totals in row 7. Set each total to a range of rows 4-6 (=SUM(B4:B6)).  When the Expand Detail Reports option is initiated, the total line is shifted down or up, based on the number of data rows populated each time the expansion is processed.

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