Expression Section
- Olena Tkachova
Analytics
The Expression Section allows set values for most report elements' properties. An expression can include data fields, constants, functions, report parameters, and property values of other controls. The Expression Section is presented by Expression Editor. All properties available in the Expression Section is set up by the Expression Editor. There are two ways to access the property of elements:
Select an element and than select the Property section on the right-side of element’s, the
symbol to open the Expression editor.
Select the Expression Section. In the Expression section select a required element from the drop-down list at the top of the section. At any property select the Ellipsis option to open Expression Editor. Any report element can be reached from the Expression Editor.
Expression Editor
The Expression Editor is a graphical interface that allows creating and editing expressions. The Editor lists all properties for which the expression can be applied. The properties are listed in the rigth-side bar.
The report items section includes Fields generated by Data Source and Parameters, series of Constraints may be applied to the fields, list of functiona and operators can be applied to the fileds and list of variables.
Expression Syntax
An expression string can consist of multiple lines that include constants, operators, function calls, fields or parameters, report items, and comments:
Iif (
[Code] == 'C',
True,
False
)
/*This expression is set for the Visible property of a control to show/hide the control based on the Code parameter value.*/
The following constans and operators is used in Expression Editor to constract expression
Constant | Description | Example |
|---|---|---|
String constants | Wrap string constants in apostrophes. If a string contains an apostrophe, double the apostrophe. | [Country] == ‘France’ [Name] == ‘O’’Neil’ |
Date-time constants | Wrap date-time constants in ‘#’ | [OrderDate] >= #2018-03-22 13:18:51.94944# |
True/False | The Boolean True value/ The Boolean False value | [InStock] == True/[InStock] == False |
Operator | Description | Example |
+ | Adds the value of one numeric expression to another or concatenates two strings | [UnitPrice] + 4 [FirstName] + ‘ ‘ + [LastName] |
- | Finds the difference between two operands | [Price1] - [Price2] |
* | Multiplies the value of two operands | [Quantity] * [UnitPrice] |
/ | Divides the first operand by the second. | [Quantity] / 2 |
% | Divides one numeric operand by the other and returns the remainder (modulus) | [Quantity] % 3 |
| | Performs a bitwise inclusive OR operation on two numeric expressions. Compares each bit of its first operand to the corresponding bit of its second operand. If either bit is 1, the corresponding resulting bit is set to 1. Otherwise, the corresponding resulting bit is set to 0. | [Number] | [Number] |
& | The bitwise AND operator. Compares each bit of its first operand to the corresponding bit of its second operand. If the two bits are 1, the corresponding resulting bit is set to 1. Otherwise, the corresponding resulting bit is set to 0. | [Number] & 10 |
^ | Performs a bitwise exclusive OR operation on two numeric expressions. | [Number] ^ [Number] |
== = | Returns True if both operands are equal; otherwise, it returns False. | [Quantity] == 10 |
!= | Returns True if the operands are not equal; otherwise, it returns False. | [Country] != ‘France’ |
< | Less than operator. Used to compare expressions. | [UnitPrice] < 20 |
<= | Less than or equal to operator. Used to compare expressions. | [UnitPrice] <= 20 |
>= | Greater than or equal to operator. Used to compare expressions. | [UnitPrice] >= 20 |
> | Greater than operator. Used to compare expressions. | [UnitPrice] > 20 |
In (,,,) | Tests for the existence of a property in an object. | [Country] In (‘USA’, ‘UK’, ‘Italy’) |
Between (,) | Specifies a range to test. Returns True if a value is greater than or equal to the first operand and less than or equal to the second operand. | [Quantity] Between (10, 20) |
And && | Performs a logical conjunction on two Boolean expressions. | [InStock] And ([ExtendedPrice]> 100) [InStock] && ([ExtendedPrice]> 100) |
Or || | Performs a logical disjunction on two Boolean expressions. | [Country]==’USA’ Or [Country]==’UK’ [Country]==’USA’ || [Country]==’UK’ |
~ | Performs a bitwise negation on a numeric expression. | ~[Roles] = 251 |
Not ! | Performs a logical negation on a Boolean expression. | Not [InStock] ![InStock] |
+ | Returns a numeric expression’s value (a unary operator). | +[Value] = 10 |
- | Returns the negative of a numeric expression’s value (a unary operator). | -[Value] = 20 |
Is Null | Returns True if an expression is a null reference (one that does not refer to any object). | [Region] is null |
Operator Precedence
When an expression contains multiple operators, these operators are evaluated in the following sequence:
Literal values
Parameters
Identifiers
OR (left-associative)
AND (left-associative)
The ‘.’ relationship qualifier (left-associative)
==, !=
<, >, <=, >=
-, + (left-associative)
*, /, % (left-associative)
NOT
Unary -
In
Iif
Trim(), Len(), Substring(), IsNull()
‘[]’ (for set-restriction)
‘()’
Group elements with parentheses to change operator precedence. For instance, operators are applied in the default order in the following expression:
Accounts[Amount == 2 + 48 * 2]
In the next expression, the addition operation is applied first, because its associated elements are grouped with parentheses, and the multiplication operation is applied last.
Accounts[Amount == (2 + 48) * 2]
Aggregate Functions
Name | Description |
|---|---|
Exists | Determines whether the object exists in the collection. |
Count | Returns the number of objects in the collection. |
Max | Returns the maximum expression value in the collection. |
Min | Returns the minimum expression value in the collection. |
Avg | Evaluates the average of the values in the collection. |
Sum | Returns the sum of all the expression values in the collection. |
Single | Returns a single object from the collection. The collection must contain no more than one object. |
Group Summary Functions
The group summary functions allows applying running summary to the group or pages in Report builder.
Name | Description |
|---|---|
sumAvg() | The sumAvg() function returns the average of a collection of numeric values in a specified group of the report. |
sumCount() | The sumCount() function returns the number of record of a collection of numeric values in a specified group of the report. |
sumSum() | The sumSum() function returns the sum of a collection of numeric values in a specified group of the report. |
sumMax() | The sumMax() function returns the max value of a collection of numeric values in a specified group of the report. |
sumMin() | The sumMin() function returns the min value of a collection of numeric values in a specified group of the report. |
SumMedian() | The sumMedian() function returns the the middle value of a collection of numeric values in a specified group of the report. |