|
<< Click to display table of contents >> 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>}
{$SET %variable TO <expression text>} or {$SET %variable = <expression text>}
When used in $IF and $SET, 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.
Expressions are used:
•in fields, to insert a result in a report
•in $if command as a condition
•in $iset command to assign a result to a variable
•in $if and $set commands in scripts.
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):
•empty (NULL) value is less than any other value
•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 |
UpperFirst(S) |
Converts the first letter of S to upper case (note: the count of characters may be changed, if the first letter is a ligature) |
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 |
GetDayOfWeek(Date) |
Returns the day of week (from 1 to 7, where 1 is Monday) of Date |
GetWeekOfMonth(Date) |
Returns a number (from 1 to 5) indicating which week of the month the date Date falls in |
MonthName(Month) |
Returns a name of the Month (where Month is a number from 1 to 12)* |
MonthShortName(Month) |
Returns a shortened name of the Month (where Month is a number from 1 to 12)* |
DayOfWeekName(DayOfWeek) |
Returns a name of the DayOfWeek (where DayOfWeek is a number from 1 to 7, 1 means Monday)* |
DayOfWeekShortName( |
Returns a shortened name of the DayOfWeek (where DayOfWeek is a number from 1 to 7, 1 means Monday)* |
* names are returned in a system default language
For empty (NULL) date-time parameters, the functions above return the empty (NULL) value.
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). |
Note: both A and B parameters are calculated before If() calculation. This means that If(y=0, 0, x/y) produces "division by 0" error if y = 0.
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) |
Conversion functions, numbers to words
See the section about number to words conversion below.
Function |
Meaning |
SpellNumber(Value, Language, Options) |
Converts the integer Value to text in the specified Language |
SpellCurrency(Value, Language, Currency, Options) |
Converts the monetary Value to text in the specified Language |
Function |
Meaning |
Defined(Value) |
Returns True if Value is not empty, returns False otherwise. Empty (NULL) values may come from empty database field |
RecNo(Depth) |
Returns a index 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. |
Query(DataQuery,Format, |
Executes the DataQuery and returns the result. See the section about Query() function below. |
Example:
•If(Defined(Date),Date,"undefined date") returns the value of Date if it is not NULL, and "undefined date" text otherwise.
Custom functions
Programmers can add their own functions, see the topic explaining how to extend Report Workshop.
The following functions are implemented as an example.
Function |
Meaning |
Char(Code) |
Returns the character for the given UTF-32 Code. (this function becomes available if you include RVReportCharCodeCalculator unit in your project) |
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.
Empty (NULL) value is converted to False.
Conversion to numbers
Boolean values to numbers: True is converted to 1, False is converted to 0.
Empty (NULL) value is converted to 0.
Strings to numbers: both dot and comma characters can be used as a decimal separator.
Example:
=1+3+"x"
returns "4x"
The first operation is 1+3 (sum of integer values), the second operation is 4+"x" (concatenation of strings, 4 is converted to "4").
SpellNumber() returns a text representation of an integer numeric value.
SpellCurrency() returns a text representation of a monetary value.
Language
The following values of Language parameter are supported:
Language |
Meaning |
•"ru" or •any string starting from "ru-" |
Russian |
•"pt" or •"pt-br" |
Brazilian Portuguese |
•"pt-pt" |
European Portuguese |
•any other string |
English |
The language string is case-insensitive.
SpellNumber() parameters
Options is a string that may contain the following characters:
Options character |
Meaning |
"m" or "f" or "n" |
Grammatical gender: male/female/neuter (male is assumed, if none is specified) |
"$" |
If included, monetary spelling rules are applied |
The Options string is case-insensitive.
SpellCurrency() parameters
Options is a string that may contain the following characters:
Options character |
Meaning |
"0" |
If included, if fractional part of Value is zero (i.e. 0 cents), it is dropped. |
"#" |
If included, a fractional part of Value (i.e. cents) is written using digits instead of words. |
The Options string is case-insensitive.
Options is a string that may contain the following characters:
Currency |
Meaning |
"usd" or "$" |
United States dollar |
"eur" or "euro" or "€" |
Euro |
"brl" or "r$" |
Brazilian real |
"rub" or "rur" or "sur" |
Russian ruble |
any other string |
Default currency |
The Currency string is case-insensitive.
The default currency depends on the Language:
Language |
Default currency |
English |
United States dollar |
Russian |
Russian ruble |
Brazilian Portuguese |
Brazilian real |
European Portuguese |
Euro |
Examples:
•SpellNumber(-10, "en", "") returns "minus ten"
•SpellCurrency(1.2, "en", "usd", "") returns "one dollar and twenty cents"
Query(DataQuery,Format,Delimiter) executes DataQuery specified in the parameter. Each record of the result is applied to Format string, so processed Format is repeated record count times. Delimiters are inserted between.
For example, let we have a data query "select * from FruitTable" that returns 3 records with "apple", "orange", "peach" in "FruitName" field.
The call Query("select * from FruitTable", "{FruitName}", ", ") returns the string "apple, orange, peach".
Please note that when inserted in a field:
•"}" character must be doubled, otherwise it will be treated as a field end;
•since there are space characters, the whole field must be in single quotes.
We have: {'=Query("select * from FruitTable", "{FruitName}}", ", ")'}.
The following rules are applied to NULL values:
•any date-time function returns NULL for NULL parameter
•when used as a number, NULL is converted to 0
•when used as a Boolean value, NULL is converted to False
•when used as a string value, NULL is converted to empty string
•when comparing, NULL is less than any other value.
The expression may return values of the following types:
•text
•integer value
•floating point value
•boolean
•date and time
•empty value (NULL)
You can override the field type by specifying <result type>, read the topic about specifying data field types.
Read the topic about format strings.