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 nonempty 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 DATE  Returns
current date  TIME  Returns
current time  ADDDATETIME(x,
y)  Adds y days (as floatingpoint 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 floatingpoint
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 onefield query, specified by SQL text x 
Functions
without parameters DATABASENAME  Returns
database name of the report data source  DATASETNAME  Returns
dataset name (table name or query SQL) of the report data source  FILENAME  Returns
path and file name of the report template  PAGENUMBER  Returns
number of current page  PAGECOUNT  Returns
count of pages in the report. This function requires running report in doublepass
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).  RECORDNUMBER  Returns
number of current record  GROUPRECORDNUMBER  Returns
number of current record in current group  PAGERECORDNUMBER  Returns
number of current record in current page  RECORDCOUNT  Returns
record count of report dataset  REPORTDESCRIPTION  Returns
the report description  REPORTAUTHOR  Returns
the report author's name  PAGEHEIGHT  Returns
page height in pixels  PAGEWIDTH  Returns
page width in pixels  LEFTMARGIN  Returns
left margin in pixels  RIGHTMARGIN  Returns
right margin in pixels  TOPMARGIN  Returns
top margin in pixels  BOTTOMMARGIN  Returns
bottom margin in pixels 
