GEXQ Formula

The GEXQ formula is used to return a single value from the GEXQ (summary) portion of a query to a spreadsheet. Users may drill down on the summary value to see detailed results. The drill down results can appear in a single panel, tabbed panel, or worksheet, based on options in the user's Spreadsheet Server Settings, and how the drill down function is executed.

Note: ASHELL Statement may be added to the query, to launch an external program in place of query detail, as part of the drill down (see "SQL SHELL Function" in the Query Designer User Manual for more information).

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 (GEXQ) in the Query Formulas tab.
2. Use the following table to enter data in the Formula Builder > GEXQ 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 GEXQ formula to the target cell with cell references to parameters, or, to insert the GEXQ formula in the target cell, then insert parameter columns and rows to the top-left of the existing worksheet data.
Field	Click the drop-down list, and select the field to be used for summarizing. Only fields assigned an alias in the query appear in the list. If left blank, the system uses the GEXQ summary field assigned in the query.
Function	If the GEXQ summary field is to be defined within the formula, click the drop-down list, and select the summary type (SUM, COUNT, MIN, MAX, etc.).
Use/Parameter	If applicable, specify or modify the cells containing the query parameters. A maximum of 25 parameters may be selected.

Note: Values to be entered in the GEXQ formula vary based on whether the GEXQ summary field is defined in the query or the formula.



Example: GEXQ Summary Field Defined in the Query

Note: See "Create the Summary Field" in the Query Designer User Manual for more information about creating the GEXQ summary field in Query Designer.




Example: GEXQ Summary Field Defined in the Formula Assistant


3. Click Insert. The formula is inserted in the cell, and the summary portion of the query in the GEXQ formula is immediately executed. The true formula (GEXQ(...)) can be seen in the Excel formula bar.

4. Copy the GEXQ formula to the appropriate destination cells.

Note: If the GEXQ formula is used in conjunction with data generated through a GEXD formula, and is in an adjacent column, use the Fill Down Columns option to copy the GEXQ formula to the same number of rows generated by the GEXD formula (see GEXD formulas for more information).


5. To view detail records underlying a GEXQ summary value, select the cell containing the GEXQ formula, then, from the Spreadsheet Server Ribbon, select Drill Down, or, right-click, and select Spreadsheet Server > Drill Down. The Drill Down panel appears, displaying the detail records of the GEXQ summary value, or the data is pushed to a worksheet.