Reportizer Documentation
Contents Index

Functions Available in Report Expressions

 Top Previous Next

Here, the report expression engine operators and functions are described.

Notes

The expressions and functions are calculated "on the fly" before they are outputted to target document.
Names of functions and operators are case insensitive.
Parameters of parameterized functions may be constants (literals) or expressions.
String literals must be enclosed in single quotes. If a literal includes quote marks, each of them must be doubled.
Multi-line string literals are not allowed. If you want to pass big text as parameter, you can split it on less separate string literals and concatenate them using + operator.
Parameters of parameterized functions are strongly typed.

Incorrect

Incorrect expression dataset_field_val(1, Cust_Name)

Correct

Incorrect expression dataset_field_val(1, 'Cust_Name')

Explanation: In this example, a database field name is a parameter of String (Text) type, so it should be enclosed in single quotes.

Expression must return only one value.

Incorrect

Incorrect expression Book title: dataset_field_val(1, 'TITLE')

Correct

Incorrect expression 'Book title: ' + dataset_field_val(1, 'TITLE')

Incorrect expression Book title: <<<dataset_field_val(1, 'TITLE')>>>

Explanation: To produce one value, the number of operands in a String (Text) expression must be equal to the number of operators plus one. In other words, there should be an operator between each two operands in a valid String expression.

Another solution is to separate dynamic parts of the expression. In reports context, dynamic parts begin with three opening triangle brackets (<<<) and end with three closing triangle brackets (>>>).

Operators

Arithmetic Operators

Work with numbers and return numbers (except + operator, which can be used for string concatenation).

+ Addition of numbers. Can be also used for concatenating string operands (as an alternative for concat function).
- Substraction of numbers
* Multiplication of numbers
/ Division of numbers
^ Raises left operand to power, specified by the right operand (as an alternative for power function)
div Integer division. It is division in which the fractional part (remainder) is discarded.
mod Remainder of integers. It divides two numbers and returns only the remainder.

Logical Operators

Work with boolean constants or expressions of any kind, which return a boolean value, and return boolean True or False.

not Logical NOT. Returns False if its single operand can be converted to True; otherwise, returns True.
and Logical AND. Returns True if both operands are True; otherwise, returns False.
or Logical OR. Returns True if either operand is True; if both are False, returns False.
xor Exclusive OR. Returns True if one operand is True and the other is False; otherwise, returns False.
= Equality
<> Inequality
> Greater than
>= Greater than or equal
< Less than
<= Less than or equal

Functions

Use functions to calculate values. Each function returns one value. Function parameters can be constants or expressions (including recursive function calls), unless otherwise noted below.

Database Related Functions

General

dataset_row_number(
  StepNo Integer, 
  GroupLevel Integer 
): Integer
dataset_group_number(
  StepNo Integer, 
  GroupLevel Integer 
): Integer
dataset_field_val(
  StepNo Integer,
  FieldName String | FieldIndex Integer
): <FieldType>
dataset_field_hex_val(
  StepNo Integer,
  FieldName String | FieldIndex Integer
): String
dataset_field_is_null(
  StepNo Integer,
  FieldName String | FieldIndex Integer
): Boolean
dataset_nvl(
  StepNo Integer,
  FieldName String | FieldIndex Integer,
  SubstVal <FieldType>
): <FieldType>
dataset_field_exists(
  StepNo Integer,
  FieldName String
): Boolean
dataset_param_val(
  StepNo Integer,
  ParamName String
): <ParamType>
query_res(
  StepNo Integer,
  SqlText String
): <FieldType>

Statistical (Aggregate)

These functions perform calculation on current dataset data. Unlike other functions, statistical functions accumulate data of each record of dataset field, therefore they need more resources and their using (especially for conditional functions) may slow down processing for large datasets.

It is strongly recommended to use these functions only inside or after loop(s) through dataset rows, otherwise they will return inadequate values.

Standard (similar to aggregate functions in SQL)

dataset_row_count(
  StepNo Integer,
  GroupLevel Integer
): Integer
dataset_min(
  StepNo Integer,
  GroupLevel Integer,
  FieldName String
): Numeric
dataset_max(
  StepNo Integer,
  GroupLevel Integer,
  FieldName String
): Numeric
dataset_sum(
  StepNo Integer,
  GroupLevel Integer,
  FieldName String
): Numeric
dataset_avg(
  StepNo Integer,
  GroupLevel Integer,
  FieldName String
): Numeric
dataset_count(
  StepNo Integer,
  GroupLevel Integer,
  FieldName String
): Integer

