Use conditional functions to determine if expressions meet a specified condition. Suppose you want to check whether customers fall into a specific age range and if that is the case, include specific data for that customer within the report. You could use the
If function to make the check, and if the condition is valid, display relevant data, otherwise do not display anything.
The expressions passed as conditions to a conditional function get evaluated and then interpreted as boolean conditions using the following rules:
- Boolean values are valid conditional values.
- Numerical values are considered valid conditions.
false, all other numerical values are considered
- Text values representing a boolean constant are considered valid conditions:
false. The match is case insensitive.
nullvalue is considered
- All other values are considered invalid conditional values and would yield an error.
The arguments of the conditional functions are evaluated using a "lazy" algorithm. No argument will get evaluated until its value is necessary. E.g.,
If(false, exp1, exp2) will not evaluate exp1 at all.
||Returns the value of the second or third argument, depending on the evaluation of the first argument. If the first argument evaluates to True the second argument is returned, if False the third argument is returned.
Example: Prints Over Budget when A is bigger than B
Example: Because A equals 500, B is subtracted from C. If A didn't equal 500, then empty text ("") would be returned.
Example: Prints sum only when vendor ID (numerical value) is not null and different than 0:
Example: Prints sum only when vendor name (text value) is not null:
||Returns one of the multiple return values, depending on the evaluation of an ordered list of conditions. The odd arguments are the conditions and the corresponding even arguments are the returned results. The result of the function is the first result argument (ReturnedValueN) after the first condition (ConditionN) that evaluates to
||Returns one of the multiple return values, depending on the evaluation and then matching of an expression. The first argument is the evaluated expression (SwitchOnExpression). Its value is compared to the match value arguments (MatchValueN). The latter are in even positions. The first odd argument (ReturnedValueN) following the matched value is returned as a result. If the last argument of the function is in an even position, it is considered the default result (DefaultValue) and gets returned when the expression doesn't match any of the match values. If there is no default value and the expression doesn't match with any of the match values, the function returns
The comparison between the switch-on and match values is case-sensitive.
The function requires at least one argument. If no arguments are passed, an error is shown
||Evaluates an expression and either returns the resulting value if different than
Example: The function returns 0 (zero) if "Price" is