Creating and Using EXD Formulas

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

Note: If the query output is to be limited to a specific number of records, then the EXD Number of Records to Return field in the Query Designer must be specified to limit the records output in Excel. This value is saved in the query definition (.EDQ file).

· Value of "0" indicates no limit to EXD records returned in Excel.

· Positive integer indicates the number of records to be returned by the EXD formula in Excel.

1. In Excel, place the cell pointer where the formula is to reside.

2. In Excel from the ExecDash menu, select Formula Assistant. The Formula Assistant panel appears. On the panel, select the appropriate formula tab (EXD).

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

3. Use the table to enter data on the EXD - Formula Assistant panel.

Field Description

Query Name When the query resides in a Query Catalog, use the drop down list to select the query to be executed.

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

When the query resides in Query Exchange, click the Search Online button ( ) to navigate to and select the query to be executed (see Download Queries from Query Exchange).

Note: After a query is installed from Query Exchange then it becomes available from the File System.

Field Description

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.

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".

Parameters If applicable, specify the cells containing parameters for the query.

4. Click the Paste to Excel button to insert the formula into the cell. In Excel the formula displays a label (GenQueryDetail...) in the cell. The true formula (EXD(...)) can be seen in the Excel formula bar.

5. Execute the EXD formula using one of the following methods:

Generate a single, selected EXD formula:

Right click on the cell containing the EXD formula, and from the popup menu select Generate This Detail List (EXD).

From the EDash ribbon, select the Generate EXD(s) drop down arrow and from the drop down list select Selected.

Generate all EXD formulas in the workbook:

From the ExecDash menu, select Generate All Query Detail Lists.

From the EDash toolbar, select the Generate All EXDs icon.

From the EDash ribbon, select the Generate EXD(s) button -or- select the Generate EXD(s) drop down arrow and from the drop down list select All.

6.7.1 Sample EXD Formula Targeting a Cell

The following example demonstrates targeting a cell as the Starting Cell/Range/Table value.

1. Initial Excel setup for the example:

· Cell A1 - identifies the user-defined report name (i.e. OPEN INVOICES BY VENDOR).

· Cell A3 - user-assigned literal, indicating what the parameter value represents (i.e. Vendor).

· Cell B3 - identifies the parameter value needed to execute the EXD (i.e. 459).

· Cell C3 - user-assigned literal, indicating what type of formula will be used (i.e. EXD).

· Cell D3 - location where the EXD formula will reside.

· Cells A6-H6 - represents report headers (manually entered in this example).

· Cells A7-G7 - represents the first row of output data.

· Cell H7 - identifies the fill down column formula (i.e. D7*.09).

2. Setup on the EXD - Formula Assistant panel:

· Query Name - use the drop down list or the Open From File button to select the query (i.e. AP Open Invoices).

· Worksheet - use the drop down list to select the worksheet for the output (i.e. Sheet 1).

· Starting Cell/Range/Table - indicates the starting cell for the output data (i.e. A7).

· Fill Down Column - indicates the number of fill down columns at the end of the output data (i.e. 1).

· Include Column Headings - indicates whether or not the EXD formula output data retrieves headings from the query. The check box is not selected in this example as the headings have previously been keyed on the worksheet.

· Clear a Range of Cells - indicates the area of output data to be cleared prior to executing the EXD formula. As this example contains a fill down column (i.e. H7), the clear range starts on the second row of output data (i.e. A8:H500). This allows the fill down formula to remain for future executions of the EXD.

· Parameters (Vendor #) - indicates the cell on the worksheet where the query parameter resides (i.e. B3).

3. The completed worksheet after EXD execution:

6.7.2 Sample EXD Formula Targeting a Range

The following example demonstrates targeting a range as the Starting Cell/Range/Table value.

1. Initial Excel setup for the example:

· Cell A1 - identifies the user-defined report name (i.e. OPEN INVOICES BY VENDOR).

· Cell A3 - user-assigned literal, indicating what the parameter value represents (i.e. Vendor).

· Cell B3 - identifies the parameter value needed to execute the EXD (i.e. 459).

· Cell C3 - user-assigned literal, indicating what type of formula will be used (i.e. EXD).

· Cell D3 - location where the EXD formula will reside.

· Cells A6-H6 - represents report headers (manually entered in this example).

· Cells A7-G7 - a range was created in Excel for these cells and the range was named EXDTargetRange.

· Cell H7 - contains a formula (i.e. D7*.09) which will automatically be filled down based upon the rows in the range when the EXD is generated. This column MUST be directly to the right of the range.

2. Setup on the EXD - Formula Assistant panel:

· Query Name - use the drop down list or the Open From File button to select the query (i.e. AP Open Invoices).

· Worksheet - use the drop down list to select the worksheet for the output (i.e. Sheet 1).

· Starting Cell/Range/Table - indicates the name of the range for the output data (i.e. EXDTargetRange).

· Fill Down Column - indicates the number of fill down columns at the end of the output data (i.e. 1).

· Include Column Headings - indicates whether or not the EXD formula output data retrieves headings from the query. The check box is not selected in this example as the headings have previously been keyed on the worksheet.

· Clear Sheet/Range/Table - indicates whether or not to clear the range of output data prior to executing the EXD formula. In this example, the check box is selected.

· Parameters (Vendor #) - indicates the cell on the worksheet where the query parameter resides (i.e. B3).

3. The completed worksheet after EXD execution:

6.7.3 Sample EXD Formula Targeting a Table

The following example demonstrates targeting a table as the Starting Cell/Range/Table value.

1. Initial Excel setup for the example:

· Cell A1 - identifies the user-defined report name (i.e. OPEN INVOICES BY VENDOR).

· Cell A3 - user-assigned literal, indicating what the parameter value represents (i.e. Vendor).

· Cell B3 - identifies the parameter value needed to execute the EXD (i.e. 459).

· Cell C3 - user-assigned literal, indicating what type of formula will be used (i.e. EXD).

· Cell D3 - location where the EXD formula will reside.

· Cells A6-H6 - represents report headers (manually entered in this example).

· Cells A7-H7 - a table was created in Excel for these cells and the table was named EXDTargetTable.

· Cell H7 - contains a formula (i.e. D7*.09) which will automatically be filled down based upon the rows in the table when the EXD is generated. This column is in the table.

2. Setup on the EXD - Formula Assistant panel:

· Query Name - use the drop down list or the Open From File button to select the query (i.e. AP Open Invoices).

· Worksheet - use the drop down list to select the worksheet for the output (i.e. Sheet 1).

· Starting Cell/Range/Table - indicates the name of the range for the output data (i.e. EXDTargetTable).

· Fill Down Column - indicates the number of fill down columns at the end of the output data (i.e. 1).

· Include Column Headings - indicates whether or not the EXD formula output data retrieves headings from the query. The check box is not selected in this example as the headings have previously been keyed on the worksheet.

· Clear Sheet/Range/Table - indicates whether or not to clear the table of output data prior to executing the EXD formula. In this example, the check box is selected.

· Parameters (Vendor #) - indicates the cell on the worksheet where the query parameter resides (i.e.

B3).

3. The completed worksheet after EXD execution: