Creating and Using Parameters

5.6.2.1 Parameters in Query Designer vs Excel

Parameters are added to a query as a place-holder for when selection criteria is to be specified in Excel. A default parameter value may be defined in the query for testing purposes, and then the actual criteria is specified in Excel at the time the formula is executed.

Query Designer

When a query is created or modified, it is normal to execute the query in Query Designer to test the results.

When testing a query parameter values are specified in the Parameters panel. Refer to Parameters Panel for more information.

Excel

Executive Dash queries are executed from Excel via formulas (EXQ, EXD, EXS, EXI). Each of these formulas have arguments where parameter values may be specified. These values may be hard-coded in the formula definition; however, it is recommended to use cell references.

5.6.2.2 Standard Parameters in the Query Designer

Standard Parameters allow the user full control over how the system interprets a parameter. When using standard parameters the query writer must know various SQL rules for working with text fields, numeric values, lists and ranges of values.

Note: Standard Parameters differ from Smart Parms. Smart Parms simplify query writing by using standard rules for processing parameters. Refer to Smart Parms Criteria Entry Rules and

Smart Parms in the Query Designer for more information.

To Create a Parameter:

1. On the Parameters panel, click the Add button. The Add a Parameter panel appears.

2. On the Add a Parameter panel, enter a description and default test value for the parameter.

3. Click the Save button, and then OK to confirm.

To Insert a Parameter into a Criteria Field:

1. On the Field List panel, click the ellipse button -or- double click within one of the seven Criteria fields for the desired field. The Builder panel appears.

2. On the Builder panel in the Source section, click the plus sign (+) to the left of Parameters. The system expands the list of available parameters.

3. From the expanded parameter list, select the parameter to be used.

4. Double click <value as (number or string)>. The parameter, &PARM01, appears in the work area on the Builder panel.

5. Enter any comparison operators and other characters as necessary to complete the criteria expression (i.e. =&PARM01).

6. Click OK. The Field List panel appears with the parameter inserted in the selected criteria field.

5.6.2.3 Smart Parms Criteria Entry Rules

When using standard parameters the query writer must know various SQL rules for working with text fields, numeric values, lists and ranges of values. Smart Parms simplify query writing by using standard rules for processing parameters. The rules used by Smart Parms are similar to those used in Global's Spreadsheet Server products for specifying segment values.

Below is a comparison by example of SQL (iSeries DB2) rules for specifying criteria values used by standard parameters versus Smart Parms.

Criteria Type Standard Parameters Smart Parms

Single Value - Text ='ABC' ABC

Single Value - Numeric =123 123

Single Value - Date =DATE('01/01/2007') 1/1/2007

Range - Text BETWEEN 'ABC' AND 'DEF' ABC.DEF

Range - Numeric BETWEEN 123 AND 456 123.456

Range - Date BETWEEN DATE('01/01/2007')

Wildcard for All Values LIKE '%' * or a blank

Wildcard for a Single Value LIKE '_500' *500

Greater Than or Equal To >=1000 1000.99999999

Less Than or Equal To <=1000 -99999999.1000

Logical NOT (using alphabet as dataset)

NOT IN('A','B','C') [D.Z]

Segment List N/A ^Country

With Smart Parms no comparison operators are used (=,<>,<,>,BETWEEN, LIKE, NOT).

The Smart Parm routine actually converts the expression to one that uses SQL rules.

5.6.2.4 Smart Parms in the Query Designer

Smart Parms may be added to a field directly on the Field List panel or via the Builder panel.

Note: Prior to adding a Smart Parm to a field an alias name must be assigned to the field. Refer to Assigning Aliases to Fields for more information.

To Add Smart Parms via the Field List Panel:

1. On the Field List panel, right click on the desired field. The Field List popup menu appears.

2. On the Field List popup menu, select Add Smart Parm for This Field. A parameter for the field is added to the Parameter panel with a blank value, and the Smart Parm (&SPARM01) is inserted into the Criteria 1 field.

To Add Smart Parms via the Builder Panel:

Note: This method must be used to add Smart Parms in other Criteria fields besides Criteria 1.

1. If the parameter doesn't already exist, create it. Refer to Standard Parameters in the Query Designer for more information.

2. On the Field List panel, click the ellipse button -or- double click within one of the seven Criteria fields for the desired field. The Builder panel appears.

3. On the Builder panel in the Source section, click the plus sign (+) to the left of Parameters. The system expands the list of available parameters.

4. From the expanded parameter list, select the parameter to be used.

5. Double click <value as smart parm>. The Smart Parm appears in the work area on the Builder panel. No other supporting characters (i.e. =, <, >) are required.

6. Click OK. The Field List panel appears with the Smart Parm inserted in the selected criteria field.

To Delete Smart Parms:

Note: If multiple Smart Parms exist for a query, only the last (highest number) Smart Parm in the list may be deleted. Example: Have Smart Parms 1, 2 and 3. Smart Parm 3 may be deleted; however, Smart Parm 2 may not be deleted unless Smart Parm 3 is first deleted.

1. In the Parameters panel, select the highest number Smart Parm and click Del. The system displays a confirmation panel. Click OK. The system removes the Smart Parm from the Parameters panel.

2. On the Field List panel, click the associated Smart Parm criteria field value. The Builder panel appears.

Delete the Smart Parm field value. Click OK. The system removes the criteria field value from the Field List.

3. Repeat the steps above to delete additional Smart Parms.

5.6.2.5 Parameters in Excel

Parameters are used in Excel in conjunction with Executive Dash formulas and provide criteria for the query being executed.

Prerequisite

Before using parameters in Excel, the query must be created and saved in the Query Designer. In addition, if using the Query Catalog, the query must also be save to the catalog.

To Use Parameters in Excel:

1. Prior to using a new or modified query, in Excel from the ExecDash menu, select Refresh Queries. The system refreshes query data.

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

2. Layout the Excel report and assign cell references for each parameter.

3. Position the cell pointer to where the Executive Dash formula is to go.

4. Open the Formula Assistant, assign values to the arguments, including the cell positions of the parameters.

6. To change the query criteria value, enter the new value in the cell designated and re-run the query.