Nevron Vision for SharePoint Documentation

Expressions

Pivot Engine > Expressions |

Expressions are widely used in the pivot design model to define grouping, sorting and filtering rules and to also define dynamic property values for data groupings, data cells and pivot properties. A large set of Functions is implemented to ensures that you have the needed flexibility to define complex pivots that perform sophisticated data analysis. A scope based format is provided to ensure that you reference Variables in correct/desired evaluation scope.

Overview

Expressions are strings that embed formulas, which the pivot engine evaluates when it processes the design pivot to build the runtime pivot. Expressions are hence only available at design time - at runtime you get the result of their evaluation either as property values or as runtime pivot structure (because expressions are used to control the generation of runtime data members for design time data groupings).

Expressions start with **'='** (Equals) character and are followed by a formula. A valid expression is such a string, which is lexically and grammatically correct. Expressions are constructed from tokens. Tokens are such substrings from the initial expression, which cannot be broken into other tokens.

A lexically correct expression consists of the following set of tokens:

- the '(' and ')' characters, which can be used to change the operators privilege, and enclose the functions arguments.**Parenthesis**- the predefined operators, which you can use (+,-,*,\,<,> etc.)**Operator**- the ',' character**Comma**- this is a constant value embedded in the expression. It can be a number, boolean value or string.**Constant**- this is an identifier, which references a function**Function**- this is an identifier, which references a dynamic (variable) value.**Variable**

Each grammatically correct expression can be represented as a tree with items from one of the following types:

- these are integer and floating point number constants. Following are some correctly formatted numbers:**Numbers**

*=12*; =*0.1*; =*145.23*; =*7.5E-17*; =*8.234E+13*- these are the true and false boolean constants. Following are some correctly formatted boolean values:**Booleans**

*=true*; =*false*; =*True*; =*False*; =*TRUE*; =*FALSE*- these are substrings of the expression, which are enclosed in " (quote) characters. Following are some correctly formatted strings:**Strings**

="hello"; ="world"supported is a common set of binary and unary operators (+. -, *, \, ^, =, <, >, <=, >=, <> and &) (see Operators below).**Operators**-- the '(' and ')' characters can be used to change the operators precedence. For example:**Parenthesis**- evaluates to 60; =

=(10 + 20) * 2*10 + 20 * 2*- evaluates to 50- supported is a large set of mathematical, trigonometric, logical, text, date-time and bitwise functions. Function arguments must be enclosed in parenthesis and divided by commas (see Functions). For example:**Functions**

*=MIN(10, 20)*- evaluates to 10; =COS(0) - evaluates to 1.variables are valid identifiers to which a value is provided at runtime. Variables are automatically created for the values of the current scope recordset fields (see Variables).**Variables**-

The expression evaluation works with variants. The result of each expression evaluation is a variant too (e.g. a value of certain type - see Variants below).

You can use operators in expressions to perform arithmetic operations (+, -, *, / and ^) or logical comparisons (<, >, =, <=, >= and <>). You can also control the order of evaluation in a formula by enclosing expressions in parentheses. The ampersand (&) operator is used for the concatenation of character strings.

Arithmetic operators mainly perform operations on numbers, but can also be used for date-time/time-span arithmetics. The following table summarizes the arithmetic operators:

Syntax | Name | Description | Examples |
---|---|---|---|

+ | Unary plus | Does nothing - it is defined for completeness with the unary minus. Argument should be Number, TimeSpan, Array or Empty. Supports array chaining. |
+10 Returns: 10 |

- | Unary minus | Used to establish a number with a negative sign. Argument should be Number, TimeSpan, Array or Empty. Supports array chaining. |
-10 Returns: -10 -ARRAY(10, 12) Returns: ARRAY(-10, -12) |

^ | Exponentiation |
Raises arg1 to the power of arg2.
Both arguments should be numbers. |
10 ^ 2 Returns: 100 ARRAY(10, 12) ^ 2 Returns: ARRAY(100, 144) |

* | Multiplication | Multiplies arg1 by arg2. Both arguments should be numbers. If either one is empty the result is empty variant. Supports array chaining. |
10 * 2 Returns: 20 ARRAY(10, 12) * 2 Returns: ARRAY(20, 24) |

/ | Division |
Divides arg1 by arg2. |
10 / 2 Returns: 5 ARRAY(10, 12) / 2 Returns: ARRAY(5, 6) |

+ | Addition |
Adds arg2 to arg1. |
10 + 2 |

