When generating reports, the ability to apply operators is essential for transforming raw data into actionable insights. Operators are special symbols or keywords used to perform operations on data values, allowing you to conduct calculations, comparisons, and logical evaluations. They play a critical role in shaping the information presented in your reports, making them more insightful and tailored to your needs.
Math operators
Basic math operators (addition, subtraction, multiplication and division) can be used to calculate numbers.
- Both operands must be the same type.
- Dates and booleans cannot perform math operations.
- Strings can only use the + operator, which works as concatenation ('abc'+'de' = ‘abcde’)
- Do not divide by zero. Use an IF function to avoid potential division by zero.
- The minus operator can be placed before a number to indicate a negative value, except when another operator immediately precedes it (2--1). Instead, use (2-(-1)).
Parameter values:
| Operator | Valid Examples | Invalid Examples |
|---|---|---|
| + | measure + 2 1+1 dimension + dimension + ' Text' |
dimension + 2
+1+1 |
| - |
- measure - measure
2-(-1) |
datefield - today
2--1 |
| * | measure * (1+2) | ‘mytext’ * 3 |
| / |
measure / SUM(measure)
measure / IF(measure = 0, 1, measure) |
measure / UPPER(dimension) measure / 0 |
Comparison Operators
Compare operands are used for mathematical and logical operations. These operators can only be utilized within functions that support conditional expressions, such as CASE and IF.
Some results examples:
- Numbers: (5 > 2) will return true
- Dates: (2022-01-01 > 2021-01-01) will return true
- Strings: (Z > A) will return true
Tip: Both operands need to be of same type. You cannot compare a string with a number.
Note: Performing comparisons are not valid outside CASE and IF conditions. The examples below should not be valid by their own, but only on the condition part of these functions.
Parameter values:
| Operator | Valid Example | Invalid Example |
|---|---|---|
| = |
1 = 1
dimension = dimension SUBSTRING(dimension, 1, 3) = ‘Str’ |
1 ==1
dimension = measure SUBSTRING(dimension, 1, 3) = datefield |
| != |
'2022-01-01' = ‘2022-01-01'
dimension != NULL |
'2022-01-01' != 5 |
| > |
measure > (5+1)
dimension > dimension |
dimension > AVG(measure) |
| >= | datefield >= GETDATE() | datefield >= today |
| < |
datefield < ‘2022-01-01 00:30:00’
dimension < MAX(dimension) |
datefield < ‘2022-01-01 aaa’ |
| <= | AVG(field) <= SUM(field2) | AVG(field) <= (1=1) |
| IN | field IN ('value1', ‘value2’) | field IN (field2) |
| NOT IN | LOWER(field) NOT IN ('value1', ‘value2’) | field NOT IN ('value', LOWER('value2')) |
Other Operators
Beyond the basic arithmetic and logical operators, there are several other operators that enhance the functionality and flexibility of your reports. These additional operators enable advanced calculations, string manipulations, and data transformations, providing a more comprehensive toolkit for data analysis.
Note: NULL can use the = and != operators. Other operators are allowed but may return weird results.
Parameter values:
| Token | Rule | Valid Example | Invalid Example |
|---|---|---|---|
| ( | Opens a scope for an expression or function parameters |
1 + (field)
1+ (((field*(field))+2)) AVG(field) cond AND (cond OR cond) |
1+(field 1+) (1+) () AVG field |
| ) | Closes a scope (must match number of open parenthesis) | ||
| , | Separates function arguments | CHARINDEX('x' , 'z' , 0) |
CHARINDEX('x' , , ) CHARINDEX('x) |
| NULL | Used to check (comparison) if a field value is NULL |
IF(field=NULL, 1, 0)
NULL |
NULL+1 AVG(NULL) |