PARAM

Creates an input parameter for an analytic, and defines the requirements for the input value.

An input parameter is a placeholder that allows the user to specify the actual value when scheduling or running an analytic.

Syntax

//PARAM variable_name type <OPTIONAL> <MULTI> <SEPARATOR value> <QUALIFIER value>
<VALUES value_list> label
  <description>

Parameters

Name Description
variable_name

The name of the variable that stores the analytic input value(s) selected or specified by the user.

For example:

  • v_start_date
  • v_regions
  • v_input_file

Also serves as the unique identifier for the parameter.

Note

When an analytic is run, the variable is created only if the user provides an input value. If a parameter is optional, and the user skips it, the variable is not created.

If subsequent logic in the analytic requires the variable to exist, you can test for its existence, and if it does not exist, create it and initialize it. For more information, see Designing optional input parameters.
type

The data type of the parameter, which controls what sort of input values can be entered.

The following types can be specified using uppercase letters:

  • C – character data
  • N – numeric data
  • D – date subtype of datetime data
  • DT – datetime subtype of datetime data
  • T – time subtype of datetime data
  • L – logical data

Note

Qualifying character input values is required for an analytic to run successfully.

How PARAM... F works

You can also specify that a file upload utility, or a Windows file browser, opens:

  • F – opens a file upload utility, or a Windows file browser, and allows a user to select a non-Analytics input file for the analytic when running in AX Web Client or the Analysis App window

    Upon selection, the file name is automatically entered as a Character input value. Specify F only. Do not specify F C.

    For example:

    //PARAM v_input_file F...

For more information, see Specifying or selecting a non-Analytics input file for an analytic.

Note

A type of F is not supported for use in analytics run in Robots or AX Client. To specify an input file for these environments, use the FILE tag. For more information, see FILE.
OPTIONAL

optional

Specifies that the parameter is optional and the user does not need to enter a value.

For more information, see Designing optional input parameters.
MULTI

optional

Specifies that the parameter accepts multiple input values.

Note

MULTI cannot be used if type is L (Logical) or F (File).

MULTI and VALUES

MULTI can be used with or without the VALUES option:

MULTI 

VALUES 

The user can select one or more values from a list of values.

MULTI 

VALUES 

The user can manually enter one or more values.

For more information, see Summary of the MULTI and VALUES options.

Multiple character input values

If you specify MULTI , and type is C (Character), you can also specify the SEPARATOR and QUALIFIER options to automatically insert separators (delimiters) and text qualifiers in a string of input values.

Note

Delimiting and qualifying multiple character input values is required for an analytic to run successfully. The separators and qualifiers can be inserted automatically, or manually by the user.
SEPARATOR value

optional

SEPARATOR can be used only when MULTI is specified, and type is C (Character).

Specifies that a separator character is automatically inserted between multiple character input values, creating a delimited list that is passed to the analytic for processing.

value specifies the separator character to use. A commonly used separator, or delimiter, is the comma , .

If SEPARATOR is omitted, a single space is used as the separator by default. The space character cannot be specified as value.

For more information, see Delimiting and qualifying character input values.
QUALIFIER value

optional

QUALIFIER can be used only when MULTI is specified, and type is C (Character).

Specifies that a text qualifier character is automatically inserted at the start and end of each character input value in a delimited list that is passed to the analytic for processing. Any text enclosed within the qualifier characters is treated as plain text.

value specifies the qualifier character to use. A commonly used qualifier is the single quotation mark ' .

If QUALIFIER is omitted, there is no default qualifier used. You cannot specify a space character as value.

For more information, see Delimiting and qualifying character input values.

Note

