A vertical expansion performs the same calculation on multiple fields by generating multiple expressions. To use a vertical expansion, configure a macro input field that represents multiple incoming fields. When the task runs, the application performs the same calculations on each field that the macro input field represents.

For example, the following expression trims leading and trailing spaces from the address ports defined by the %addr% variable and writes the results to output fields with a suffix of _o:

%addr%_o = LTRIM(RTRIM(%addr%))

This results in the following expressions in the following output fields:

Horizontal expansion performs a calculation across multiple fields while expanding a single expression. To use a horizontal expansion, configure a macro input field that represents a set of incoming fields or a set of constants. When the task runs, the task expands the macro input field and then uses the fields or constants to calculate a complex expression.

You can use the following horizontal expansion functions:

%OPR_CONCAT%

Uses the CONCAT function and expands an expression in an expression macro to concatenate multiple fields. %OPR_CONCAT% creates calculations similar to the following expression:

FieldA || FieldB || FieldC...

%OPR_CONCATDELIM%

Uses the CONCAT function and expands an expression in an expression macro to concatenate multiple fields, and adds a comma delimiter. %OPR_CONCATDELIM% creates calculations similar to the following expression:

FieldA || ", " || FieldB || ", " || FieldC...

%OPR_IIF%

Uses the IIF function and expands an expression in an expression macro to evaluate a set of IIF statements. %OPR_IIF% creates calculations similar to the following expression:

IIF(<field> >= <constantA>, <constant1>,
  IIF(<field> >= <constantB>, <constant2>,
    IIF(<field> >= <constantC>, <constant3>, 'out of range')))

%OPR_SUM%

Uses the SUM function and expands an expression in an expression macro to return the sum of all fields. %OPR_SUM% creates calculations similar to the following expression:

FieldA + FieldB + FieldC...

For example, the following expression checks if any of the fields are null. If a field is null, it sets the Isnull field to a positive number:

Isnull=%OPR_SUM{IIF(ISNULL(%fields%),1,0]%

When expanded, the expression macro generates the following expression, and expands the expression to include all fields defined by the %fields% variable.

Isnull=IIF(ISNULL (fieldA, 1,0) + IIF(ISNULL(fieldB, 1, 0)...