Conditional

These functions work like standard statistical functions, but with one important difference: they check Condition for each row, and calculate only if it evaluates to True. It is important to write the Condition parameter in these functions as String constant (i.e. enclosed in single quotes) or a string deterministic expression (see below).

The parameters of these functions must be constants or deterministic expressions (i.e. return the same result any time they are called with a specific set of input values). The Condition parameter can use also nondeterministic expressions, but, as noted above, it should be written as string literal or string deterministic expression.

dataset_row_count_ex(
  StepNo Integer,
  GroupLevel Integer,
  Condition String
): Integer
dataset_min_ex(
  StepNo Integer,
  GroupLevel Integer,
  FieldName String,
  Condition String
): Numeric
dataset_max_ex(
  StepNo Integer,
  GroupLevel Integer,
  FieldName String,
  Condition String
): Numeric
dataset_sum_ex(
  StepNo Integer,
  GroupLevel Integer,
  FieldName String,
  Condition String
): Numeric
dataset_avg_ex(
  StepNo Integer,
  GroupLevel Integer,
  FieldName String,
  Condition String
): Numeric
dataset_count_ex(
  StepNo Integer,
  GroupLevel Integer,
  FieldName String,
  Condition String
): Integer
It is not recommended to use nested calls of statistical functions (i.e. when Condition contains statistical functions calls), because the application cannot guarantee the correct result of such calls.
But if you do this, try to avoid cyclic field references in these functions because this may cause unexpected results of expressions. For example, using FieldName inside Condition is a cyclic reference and should be avoided.

Examples of correct using of conditional statistical functions:

dataset_sum_ex(1, 0, 'PaymentSum', 'dataset_field_val(1, ''CustNo'') > 1000')

(this expression will sum data from PaymentSum field, when value of numeric field CustNo is larger then 1000)

dataset_count_ex(1, 0, 'CustNo', 'dataset_field_val(1, ''Paid'')')

(this expression will return count of values from field CustNo, when value of boolean field Paid is True)

dataset_sum_ex(1, 0, 'BillSum', 'dataset_sum_ex(1, 0, ''OldBillSum'', ''dataset_field_val(1, ''''CustNo'''') > 0'') > 0')

(this function will return sum of values from field BillSum, when conditional sum of field OldBillSum is larger than 0)

Examples of incorrect using of conditional statistical functions:

dataset_count_ex(1, 0, 'CustNo', 'yes')

(error: not boolean constant cannot be used as condition)

dataset_sum_ex(1, 0, 'BillSum', 'dataset_sum_ex(1, 0, ''OldBillSum'', ''dataset_sum_ex(1, 0, ''''BillSum'''', True) > 0'') > 0')

(error: cyclic reference for field BillSum)

Mathematical Functions

abs(
  x Numeric
): Numeric
frac(
  x Numeric
): Numeric
int(
  x Numeric
): Numeric
round(
  x Numeric
): Integer
sqrt(
  x Numeric
): Numeric
power(
  x Numeric,
  y Numeric
): Numeric
exp(
  x Numeric
): Numeric
ln(
  x Numeric
): Numeric
cos(
  x Numeric
): Numeric
sin(
  x Numeric
): Numeric
tan(
  x Numeric
): Numeric
atan(
  x Numeric
): Numeric

Date and Time Functions

date: DateTime
time: DateTime
add_date_time(
  x DateTime,
  y Numeric
): DateTime
compare_date_time(
  Date1 DateTime,
  Date2 DateTime
): Integer
date_time_diff(
  Date1 DateTime,
  Date2 DateTime
): Numeric
format_date_time(
  x DateTime,
  Mask String
): String

File Related Functions

file_created(
  FileName String
): DateTime
file_last_modified(
  FileName String
): DateTime
file_last_accessed(
  FileName String
): DateTime
file_size(
  FileName String
): Numeric
file_version(
  FileName String
): String
extract_file_ext(
  FileName String
): String
extract_file_name(
  FileName String
): String
extract_file_dir(
  FileName String
): String
extract_file_path(
  FileName String
): String
extract_file_text(
  FileName String
): String
file_exists(
  FileName String
): Boolean
dir_exists(
  DirectoryName String
): Boolean

Miscellaneous Functions

