GEXD Formula

The GEXD formula is used to directly insert the detail results of a query to a spreadsheet.

1. In Excel, select the cell where the formula is to reside, then, from the Spreadsheet Server Ribbon, select Formula Builder.  The Formula Builder panel appears. Select the formula (GEXD) in the Query Formulas tab.
2. Use the following table to enter data in the Formula Builder > GEXD panel:

Field	Description
Query Name	Click the Browse button to navigate to, and, select the query to be executed.
Worksheet	Click the drop-down list, and select the worksheet destination for the formula. Defaults to the current active worksheet.
Starting Cell	Specify the starting cell for the template.
Clear/Shift Worksheet	Specify, when inserting data, whether to overwrite existing cell data in the worksheet, clear all cell data in the worksheet, insert the GEXD formula to the target cell with cell references to parameters, or, to insert the GEXD formula in the target cell, then insert parameter columns and rows to the top-left of the existing worksheet data.
Target Worksheet	Type the name of the worksheet, or click the drop-down list, and select the worksheet destination for the output. Defaults to the current active worksheet.
Target Type	Specify whether the expanded detail should be inserted into a table, range, or row in the target worksheet.
Target Cell/Range/Table	Type the cell, range, or table, or, click the drop-down list, and select the desired range or table of the output target. If Column Headings is selected, the cell represents the left-most column heading as defined in the query; otherwise it represents the left-most cell of the first row of data. If the range or table does not exist, a prompt appears, allowing for the creation of the table.
Fill Down Columns	Specify the number of columns that contain formulas or values to the right of the results grid. These columns fill down to match the number of records in the results. Valid values are: 1, 2, 3, etc. Note: If targeting a range, the fill down formulas or values must reside outside of the output range. If Column Headings is selected, the fill down columns are required to be on the first row following the heading. If not selected, the fill down columns are required to be on the first two rows of the GEXD data. In some instances, the GEXD Row function may be used in place of this option.
Column Headings	If selected, the generated output data will contain column headings from the query. Do not select this option if column headings will be manually entered in Excel.
Auto Fit Column Sizes	If selected, the system performs the Auto Fit Column Width feature of Excel, when the formula is executed.
Clear a Range of Cells	If the Target Type is a cell, specify the range of cells to be cleared prior to executing the formula. Note: if using Fill Down Columns, exclude the first row from the clear range to avoid deleting the fill down formula or value (for example, if the Starting Cell is A7, the Clear Range of Cells must be A8:xx.
Clear Sheet/Range/Table	If selected, the entire output sheet (specified in the Target Worksheet field), or the range or table (specified in the Target Cell/Range/Table field) is cleared when the GEXD formula is executed. Do not select this option if the GEXD formula or any of the query parameters are located in the output sheet. Note: see Clear a Range of Cells for clearing a range of cell data.
Custom Layout	Click the drop-down list, and select the custom layout to be used when the GEXD formula is generated. The Create and Edit buttons open the Custom Layout Editor, allowing the user to create or modify custom layouts used when the GEXD formula is generated.
Use/Parameter	If applicable, specify or modify the cells containing the query parameters. A maximum of 25 parameters may be selected.


3. Click Insert. The formula is inserted into the cell, and a label (GenQueryDetail...) appears in the cell. The true formula (GEXD(...)) can be seen in the Excel formula bar.

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

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

5. Note:
   

   • If generating a GEXD to a table set to be cleared, and the system detects extra non-fill down columns at the end of the output table, a message appears, indicating the data in these extra columns will be lost, and allows the user to continue, or abort processing for the GEXD.
   • If generating a GEXD to a range or table set to be cleared, and the system detects data adjacent to the range or table, a message appears, indicating the data in these adjacent columns will be lost, and allows the user to continue, or abort processing for the GEXD.