Database Tour Documentation Contents Index

Functions Available in Report Expressions

Top Previous Next

Here functions for Database Tour Pro report expressions are described.

Note

All functions are calculated just before their parent expressions are displayed in report.

Names of functions are case sensitive and must be entered in upper case.

Parameters of parameterized functions may be constants or expressions.

Statistical (aggregate)

These functions calculate data of report dataset fields. Unlike other functions, statistical functions accumulate data of each record of report dataset field, therefore they need more resources and their using (especially conditional functions) may slow up generating report for large datasets. For Expression objects, result of statistical functions depends on Aggregation Range property.

Standard (similar to aggregate functions in SQL)

MIN(x)Returns the lowest value of all records in field x
MAX(x)Returns the highest value of all records in field x
SUM(x)Sums values of all records in field x
COUNT(x)Returns count of all non-empty values of all records in field x
AVERAGE(x)Returns the average value of all records in field x

Conditional

These functions do the same as standard statistical functions, but with one important difference: they check condition y for each record, and, if this condition returns Boolean True or a numeric value, which is larger then 0, the data is taken into calculations. Condition y can be any valid numeric or Boolean expression.

MINEX(x, y)
MAXEX(x, y)
SUMEX(x, y)
COUNTEX(x, y)
AVERAGEEX(x, y)

Note

Try to avoid cyclic field references in these functions because this may cause unexpected results of expressions. For example, using field x inside condition y is a cyclic reference and should be avoided.

Examples of valid using conditional statistical functions:

SUMEX(PaymentSum, FIELDVAL(CustNo) * 1000)

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

COUNTEX(CustNo, FIELDVAL(Paid))

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

SUMEX(BillSum, SUMEX(OldBillSum, FIELDVAL(CustNo)))

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

Examples of invalid using conditional statistical functions:

COUNTEX(CustNo, 'yes')

(error: string constant cannot be used as condition)

SUMEX(BillSum, SUMEX(OldBillSum, SUMEX(BillSum, 1000)))

(error: cyclic reference for field BillSum)

Mathematical

ABS(x)Returns an absolute value of numeric x
FRAC(x)Returns fractional part of numeric xx
INT(x)Returns the integer part of numeric x
ROUND(x)Returns the value of x rounded to the nearest whole number
SQRT(x)Returns square root of numeric x
POWER(x, y)Raises base x to the power y
EXP(x)Returns the exponential of x
LOG(x)Returns the natural (base e) logarithm of x
COS(x)Returns the cosine of x, where x is given in radians
SIN(x)Returns the sine of x, where x is given in radians
TAN(x)Returns the sine of x, where x is given in radians
ATAN(x)Returns the arc tangent of x, that is, the value whose tangent is x

Date and time

DATEReturns current date
TIMEReturns current time
ADDDATETIME(x, y)Adds y days (as floating-point number) to date/time x
COMPAREDATETIME(x, y)Returns an integer indicating the result of comparison of the date/time x and date/time y: 0 when x = y, 1 when x > y, and -1 when x < y
DATETIMEDIFF(x, y)Returns difference in days (as floating-point number) between date/time x and date/time y
FORMATDATETIME(x, y)Formats a date and time value x using mask y. Mask may be any valid mask for date or time like those ones, which are used in Mask property of Expression objects. For example, to display only current month, use DATE as x and m or mmmm as mask y.

File related

FILECREATED(x)Returns the date and time the file x was created. x should be full file path.
FILELASTMODIFIED(x)Returns the date and time the file x was last modified. x should be full file path.
FILELASTACCESSED(x)Returns the date and time the file x was last accessed. x should be full file path.
FILESIZE(x)Returns the size of the file x in bytes. x should be full file path.
EXTRACTFILEEXT(x)Extracts the extension of the file x (with period)
EXTRACTFILENAME(x)Extracts file name from the full file path x
EXTRACTFILEDIR(x)Extracts file path (without \ character at the end) from the full file path x
EXTRACTFILEPATH(x)Extracts file path (with \ character at the end) from the full file path x

Miscelanious

FIELDVAL(x)Returns value of field x
PARAMVAL(x)Returns value of query parameter x (if report dataset is a parameterized query)
ISNULL(x)Returns True, when value of field x is blank (Null value), and False in other cases
NVL(x, y)Returns y, when value of field x is null, and the field value in another case
IIF(x, y, z)Returns y if condition x evaluates to True, or z in another case
STR(x)Converts x to a string
UPPER(x)Converts a string x to upper case
LOWER(x)Converts a string x to lower case
CAPITALIZE(x)Converts first characters of every word in a string x to upper case
PRETTY(x)Makes the first character of string x in upper case and the rest in lower case
LENGTH(x)Returns length (count of characters) of a string x
POS(x, y)Returns position of substring x in string y
SUBSTR(x, y, z)Returns a substring of a string x, containing z characters starting at y position
SUBSTRCOUNT(x, y)Returns the number of times the substring y occurs in string x
TRIM(x)Trims leading and trailing spaces and control characters from a string x
TRIMLEFT(x)Trims leading spaces and control characters from a string x
TRIMRIGHT(x)Trims trailing spaces and control characters from a string x
NUMTOWORDS(x, y)Converts number y to its representation in words. x is a mask for output. In the mask, symbol N replaces an integer part of the number in words, and figure 0 represents one digit from a fractional part of the number. For example, NUMTOWORDS('N dollars 00 cents', 12.34) returns twelve dollars 34 cents.
ORDINALNUMBER(x)Returns the original number and the ordinal suffix of integer x
UNQUOTE(x)Converts text x to unquoted text
REPLACE(x, y, z)Replaces all occurancies of substring y with substring z in text x
CONCAT(x, y)Returns a string that is the result of concatenating string x to string y
QUERYRES(x)Returns result of the one-field query, specified by SQL text x

Functions without parameters

DATABASENAMEReturns database name of the report data source
DATASETNAMEReturns dataset name (table name or query SQL) of the report data source
FILENAMEReturns path and file name of the report template
PAGENUMBERReturns number of current page
PAGECOUNTReturns count of pages in the report. This function requires running report in double-pass mode and may slow up large reports. It should be used only in expressions, where the result of the expression is shown in report (in Expression objects).
RECORDNUMBERReturns number of current record
GROUPRECORDNUMBERReturns number of current record in current group
PAGERECORDNUMBERReturns number of current record in current page
RECORDCOUNTReturns record count of report dataset
REPORTDESCRIPTIONReturns the report description
REPORTAUTHORReturns the report author's name
PAGEHEIGHTReturns page height in pixels
PAGEWIDTHReturns page width in pixels
LEFTMARGINReturns left margin in pixels
RIGHTMARGINReturns right margin in pixels
TOPMARGINReturns top margin in pixels
BOTTOMMARGINReturns bottom margin in pixels