capitalize(
  Str String,
): String
char(
  x Integer
): String
concat(
  Str1 String,
  Str2 String
): String
format_float(
  x Numeric,
  Mask String
): String
iif(
  Condition Boolean,
  x <AnyType>,
  y <AnyType>
): <AnyType>
length(
  Str String,
): Integer
lower(
  Str String,
): String
lpad(
  Str String,
  Count Numeric,
  Char String
): String
number_to_words(
  x Numeric,
  Language String,
  Options String
): String
ordinal_number(
  x Integer,
  Language String,
  Case String,
  Gender String
): String
parse(
  x <AnyType>
): <AnyType>
pos(
  SubStr String,
  Str String
): Integer
pretty(
  Str String,
): String
quantitative_numeral(
  x Integer,
  Language String,
  Case String,
  Gender String
): String
rgb(
  Red Integer,
  Green Integer,
  Blue Integer
): String
rpad(
  Str String,
  Count Numeric,
  Char String
): String
string_replace(
  Str String,
  SubStr String,
  NewSubStr String
): String
substr(
  Str String,
  Index Integer,
  Count Integer
): String
substr_count(
  Str String,
  SubStr String
): Integer
to_number(
  <AnyType> String
): Numeric
to_string(
  <AnyType> String
): String
trim(
  Str String,
): String
trim_left(
  Str String,
): String
trim_right(
  Str String,
): String
upper(
  Str String,
): String

Report Specific Functions

report_author: String
report_changed_by: String
report_database_name: String
report_dataset_name: String
report_description: String
report_file_name: String
page_left_margin: Integer
page_right_margin: Integer
page_top_margin: Integer
page_bottom_margin: Integer
page_number: Integer
page_count: Integer
page_height: Integer
page_width: Integer
internal_property(
  PropertyName String
): String

Obsolete Report Functions

GROUPRECORDNUMBER This function is no longer supported. Use dataset_row_number instead
NUMTOWORDS This function is no longer supported. Use number_to_words instead
DATASETNAME Use report_dataset_name instead
DATABASENAME Use report_database_name instead
FILENAME Use report_file_name instead
PAGEHEIGHT Use page_height instead
PAGEWIDTH Use page_width instead
LEFTMARGIN Use page_left_margin instead
RIGHTMARGIN Use page_right_margin instead
TOPMARGIN Use page_top_margin instead
BOTTOMMARGIN Use page_bottom_margin instead
PAGENUMBER Use page_number instead
RECORDNUMBER Use dataset_row_number instead
PAGERECORDNUMBER Use dataset_row_number instead
GROUP1RECORDNUMBER Use dataset_row_number instead
GROUP2RECORDNUMBER Use dataset_row_number instead
GROUP3RECORDNUMBER Use dataset_row_number instead
RECORDCOUNT Use record_count instead
REPORTDESCRIPTION Use report_description instead
REPORTAUTHOR Use report_author instead
PAGECOUNT Use page_count instead
MIN Use dataset_min instead
MAX Use dataset_max instead
SUM Use dataset_sum instead
AVERAGE Use dataset_avg instead
COUNT Use dataset_count instead
MINEX Use dataset_min_ex instead
MAXEX Use dataset_max_ex instead
SUMEX Use dataset_sum_ex instead
AVERAGEEX Use dataset_avg_ex instead
COUNTEX Use dataset_count_ex instead
FILESIZE Use file_size instead
FIELDVAL Use dataset_field_val instead
PARAMVAL Use dataset_param_val instead
ISNULL Use dataset_is_null instead
FIELDEXISTS Use dataset_field_exists instead
NVL Use dataset_nvl instead
QUERYRES Use query_res instead
STR Use to_string instead
TRIMLEFT Use trim_left instead
TRIMRIGHT Use trim_right instead
ORDINALNUMBER Use ordinal_number instead
QUANTITATIVENUMERAL Use quantitative_numeral instead
FILECREATED Use file_created instead
FILELASTMODIFIED Use file_last_modified instead
FILELASTACCESSED Use file_last_accessed instead
EXTRACTFILENAME Use extract_file_name instead
EXTRACTFILEEXT Use extract_file_ext instead
EXTRACTFILEDIR Use extract_file_dir instead
EXTRACTFILEPATH Use extract_file_path instead
REPLACE Use string_replace instead
ADDDATETIME Use add_date_time instead
COMPAREDATETIME Use compare_date_time instead
DATETIMEDIFF Use date_time_diff instead
FORMATDATETIME Use format_date_time instead
SUBSTRCOUNT Use substr_count instead