%OPR_SUM%
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.
Syntax
%OPR_SUM[ macro_input_field [, filter_condition ] ]%
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:
Q1 | Q2 | Q3 | Q4 | RETURN VALUE |
|---|
50 | 200 | 50 | 100 | 400 |
100 | NULL | 100 | 250 | 450 |
500 | 200 | 200 | 200 | 1100 |
NULL | NULL | NULL | NULL | NULL |
400 | NULL | NULL | 400 | 800 |