Build a Template

Build a Template is a tool used to aid in quickly creating a template within an Executive Dash spreadsheet.

This tool populates the necessary rows and columns with required parameters as provided by the user. The tool inserts an EXQ, EXD, EXS, or EXI formula using the parameters.

1. In Excel from the ExecDash menu, select Build a Template. The Build Template panel appears.

Nav Tip:This panel may also be accessed by selecting the EDash ribbon equivalent (see EDash Ribbon).

2. Select the tab of the desired formula. The selected formula panel appears.

3. Specify the query to be used in the formula.

· When the query resides in the File System, click the Open From File button ( ) to navigate to and select the query to be executed.

4. Use the table to enter data on the EXQ tab of the Build Template panel.

Field Description

EXQ: Returns a single value from the EXQ (Summary) portion of the query into the spreadsheet. Refer to Creating and Using EXQ Formulas for more information.

Total Field If the EXQ summary field is to be defined within the formula, use the drop down list to select the field to be summarized.

Total Type If the EXQ summary field is to be defined within the formula, use the drop down list to select summary type (i.e. SUM, COUNT, MIN, MAX).

5. Use the table to enter data on the EXD tab of the Build Template panel.

Field Description

EXD: Directly inserts the Detail results of the query into the spreadsheet.

Refer to Creating and Using EXD Formulas for more information.

Worksheet From the drop down list select the worksheet destination for the output.

Defaults the current active worksheet.

Starting Cell/Range/Table Specify the upper-left corner of the output target. This value may be a single cell, a table or a range. If Include Column Headings is selected, then it represents the left-most column heading as defined in the query.

Otherwise it represents the left-most cell of the first row of data.

Fill Down Columns Specify the number of columns that contain formulas/values to the right of the results grid. These columns will fill down to match the number of records in the results. Valid values are 1, 2, 3, etc.

Note: When using Fill Down Columns targeting a range the fill down formulas/values must reside outside of the output range.

Note: When Include Column Heading is selected, the fill down columns are required to be on the first row following the headings. When Include Column Headings is not selected, the fill down columns are required to be on the first two rows for the EXD data.

Note: In some instances, the EXD Row function may be used in place of fill down columns. Refer to EXD Row Function for more information.

Include Column Headings Select the check box to indicate that generated output data will contain column headings from the query. Do not select the check box if column headings will be manually entered in Excel.

Auto Fit Column Sizes Select the check box to have the system perform the Autofill Column Width feature of Excel when the formula is executed.

Field Description

Clear Sheet/Range/Table Select the check box to have the system clear the entire output sheet (specified in the Worksheet field) or range or table (specified in the Starting Cell/Range/Table field) to be cleared when the EXD formula is executed. Do not select this check box if the EXD formula or any of the parameters for the query are located on the output sheet.

Note: See Clear a Range of Cells for clearing a range of cell data.

Clear a Range of Cells Specify the range of cells to be cleared prior to executing the EXD formula.

Note: If Fill Down Columns are used, be sure to exclude the first row from the clear range. If the first row is cleared then the formula/value to be filled down will be deleted and blanks will be filled.

Example: When a Fill Down Column is specified, if the Starting Cell is A7, the Clear Range of Cells should be "A8:xx".

6. Use the table to enter data on the EXS tab of the Build Template panel.

Field Description

EXS: Opens a selector window with the values returned from the query.

Refer to Creating and Using EXS Formulas for more information.

Selector Text Specify the label for the EXS.

Cell for Value Specify the cell destination for the selected query value(s).

8. Use the table to enter data in the Excel Properties section of the Build Template panel.

Field Description

Excel Properties:

Worksheet From the drop down list select the worksheet destination for the formula. Defaults the current active worksheet.

Starting Column/Row Identify the starting column and row for the formula template.

Use Absolute Cell References

Select the check box to automatically lock both the column and row values of the query's parameter cell references. If not selected, the system will only lock the column value when the parameter is assigned to a row and only lock the row value when the parameter is assigned to a column.

Line Description Enter a description to be assigned to the formula. This value is placed in the cell prior to the cell containing the formula.

Clear Worksheet Select the radio button to clear all cell data on the target worksheet when inserting data.

Do Not Clear Worksheet Select the radio button to not clear cell data on the target worksheet when inserting data.

Shift Worksheet Data Select the radio button to insert the formula in the target cell and to insert the data for newly inserted columns and rows to the top and to the left of the existing worksheet data.

Field Description

Parameters: The parameter related fields only appear when parameters have been specified in the selected query definition.

Display/Use Select the check box to include the query parameter in the formula.

Row/Col Select the button to toggle between Row and Col. This button

determines if the query parameter will be located on the row to the left of the formula or in a column above the formula.

Value Enter the value to default on the template for the query parameter. The value may be a single value, a wildcard, list, range, or segment list.

9. After the appropriate data has been entered and verified, click one of the following toolbar icons or buttons.

· Insert (icon) -- inserts parameter labels and values and the formula to the selected worksheet, and keeps the panel open for additional inserts.

· Insert (button) -- inserts parameter labels and values and the formula to the selected worksheet and closes the panel.

· Close (button) -- closes the panel.