- | Subtraction | Subtracts arg2 from arg1. If first argument is Number, second argument must also be a Number. If first argument is DateTime, second argument must be a TimeSpan. If first argument is TimeSpan, second argument must be a TimeSpan. Supports array chaining. |
10 - 2 Returns: 8 ARRAY(10, 12) - 2 Returns: ARRAY(8, 10) ARRAY(10, 12) - ARRAY(12, 23) Returns: ARRAY(-2, -11) |

Comparison operators are used in logical statements to determine equality or difference between variables or values. Comparison operators always evaluate to true or false. The following table summarizes the comparison operators:

Syntax | Name | Description | Examples |
---|---|---|---|

> | Greater than | Returns true if arg1 is greater than arg2. Otherwise returns false. Supports array chaining. |
10 > 2 Returns: true |

< | Less than | Returns true if arg1 is less than arg2. Otherwise returns false. Supports array chaining. |
10 < 2 Returns: false |

>= | Greater than or equal to | Returns true if arg1 is greater than or equal to arg2. Otherwise returns false. Supports array chaining. |
10 >= 2 Returns: true |

<= | Less than or equal to | Returns true if arg1 is less than or equal to arg2. Otherwise returns false. Supports array chaining. |
10 <= 2 Returns: false |

= | Equal to | Returns true if arg1 is equal to arg2. Otherwise returns false. Supports array chaining. |
10 = 2 Returns: false |

<> | Not equal to | Returns true if arg1 is not equal to arg2. Otherwise returns false. Supports array chaining. |
10 <> 2 Returns: true |

Logical operators are used to determine the logic between variables or values.

Syntax | Name | Description | Examples |
---|---|---|---|

&& | Logical AND | Returns true if both arg1 and arg2 are true, otherwise returns false. Supports array chaining. |
true && false Returns: false |

|| | Logical OR | Returns true if either arg1 or arg2 is true, otherwise returns false. Supports array chaining. |
true || false Returns: true |

The Logical NOT is not implemented as an operator, but as a function (see Logical Functions for more info). |

The ampersand operator (&) is used to concatenate its arguments. You can use this operator to create new words and phrases. For example:

"hello" & " " & "world"

evaluates to: "hello world"

It is important to know that there is a priority assigned to each operator. If an expression contains several operators, which are not enclosed in parentheses, the operator with the highest priority is evaluated first. For example:

10 + 20 * 2

evaluates to 50, because the multiplication operator will be evaluated first (it has a higher priority than the addition operator).

(10 + 20) * 2

evaluates to 60, since the parentheses override the default way in which the expression is evaluated. The following table summarizes the operators priority:

Priority | Operators |
---|---|

1 | Unary plus (+); Unary minus (-) |

2 | Exponentiation (^) |

3 | Multiplication (*); Division (/) |

4 | Addition (+); Subtraction (-) |

5 | String concatenation (&) |

6 | Greater than (>); Greater than or equal to (>=); Less than (<); Less than or equal to (<=) |

7 | Equal (=); Not equal (<>) |

8 | Logical And (&&&) |

9 | Logical Or (||) |

Variants are value-type pairs, which facilitate the type conversion of values, as well as mathematical and logical operations with values of different type. The result of an expression evaluation is always a variant too (e.g. a value of certain type). When variants are consumed or used in functions or operators that require a value of a certain type, variants are automatically casted to a value of this type.

Variant casting however cannot be always performed - for example you cannot cast "hello world" to a double. In cases when automatic casting cannot be performed the pivot will raise an error. There are seven main types of variants:

**Empty**- an empty variant holds no value. It is equivalent to DBNull. Empty fields values are represented by empty variants.**Boolean**- holds a boolean (true/false) value. Boolean values can be converted to string. See Logical Functions.**Number**- holds a floating point or integer number, with some precision (for example an Int32 number variant holds an integer value in 32 bits). When performing arithmetic operations numbers automatically change their type to ensure that they can hold the resulting value. Casting floating point numbers to integer numbers removes the decimal portion of the value. Numbers can be converted to string and you can format numbers with the FORMAT function. See Mathematical Functions and Trigonometric Functions.**Text**- holds a text string. See Text Functions.**Date Time**- holds a date time value. Date-time variants can be converted to string and can be formatted with the FORMAT function. See Date Time Functions.**Time Span**- a time span value. Time-span variants can be converted to string (see Time Span Functions).**Array**- holds an array of arbitrary variant values (see Array Functions).

A set of Type Functions is available, which lets you manage variant types.