GEXD Formula

Use the GEXD formula to directly insert detailed results of a query to a spreadsheet.

Note: See GEXD Limit for information about creating the GEXQ summary field in Query Designer.

1. In Excel, select the cell for the formula to reside and then select Formula Builder from the Spreadsheet Server ribbon. The Formula Builder panel displays. Select GEXD under Query Formulas.
2. Use the following table to enter data in the GEXD panel.

Field	Description
Query Name	Click Browse to navigate to and select the query to execute or select it from the drop-down list.
Worksheet	Select the worksheet destination for the formula from the drop-down list. Defaults to the current active worksheet.
Starting Cell	Specify the starting cell for the template.
Clear/ Shift Worksheet	Specify 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 and then insert parameter columns and rows to the top-left of the existing worksheet data.
Target Worksheet	Type the name of the worksheet or select the worksheet destination for the output from the drop-down list. Defaults to the current active worksheet.
Target Type	Specify whether the expanded detail should display in a table, range, or row in the target worksheet.
Target Cell/ Range/ Table	Type the cell, range, or table or select the desired range or table of the output target from the drop-down list. If you select Column Headings, 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, and so on. Note: If targeting a range, the fill down formulas or values must reside outside of the output range. If you select Column Headings, the fill down columns require to be on the first row following the heading. If not, the fill down columns require to be on the first two rows of the GEXD data. In some instances, you may use the GEXD Row function instead of this option.
Column Headings	If selected, the generated output data will contain column headings from the query. Do not select this option if you want to manually enter column headings in Excel.
Autofit Column Size	If selected, the system performs the Auto Fit Column Width feature of Excel when executing the formula.
Clear a Range of Cells	If the Target Type is a cell, specify the range of cells to clear before 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, this field 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) clears when executing the GEXD formula. Do not select this option if the output sheet includes GEXD formula or any of the query parameters.
Custom Layout	Select the custom layout to use from the drop-down list when generating the GEXD formula. The Create and Edit buttons open the Custom Layout Editor, allowing the user to create or modify custom layouts.
Use/Parameter	If applicable, specify or modify the cells containing the query parameters. You can select a maximum of 25 parameters.



3. Click Insert. The formula inserts into the cell and a label (GenQueryDetail...) appears in the cell. It also displays the date and time of formula completion. You can see the true formula (GEXD(...)) in the Excel formula bar.

4. To generate detail reports for all GEXD formulas in the workbook, select Execute Reports from the Spreadsheet Server ribbon. The results for all GEXD formulas expand into the appropriate formatted sheets. To generate detail reports for selected GEXD formulas, select the cells containing the GEXD formulas and select Execute Reports > Selected Reports > Selected Detail Reports (GEXD Formulas), or right-click and select Spreadsheet Server> Generate Selected Detail Reports (GEXD).


Note:
• If generating a GEXD to a table set to clear and the system detects extra non-fill down columns at the end of the output table, a message appears indicating it may lose the data in these extra columns and it allows the user to continue or abort processing the GEXD.
• If generating a GEXD to a range or table set to clear and the system detects data adjacent to the range or table, a message appears indicating it may lose the data in these adjacent columns and it allows the user to continue or abort processing for the GEXD.
• If the system detects columns without data when generating a GEXD to a cell, range, or table, those empty columns are not overwritten.