Formula Builder for Queries

Formula Builder is a tool used to aid in quickly creating a template in a Spreadsheet Server spreadsheet. It populates the necessary columns and rows with required parameters, as provided by the user. The tool inserts a GEXQ, GEXD, GEXS, or GEXI formula using the provided parameters. Formulas available in the Formula Builder function are based on the user's licensed features. The availability of the SAP Explorer button in the GEXS and GEXI panels is based on user and/or connection authority settings in Application Configurator. The default value for Row/Col buttons is based on the option in the user's Spreadsheet Server Settings.

Note: See Formula Builder for more information about GL-related formulas.

1. In Excel, from the Spreadsheet Server Ribbon, select Formula Builder. The Formula Builder panel appears, opening to the last used formula.
2. Select the desired formula in the Query Formulas tab. The selected formula panel appears.
3. Use the following table to enter data in the Formula Builder > GEXQ panel:

Field	Description
GEXQ:	Returns a single value from the GEXQ (summary) portion of the query to the spreadsheet (see GEXQ Formulas for more information).
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	Click the drop-down list, and select the operation ( SUM, COUNT, MIN, MAX, etc.) to be applied to the Total Field . If left blank, the system uses the GEXQ summary type assigned in the query.
Parameters:	The parameter-related fields only appear when parameters have been specified in the selected query definition.
Use/Parameter	If selected, the query parameter is included in the formula. If a parameter is required, the associated Use/Parameter field is selected and disabled. A maximum of 25 parameters may be selected for processing.



4. Use the following table to enter data in the Formula Builder > GEXD panel:

Field	Description
GEXD:	Inserts query detail results to the spreadsheet (see GEXD Formulas for more information).
Query Name	Click the Browse button to navigate to, and, select the query to be executed. Click the SAP Explorer button () to search for, and, select the T-Code or SAP query (SQ00) to be executed (see SAP Explorer for more information).
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.



5. Use the following table to enter data in the Formula Builder > GEXS panel:

Field	Description
GEXS:	Opens a selector panel, based on query results (see GEXS Formulas for more information).
Query Name	Click the Browse button to navigate to, and, select the query to be executed Click the SAP Explorer button () to search for, and, select the SAP query (SQ00) to be executed (see SAP Explorer for more information).
Worksheet	Click the drop-down list, and select the worksheet destination for the formula. Defaults to the current active worksheet.
Starting Cell	Select the cell where the formula is to reside (typically adjacent to the Cell for Value).
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 GEXS formula to the target cell with cell references to parameters, or, to insert the GEXS formula in the target cell, then insert parameter columns and rows to the top-left of the existing worksheet data.
Selector Text	Specify the label for the GEXS.
Cell for Value	Specify the cell destination for the selected query value.



6. Use the following table to enter data in the Formula Builder > GEXI panel:

Field	Description
GEXI:	Displays query detail in a drill down panel (see GEXI Formulas for more information).
Query Name	Click the Browse button to navigate to, and, select the query to be executed Click the SAP Explorer button () to search for, and, select the SAP query (SQ00) to be executed (see SAP Explorer for more information).
Worksheet	Click the drop-down list, and select the worksheet destination for the formula. Defaults to the current active worksheet.
Starting Cell	Select the cell where the formula is to reside.
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 GEXI formula to the target cell with cell references to parameters, or, to insert the GEXI formula in the target cell, then insert parameter columns and rows to the top-left of the existing worksheet data.



7. Use the following table to enter data in the bottom section of the GEXQ, GEXD, GEXS, or GEXI panel.

Field	Description
Parameters:	The parameter-related fields only appear when parameters have been specified in the selected query definition.
Use/Parameter	If selected, the query parameter is included in the formula.
Row/Col	Select to toggle between Row and Col. This button determines if the query parameter will be located in the row to the left of the formula, or in a column above the formula. The default value for Row/Col buttons is based on the option in the user's Spreadsheet Server Settings.
Value	Type the value (single value, wildcard, list, range, or segment list), or, if available, click the ellipses ( ... ) button to execute the user defined look up query, to select the value to default in the template for the query parameter (see "Assign Query" in the Query Designer User Manual for more information). When a value is entered, the system automatically selects the associated Use/Parameter field for the parameter.
Thru Value	If the selected query is a T-Code or SAP query (SQ00), and the parameter is set to allow a selection range, if desired, type the ending range value for the parameter.



8. After the appropriate data has been entered and verified, click one of the following buttons:
• Insert: inserts parameter labels and values, and the formula, to the selected worksheet, and closes the panel.
• Cancel or Close: closes the panel.