Nevron Vision for SharePoint Documentation
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:

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

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).

Operators

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.
If either one is empty the result is empty variant.
Supports array chaining.

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.
Both arguments should be numbers.
If either one is empty the result is empty variant.
Supports array chaining.

10 / 2
Returns: 5

ARRAY(10, 12) / 2
Returns: ARRAY(5, 6)
+ Addition

Adds arg2 to arg1.
If either argument is a String, performs string concatenation.
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 or DateTime.
Supports array chaining.

10 + 2
Returns: 12

ARRAY(10, 12) + 2
Returns: ARRAY(12, 14)

ARRAY(10, 12) + ARRAY(12, 23)
Returns: ARRAY(22, 35)

10 + "Nevron"
Returns: "10Nevron"

- 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

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:

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

 

 


©2016. Nevron Software LLC.

Send Feedback