<< Click to display table of contents >> ## ReportWorkshop Overview | Template syntax | Expressions |

See the topic about fields in templates for a general description of the syntax.

<expression> ::= [']=<expression text>['][ <result type>][ "<format>"]

An expression text (including the starting '=' character) may be enclosed in single quotes. These single quotes are necessary, if it contains space characters. To insert a single quote in a quoted expression, use a double single quote.

<result type> – see "expression results types" section below.

<format> – see "expression result format strings" section below.

Examples:

•{=Value+1}

•{=(x+ln(x))/2}

•{'=if(Score>=10,"win","try again")'}

{$IF <expression text>}

When used in $IF, expression text does not need single quotes.

An expression is a construction that returns a value.

Expressions allow calculating a numeric, text, date-time, or boolean (logical) values, and insert them in a report.

List of operators

Expressions can include binary operators listed in the next table.

Character |
Operator |

+ |
for numbers: addition for strings: concatenation |

- |
subtraction of numbers |

* |
multiplication of numbers |

/ |
division of numbers |

| |
logical OR |

& |
logical AND |

= or == |
equality |

<> or != |
inequality |

< |
less than |

<= |
less than or equal to |

> |
greater than |

>= |
greater than or equal to |

Expressions can include unary operators listed in the next table.

Character |
Operator |

+ |
sign identity |

- |
sign negation |

! |
logical negation |

Precedence

In complex expressions, rules of precedence determine the order in which operations are performed.

Precedence of operators:

•unary + - ! (highest)

•* /

•binary + -

•= == < > <= >= <> !=

•&

• | (lowest)

An operator with higher precedence is evaluated before an operator with lower precedence, while operators of equal precedence are evaluated from left to right (except for unary operators, which are evaluated from right to left).

You can use parentheses to override these precedence rules. An expression within parentheses is evaluated first, then treated as a single operand.

Example:

(A+B)*C

multiplies C times the sum of A and B.

Relational operators (such as = or <) are used to compare two operands.

Operands may be converted to another type before the comparison (see the section about type conversions below):

•if at least one of operands is a Boolean value, the second operand is converted to a Boolean value as well (e.g. {="true"=True()} returns True)

•otherwise, if one operand is text and another operand is numeric, text operand is converted to a number (e.g. {="1.1"=1.1} returns True)

•otherwise, operands are compared if they have compatible types.

If operands cannot be converted, or operands have incomparable types, an error occurs.

Strings are compared in lexicographical order, letter by letter, from left to right. The comparison is case sensitive.

For Boolean (logical) operands, True > False.

Addition and concatenation (+)

If one operand is text, another operand is converted to text, and the operator performs concatenation of strings.

The following values can be used as operands:

•numeric constants

•string constants

•variables

•cross-tab header fields

•aggregate functions

•other functions

Numeric constants

Examples: 12, 12.34, 12.34e56,12.34e+56, 12.34e-56.

The dot character is used as a decimal separator. 'E' or 'e' can be used to separate a mantissa and an exponent.

Note: if a string value is converted to a number, both dot and comma characters can be used as a decimal separator; but in numbers in an expression, only dot can be used.

String constants

Example: "Hello world!", "so called ""doctors""".

A string constant is enclosed in double quotes; to use a double quote in a string, insert it twice. If the expression includes strings containing space characters, the whole expression text must be enclosed in single quotes, e.g: {'=lower("Hello world!")'} (this rule is only for expression field types; the expression does not need to be quoted when used in $IF command)

Examples: %myvariable, '%my variable', or [%my variable].

Names of variables may be enclosed in single quotes or square brackets; it is necessary if they include space characters. If the expression includes variable names containing space characters, the whole expression text must be enclosed in single quotes, e.g: {'=[%my variable]+1'} or {'=''%my variable''+1'} (single quotes inside single quotes must be doubled) (this rule is only for expression field types; the expression does not need to be quoted when used in $IF command)

Only text values of variables are supported in expressions (graphic values are not supported)

Examples: Total, SalesTable:Total, :Total, ^:Total, 'Total sales', [SalesTable:Total sales].

Data fields may be enclosed in single quotes or square brackets; it is necessary if they include space characters in names. If the expression includes data fields containing space characters, the whole expression text must be enclosed in single quotes (single quotes inside single quotes must be doubled) (this rule is only for expression field types; the expression does not need to be quoted when used in $IF command).

Names of data fields in expressions must not be the same as function names. To make them distinct, you can use full names, or enclose them in single quotes or square brackets

Example (incorrect, there is len() function): {=len}

Correct: {=mytable:len+1} {=[len]+1} {'=''len''+1'} (a single quote before the opening '=' character is a quote around the whole expression text; so quotes around the field name are doubled)

Only text, numeric, boolean (logical), and date-time field values are supported.

Example: #Category

Example: Sum("Sales"), Min("Sales").

Arguments (field names) must be strings. Like other string constants, they must be enclosed in double quotes.

Other functions

Example: Lower("HELLO"), MakeTime(Hour, Minutes, Seconds), Sqrt(2).

Functions are discussed in the next section of this topic.

Function names are case insensitive.

Basic math functions

Function |
Meaning |

Div(X,Y) |
Calculates integer division of X by Y |

Mod(X,Y) |
Returns the reminder of integer division of X by Y |

Round(X,Digits) |
Rounds X to Digits number of digits. Examples: •Round(1.234, 2) = 1.23 •Round(123.4, -2) = 100 •Round(2.5, 0) = 3 (integer value) The function works by rounding numbers 1-4 down, and rounding numbers 5-9 up. |

Ceiling(X) |
Return the smallest integer value that is greater than or equal to X. |

Floor(X) |
Returns the largest integer value that is smaller than or equal to X. |

Trunc(X) |
Rounds X toward zero. |

Abs(X) |
Returns the absolute value of X |

Sign(X) |
Returns: •0, if X is zero. •1, if X is greater than zero. •-1, if X is less than zero. |

Trigonometric functions

Function |
Meaning |

Sin(X) |
The sine of X (in radians) |

Cos(X) |
The cosine of X (in radians) |

Tan(X) |
The tangent of X (in radians) |

Cotan(X) |
The cotangent of X (in radians) |

Radians(X) |
Converts X (in degrees) to radians |

Degrees(X) |
Converts X (in radians) to degrees |

Pi() |
π (approx. 3.14159) |

Logarithms and exponents

Function |
Meaning |

Log(X,Base) |
Returns the logarithm base Base of X logBaseX |

Log10(X) |
Returns the logarithm base 10 of X log10X |

Ln(X) |
Returns the natural logarithm of X lnX = logeX |

Exp(X) |
Returns the exponential of X eX |

Power(Base,Exponent) |
Raises Base to Exponent power BaseExponent |

Sqrt(X) |
Returns the square root of X √x |

Min and max

See the section about relational operators above for information about comparisons.

Report Workshop uses "Min" and "Max" names for aggregate functions, so it uses "MinVal" and "MaxVal" names for functions that compare two operands.

Function |
Meaning |

MinVal(X,Y) |
Returns the lesser of X and Y |

MaxVall(X,Y) |
Returns the greater of X and Y |

Text functions

Function |
Meaning |

Upper(S) |
Converts S to upper case |

Lower(S) |
Converts S to lower case |

Trim(S) |
Trims leading and trailing spaces and control characters from S |

LTrim(S) |
Trims leading spaces and control characters from S |

RTrim(S) |
Trims trailing spaces and control characters from S |

Substring(S,Index,Count) |
Returns a substring of S containing Count characters starting from the Index-th character. Index of the first character is 1. |

Left(S,Count) |
Returns a substring of S containing first Count characters |

Right(S, Count) |
Returns a substring of S containing last Count characters |

Repeat(S,Count) |
Repeats the string S Count times |

Len(S) |
Returns the count of characters in S |

Date and time functions

Date-time values may contain a date, a time, or both date and time.

Function |
Meaning |

Now() |
Returns the current date and time |

CurDate() |
Returns the current date |

CurTime() |
Returns the current time |

MakeDate(Year,Days) |
Returns a date based on Year and a number of days Days. The first day in a year is 1. |

DateFromParts(Year,Month, |
Returns a date based on Year, Month, and Days. Month is from 1 to 12, Days is from 1 to 28 or 31, depending on the month. |

MakeTime(Hour,Minutes, |
Returns a time based on the specified Hour, Minutes, and Seconds |

GetDay(Date) |
Returns the day of the month (from 1 to 31) of the specified Date |

GetMonth(Date) |
Returns the month (from 1 to 12) of the specified Date |

GetYear(Date) |
Returns the year of the specified Date |

GetHour(Time) |
Returns the hour of day (from 0 to 23) of Time |

GetMinutes(Time) |
Returns the minutes (from 0 to 59) of Time |

GetSeconds(Time) |
Returns the seconds (from 0 to 59) of Time |

Logical functions

See the section about conversions below.

Function |
Meaning |

False() |
Returns False |

True() |
Returns True |

If(Condition,A,B) |
If Condition is True, returns A. If Condition is False, returns B. This function is especially useful in tags of items, hints and checkpoint names (where $IF command is not available) |

Conversion functions

See the section about conversions below.

Function |
Meaning |

ToText(X) |
Converts X to a text string |

ToNumber(X) |
Converts X to a number (the same as unary + operator) |

Data functions

Function |
Meaning |

RecNo(Depth) |
Returns a number of the currently processed record. Records are counted from 1. Depth = 0 means the most nested query, 1 means the query containing it, and so on. |

Types of values may be converted explicitly (using ToText() and ToNumber() functions) or implicitly (when a function or an operator needs a value of another type).

Conversion to boolean values (True or False)

Text to boolean values:

Values evaluated as True |
Values evaluated as False |

"t" "y" "true" "yes" "on" "1" Texts.TrueText |
"f" "n" "false" "no" "off" "0" Texts.FalseText |

Number to boolean values: any non-zero value is converted to True, zero is converted to False.

Conversion to numbers

Boolean values to numbers: True is converted to 1, False is converted to 0.

Strings to numbers: both dot and comma characters can be used as a decimal separator.

The expression may return values of the following types:

•text

•integer value

•floating point value

•boolean

•date and time

You can override the field type by specifying <result type>, read the topic about specifying data field types.

Read the topic about format strings.