ReportWorkshop Overview | Template syntax | Expressions

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

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")'}

Syntax in $IF command

{$IF <expression text>}

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

About expressions

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.

Operators

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

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.

Operands

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)

Variables

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)

Data fields

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.

Cross-tab header fields

Example: #Category

Aggregate functions

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.

Functions

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,
Days)

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,
Seconds)

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.

Type conversion

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.

Expression result types

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.

Expression result format strings

Read the topic about format strings.