Analytic input parameters currently do not support the use of the double quotation mark (") as a text qualifier. You can use the single quotation mark (') instead. Specifying a double quotation mark qualifier will cause the PARAM tag to malfunction.
VALUES value_list

optional

A list of values that the user can select from when running the analytic.

Use the following syntax to specify the values:

VALUES |Value 1|Value 2|Value 3|Value n|

VALUES and MULTI

VALUES can be used with or without the MULTI option:

VALUES 

MULTI 

The user can select one or more values from the list of values.

VALUES 

MULTI 

The user can select a single value from the list of values.

For more information, see Summary of the MULTI and VALUES options.

Format of values in value_list

Character values
  • can contain spaces and punctuation
Numeric values
  • can be positive or negative
  • must be specified using decimal notation, and without a thousands separator

    For example, 1500.00 or -1500.00
Datetime values
  • Date must be specified using the format MM/DD/YYYY

    For example, 12/31/2014

  • Datetime must be specified using the format MM/DD/YYYY hh:mm:ss

    For example, 12/31/2014 23:59:59

  • Time must be specified using the format hh:mm:ss

    For example, 23:59:59
Logical values VALUES cannot be used if type is L (Logical)
label

The user interface label for the parameter.

In client applications, label is displayed with the input field.
description

optional

Descriptive text that provides additional information about the parameter.

In client applications, description is displayed with the input field.

description can provide instructions that assist the user. For example, "Enter the cutoff date for the payroll period".

description must be entered on the next line after the associated PARAM tag. The text can be multiline, but it cannot skip lines. Line breaks are not preserved when displayed in client applications.

Examples

Basic examples

Allows the user to optionally specify a date range:

//PARAM v_start_date D OPTIONAL Start Date (Optional)
  Enter the start date for the analysis
//PARAM v_end_date D OPTIONAL End Date (Optional)
  Enter the end date for the analysis

Requires the user to select a maximum number of transactions to process:

//PARAM v_maxTrans N VALUES |250|500|750|1000| Maximum transactions to process

Requires the user to specify one or more merchant category codes:

//PARAM v_codes C MULTI SEPARATOR , QUALIFIER ' MC Codes to include
  Specify one or more merchant category codes. Press "Enter" after each code, so that each code is on a separate line. Do not enclose codes in quotation marks.

Requires the user to select one or more merchant category codes:

//PARAM v_codes C MULTI SEPARATOR , QUALIFIER ' VALUES |4121 Taxis/Limousines|5812 Restaurants|5813 Drinking Places - Alcoholic Beverages|5814 Fast food restaurants| MC Codes to include
  Select one or more merchant category codes.

Advanced examples

Require a user to specify an amount range

You need to classify the records in a table that fall between a minimum and maximum amount range. This range changes occasionally, so you provide input parameters that allow the user who runs the analytic to define the range when scheduling or running the script:

COMMENT
//ANALYTIC test_analytic
//PARAM v_min_amount N Minimum Amount
  Enter a minimum amount
//PARAM v_max_amount N Maximum Amount
  Enter a maximum amount
END

CLASSIFY ON %v_FieldA% IF BETWEEN(AMOUNT, v_min_amount, v_max_amount) SUBTOTAL AMOUNT TO "Classified_%v_AnalysisTable%.FIL"

Allow the user to optionally exclude one or more customer numbers

You need to classify the records in a table but you want to give the user the option to exclude some customers from the analysis.

To do this, you provide an optional character parameter. Your script tests whether or not the value is provided, and if so, those customer numbers are excluded from the classify command:

COMMENT
//ANALYTIC test_analytic
//PARAM v_cust_no C OPTIONAL MULTI SEPARATOR , QUALIFIER ' Customer Number(s) to exclude (optional)
  Specify one or more customer numbers. Press "Enter" after each number, so that each number is on a separate line. Do not enclose numbers in quotation marks.
END

IF FTYPE("v_cust_no") = "U" v_cust_no = ""
GROUP IF v_cust_no = ""
  CLASSIFY ON %v_FieldA% SUBTOTAL AMOUNT TO "Classified_%v_AnalysisTable%.FIL"
ELSE
  CLASSIFY ON %v_FieldA% IF NOT MATCH(CUSTNO, %v_cust_no%) SUBTOTAL AMOUNT TO "Classified_%v_AnalysisTable%.FIL"
END

Allow the user to select an input file (AX Web Client or the Analysis App window only)

You are distributing an analysis app to colleagues who will run it in the Analysis App window. When they run the analytic script in the app, you want to provide them with a Windows file browser to select a Microsoft Excel file to import data from:

COMMENT
//ANALYTIC test_analytic
//PARAM v_input_file F Input File
  Select an input file
END

IMPORT EXCEL TO Trans_May_raw Trans_May_raw.fil FROM "%v_input_file%" TABLE "Trans2_May$" CHARMAX 100 KEEPTITLE

Require the user to specify an input file path and file name (the Analysis App window only)

You are distributing an analysis app to colleagues who will run it in the Analysis App window. When they run the analytic script in the app, you want them to specify a filepath and filename to use as an import file:

COMMENT
//ANALYTIC test_analytic
//PARAM v_input_file C Input File Path and Name
  Enter an absolute file path and a file name, for example: C:\Users\username\Documents\ACL Data\Sample Data Files\ Trans_May.xls
END

IMPORT EXCEL TO Trans_May_raw Trans_May_raw.fil FROM "%v_input_file%" TABLE "Trans2_May$" CHARMAX 100 KEEPTITLE

Using default values for optional parameters

You are creating an analytic that extracts transaction records to a results table. You want to give the user who runs the script the option of providing a date range as well as a list of entities for filtering the records to extract.

To do this, you create three optional parameters:

  • v_start_date
  • v_end_date
  • v_entity_list

In the opening lines of the script, you test if these values are set. If they are not set, you set default values of the minimum and maximum dates as well as a default flag to test for with v_entity_list.

In your EXTRACT command, you use the values to filter the records:

COMMENT
//ANALYTIC test
  This analytic tests the PARAM
//RESULT TABLE t_results
//PARAM v_start_date D OPTIONAL Enter Start Date
//PARAM v_end_date D OPTIONAL Enter End Date
//PARAM v_entity_list C MULTI OPTIONAL |entity1|entity2|
END

IF NOT ISDEFINED("v_start_date") v_start_date = `19000101`
IF NOT ISDEFINED("v_end_date") v_end_date = `99991231`
IF NOT ISDEFINED("v_entity_list") v_entity_list = "'all'"

EXTRACT FIELDS ALL TO t_results IF BETWEEN(transaction_date v_start_date v_end_date) AND (MATCH(entity_field %v_entity_list%) OR v_entity_list = "'all'")

Remarks

Designing optional input parameters

If you use OPTIONAL with the PARAM tag, the variable associated with the analytic input parameter may or may not be created when the analytic runs:

  • variable automatically created if the user specifies an input value
  • variable not created if the user skips the optional parameter and does not specify an input value

Test for the existence of the parameter variable

If subsequent logic in the analytic depends on being able to evaluate the contents of the parameter variable, including evaluating an empty or null state, you need to test for the existence of the parameter variable. If the parameter variable does not exist, you need to create it and initialize it to null.

Use the IF command with the FTYPE( ) function, or the ISDEFINED( ) function, to perform the test and create the variable if it does not exist:

IF FTYPE("var_name") = "U" var_name = ""
IF NOT ISDEFINED("var_name") var_name =	""

When to perform the test

Perform the test after the analytic header and before any Analyticsscript logic that depends on the existence of the parameter variable.

Summary of the MULTI and VALUES options

The table below summarizes the effect of the MULTI and VALUES options on the user input control in the user interface.

User input control (Robots)

Parameter design

MULTI

VALUES

A single input value manually entered in a field

One or more input values manually entered in a field

A single input value selected from a drop-down list of values

One or more input values selected from a checklist of values

Delimiting and qualifying character input values

For an analytic to run successfully, character input values must be delimited by a separator if there is more than one value, and values must be qualified.

Avoid nested text qualifiers

When you create character input parameters, and when you instruct users of the analytic how to enter character input values, you need to be careful to avoid creating redundant or nested text qualifiers (qualifiers within qualifiers). Redundant text qualifiers will cause the input parameter to malfunction.

Methods for inserting text qualifiers

There are four different methods available for inserting text qualifiers around character input values. Depending on the method, a separator is also inserted between the input values.

As you develop an analytic, you may need to experiment with different methods to find what works best for the character values that users will input.

Note

One or more of the methods may not be applicable, depending on how you are using the MULTI and VALUES options.

Each input parameter must use only one of these methods.

1 Use SEPARATOR and QUALIFIER

Include the SEPARATOR and QUALIFIER options in the PARAM tag.

For example:

//PARAM v_regions C MULTI SEPARATOR , QUALIFIER '

Not applicable if you use VALUES without MULTI.

Tip

Use this method whenever possible. It is the least labor-intensive and the least error-prone.
2 Manually specify separators and qualifiers

Require the user of the analytic to manually specify separators and qualifiers in addition to the actual input values.

For example:

'North America','Europe','Asia'

Not applicable if you use VALUES with or without MULTI.
3 Include qualifiers in the value_list

Include qualifiers with each value in the value_list specified with the VALUES option.

For example:

VALUES |'Asia'|'Europe'|'Middle East'|'North America'|

Not applicable if you use MULTI without VALUES.
4 Enclose the parameter variable in qualifiers

In the syntax of the Analytics script, enclose the parameter variable in text qualifiers.

For example:

IF MATCH(REGIONS, "%v_regions%")

Use this method only if you are using VALUES without MULTI.
 

Note

Analytic input parameters currently do not support the use of the double quotation mark (") as a text qualifier. You can use the single quotation mark (') instead with the QUALIFIER option, in the value_list, or when manually specifying qualifiers around input values. Double quotation marks can be used as text qualifiers in the body of an Analytics script.

When to use the different methods

The table below summarizes when to use the different methods for inserting text qualifiers.

 

MULTI 

VALUES 

MULTI 

VALUES 

MULTI 

VALUES 

Method 1

Use the SEPARATOR and QUALIFIER options

If used, do not use Method 2

Not applicable

If used, do not use Method 3

Method 2

Manually specify separators and qualifiers

If used, do not use Method 1

Not applicable

Not applicable

Method 3

Include qualifiers in the value_list

Not applicable

If used, do not use Method 4

If used, do not use Method 1

Method 4

Enclose the parameter variable in qualifiers

Do not use

If used, do not use Method 3

Do not use

Specifying or selecting a non-Analytics input file for an analytic

The table below summarizes the different methods for specifying or selecting a non-Analytics input file for an analytic. The method you choose is partly dependent on which client application will be used to run the analytic.

Method

Details

 Robots

AX Client

AX Web Client

The Analysis App window

PARAM tag with type of 'F'
  • AX Web Client user selects the input file using a file upload utility

    The file name is automatically specified as the analytic input value. The file is automatically uploaded to the appropriate Related Files subfolder on AX Server.

  • Analysis App window user selects the input file using the Windows file browser

    The file path and the file name are automatically specified as the analytic input value.

This method is the best option because it combines flexibility, ease of use, and precision.

  

 

PARAM tag with type of 'C'

The user manually specifies an input file path and file name as an analytic input value.

This method provides flexibility because the file path and the file name are not specified in advance. However, it is laborious and error prone because it requires the user to manually enter these values.

  

 

  

FILE tag

(For more information, see FILE)
  • Robots the input file must be located in the Input/Output tab in the robot
  • AX Client, AX Web Client the input file must be located in the appropriate Related Files subfolder on AX Server

 

Input file path and file name hard-coded in the analytic

This method avoids use of the PARAM tag, however it is the least flexible. On every computer where the analytic is run, the user must ensure that the input file has a file path and a file name identical to those specified in the analytic.

  

 

  

[ Back to top ]

ACL Scripting Guide 14.1