Uses the SUM function and expands an expression in an expression macro to return the sum of all fields. Optionally, you can apply a filter to limit the rows you read to calculate the total. You can use only one other aggregate function within SUM, and the nested function must return a numeric datatype.
The following table describes the arguments for this function:
Argument
Required/Optional
Description
macro _input _field
Required
Numeric datatype. Passes the values you want to add. You can enter a macro input field or an expression that includes at least one macro input field.
filter_condition
Optional
Limits the rows in the search. The filter condition must be a numeric value or evaluate to TRUE, FALSE, or NULL. You can enter any valid transformation expression.
Return Value
Numeric value.
NULL if all fields passed to the function are NULL or if no rows are selected. For example, if a filter condition evaluates to FALSE or NULL for all rows, %OPR_SUM% returns NULL .
If a single value is NULL, %OPR_SUM% ignores it. If all values are NULL, %OPR_SUM% returns NULL.
Group By
%OPR_SUM% groups values based on group by fields that you define in the transformation, and returns one result for each group.
If there is no group by field, %OPR_SUM% treats all rows as one group, returning one value.
Example
The %Sales% macro input field represents the following fields:
Q1, Q2, Q3, Q4
The following expression returns the sum of the fields that %Sales% represents:
%OPR_SUM[ %Sales% ]%
%OPR_SUM% adds the following sales figures from each quarter to return one value: