BackPrevious Topic Next TopicNext

Appendix 1: Formula Functions

Logi Report provides the following types of the built-in formula functions: Array, Date Range, Date/Time, Financial, Math, String, and Others. This topic describes the usage of each function with examples.

Array

You can use the Array functions to summarize field data in several different ways.

Function Description Argument Return Value Example
Any()
  • Any(array_variable);
  • Any(field_variable);
This function is used to check whether there is at least one element in the argument is true. If there is, return true; if there isn't, return false.
  • array_variable - Refers to an array.
  • field_variable - A special variable in formula. It refers to a group of values of a database field or of a formula field.
A Boolean value.
  • Boolean x = Any([false, false, false, true, false, false])
  • Boolean x = Any(@isExcellent)
  • Boolean x = Any(@formula) (@formula is a formula which returns a Boolean array.)
  • Boolean x = Any(@@ctf1) (@@ctf1 is a crosstab formula which returns a Boolean array.)
Average()
  • Average(array_variable);
  • Average(field_variable);
  • Average(field_variable, groupby);
This function is used to return the average value of a group of values referred to by the first argument. The second argument, if there is one, returns the group-by field name.
  • array_variable - Refers to an array whose element is of the Number or Currency value.
  • field_variable  - A special variable in formula. It refers to a group of values of a database field or of a formula field. Whatever source they come from, database or formula, they should be of Number or Currency data type.
  • groupby - Can be a constant String which indicates the group-by field name or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Note: If a field_variable is assigned to a variable, for example X, the variable X will lose the characteristic of representing a group of values. The following formula will then be treated as incorrect.

Number x = @dbfield;
Number y = Average(x); // system will prompt you that there is no such kind of function.

The result type relies on the first argument. It can be of the Number or Currency data type, if the data type of each element in the first argument is also of the Number or Currency data type respectively.
  • Number x = Average([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
  • Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0]; Number y = Average(x)
  • Currency x = Average([$1, $2, $3.0, $4.5, $45, $67])
  • Currency x[] = [$1, $2, $3.0, $4.5, $45, $67]; Currency y = Average(x)
  • Number x = Average(@dbfield)
  • Number x = Average(@formula)
  • Number x = Average(@dbfield, "group_field")
  • Number x = Average(@dbfield, @"group_field")
  • Number x = Average(@dbfield, @parameter)
  • Currency x = Average(@dbfield)
  • Currency x = Average(@formula)
  • Currency x = Average(@formula, "group_field")
  • Currency x = Average(@formula, @"group_field")
  • Currency x = Average(@formula, @parameter)
Count()
  • Count(array_variable);
  • Count(field_variable);
  • Count(field_variable, groupby);
This function is used to count the number of a group of values referred to by the first argument. The second argument, if there is one, returns the group-by field name.
  • array_variable - Refers to an array.
  • field_variable - A special variable in Formula. It refers to a group of values of a database field or of a formula field.
  • groupby - Can be a constant String to indicate the group-by field name, or a field variable on which the grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Note: If a field_variable is assigned to a variable, for example X, the variable X will lose the characteristic of representing a group of values. The following formula will then be treated as in correct.

Number x = @dbfield;
Number y = Count(x); // system will prompt you that there is no such kind of function.

A Number value.
  • Number x = Count([1.0, 2.0, 3.0, 4.5, 45.0, 67.0]).
  • Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0]; 
    Number y = Count(x).
  • Number x = Count(["str1","str2","str3","str4","str5","str6"]).
  • Number x = Count(@dbfield).
  • Number x = Count(@formula).
  • Number x = Count(@dbfield, "group_field").
  • Number x = Count(@formula, @"group_field").
  • Number x = Count(@dbfield, @parameter).
DistinctCount()
  • DistinctCount(array_variable);
  • DistinctCount(field_variable);
  • DistinctCount(field_variable, groupby);
This function is used to count the number of distinct values referred to by the first argument together. The second argument, if there is one, gives the group-by field name.
  • array_variable - Refers to an array whose element should be a Number.
  • field_variable - A special variable in formula. It refers to a group of values from a database or formula field.
  • groupby - Can be a constant String to indicate the group-by field name, or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Note: If a field_variable is assigned to a variable, for example X, the variable X will lose the characteristic of representing a group of values. The following formula will then be treated as incorrect.

Number x = @dbfield;
Number y = DistinctCount(x); // system will prompt you that there is no such kind of function.

A Number value.
  • Number x = DistinctCount([1.0, 1.0, 3.0, 4.5, 4.5, 67.0]) // result is 4
  • Number x[6] = [1.0, 1.0, 3.0, 4.5, 4.5, 67.0];
    Number y = DistinctCount(x)
  • Number x = DistinctCount(@dbfield)
  • Number x = DistinctCount(@formula)
  • Number x = DistinctCount(@dbfield, "group_field")
  • Number x = DistinctCount(@formula, @"group_field")
  • Number x = DistinctCount(@dbfield, @parameter)
Every()
  • Every(array_variable);
  • Every(field_variable);
This function is used to check whether each element in the argument is true. If yes, return true; if not, return false.
  • array_variable - Refers to an array
  • field_variable - A special variable in formula. It refers to a group of values of a database field or of a formula field.
A Boolean value.
  • Boolean x = Every([false, false, false, true, false, false])
  • Boolean x = Every(@isExcellent)
  • Boolean x = Every(@formula) (@formula is a formula which returns a Boolean array.)
  • Boolean x = Every(@@ctf1) (@@ctf1 is a crosstab formula which returns a Boolean array.)
Maximum()
  • Maximum(array_variable);
  • Maximum(field_variable);
  • Maximum(field_variable, groupby);
This function is used to pick up the maximum value from a group of values referred to by the first argument. The groupby argument specifies the group-by field name.
  • array_variable - Refers to an array whose element should be Number.
  • field_variable - A special variable in formula. It refers to a group of values from a database or formula field.
  • groupby - Can be a constant String to indicate the group-by field name, or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Note: If a field_variable is assigned to a variable, for example X, the variable X will lose the characteristic of representing a group of values. The following formula will then be treated as in correct.

Number x = @dbfield;
Number y = Maximum(x); // system will prompt you that there is no such kind of function.

The result type relies on the first argument. It may be Number, Currency, String, Date, Time, DateTime, or Boolean if the data type of each element in the first argument is Number, Currency, String, Date, Time, DateTime or Boolean respectively.
  • Number x = Maximum([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
  • Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
    Number y = Maximum(x)
  • Number x = Maximum(@dbfield)
  • Date x = Maximum(@formula)
  • Time x = Maximum(@dbfield, "group_field")
  • DateTime x = Maximum(@dbfield, @"group_field")
  • String x = Maximum(@dbfield, @parameter)
  • Currency x = Maximum(@dbfield)
  • Boolean x = Maximum(@formula)
  • Date x = Maximum(@formula, "group_field")
  • Currency x = Maximum(@formula, @"group_field")
  • Currency x = Maximum(@formula, @parameter)
  • String x = Maximum(['1', '2', '3.0', '4.5', '45', '67'])
  • Date x = Maximum([toDate(1998, 1, 1), toDate(1997, 7, 1), toDate(1999, 1, 16)])
Minimum()
  • Minimum(array_variable);
  • Minimum(field_variable);
  • Minimum(field_variable, groupby);
This function is used to pick up the minimum value from a group of values referred to by the first argument together. The second argument, if there is one, gives the group-by field name.
  • array_variable - Refers to an array whose element should be a Number.
  • field_variable - A special variable in formula. It refers to a group of values from a database or formula field.
  • groupby - Can be a constant String to indicate the group-by field name, or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Note: If a field_variable is assigned to a variable, for example X, the variable X will lose the characteristic of representing a group of values. The following formula will then be treated as incorrect.

Number x = @dbfield;
Number y = Minimum(x); // system will prompt you that there is no such kind of function.

The result type relies on the first argument. It may be Number, Currency, String, Date, Time, DateTime, or Boolean if the data type of each element in the first argument is Number, Currency, String, Date, Time, DateTime or Boolean respectively.
  • Number x = Minimum([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
  • Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
    Number y = Minimum(x)
  • Number x = Minimum(@dbfield)
  • Date x = Minimum(@formula)
  • Time x = Minimum(@dbfield, "group_field")
  • DateTime x = Minimum(@dbfield, @"group_field")
  • String x = Minimum(@dbfield, @parameter)
  • Currency x = Minimum(@dbfield)
  • Boolean x = Minimum(@formula)
  • Date x = Minimum(@formula, "group_field")
  • Currency x = Minimum(@formula, @"group_field")
  • Currency x = Minimum(@formula, @parameter)
  • String x = Minimum(['1', '2', '3.0', '4.5', '45', '67'])
  • Date x = Minimum([toDate(1998, 1, 1), toDate(1997, 7, 1), toDate(1999, 1, 16)]
PopulationStdDev()
  • PopulationStdDev(array_variable);
  • PopulationStdDev(field_variable);
  • PopulationStdDev(field_variable, groupby);
This function is used to find the population standard deviation of a group of values referred to by the first argument. The second argument, if there is one, gives the group-by field name.
  • array_variable - Refers to an array whose element should be Number value.
  • field_variable - A special variable in formula. It refers to a group of values from a database or formula field. Whatever kind of source the data comes from, database or formula, it should be of the Number data type.
  • groupby - Can be a constant String to indicate the group-by field name, or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Note: If a field_variable is assigned to a variable, for example X, the variable X will lose the characteristic of representing a group of values. The following formula will then be treated as incorrect.

Number x = @dbfield;
Number y = PopulationStdDev(x); // system will prompt you that there is no such kind of function.

A Number value.
  • Number x = PopulationStdDev([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
  • Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
    Number y = PopulationStdDev(x)
  • Number x = PopulationStdDev(@dbfield)
  • Number x = PopulationStdDev(@formula)
  • Number x = PopulationStdDev(@dbfield, "group_field")
  • Number x = PopulationStdDev(@dbfield, @"group_field")
  • Number x = PopulationStdDev(@dbfield, @parameter)
PopulationVariance()
  • PopulationVariance(array_variable);
  • PopulationVariance(field_variable);
  • PopulationVariance(field_variable, groupby);
This function is used to find the population variance of a group of values referred to be the first argument. The second argument, if there is one, gives the group-by field name.
  • array_variable - Refers to an array whose element should be Number value.
  • field_variable - A special variable in formula. It refers to a group of values from a database or formula field. Whatever kind of source the data comes from, database or formula, it should be of the Number data type.
  • groupby- Can be a constant String to indicate the group-by field name, or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Note: If a field_variable is assigned to a variable, for example X, the variable X will lose the characteristic of representing a group of values. The following formula will then be treated as incorrect.

Number x = @dbfield;
Number y = PopulationVariance(x); // system will prompt you that there is no such kind of function.

A Number value.
  • Number x = PopulationVariance([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
  • Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
    Number y = PopulationVariance(x)
  • Number x = PopulationVariance(@dbfield)
  • Number x = PopulationVariance(@formula)
  • Number x = PopulationVariance(@dbfield, "group_field")
  • Number x = PopulationVariance(@dbfield, @"group_field")
  • Number x = PopulationVariance(@dbfield, @parameter)
StdDev()
  • StdDev(array_variable);
  • StdDev(field_variable);
  • StdDev(field_variable, groupby);
This function is used to find the standard deviation of a group of values referred to by the first argument. The second argument, if there is one, gives the group-by field name.
  • array_variable - Refers to an array whose element should be a Number value.
  • field_variable - A special variable in formula. It refers to a group of values from a database or formula field. Whatever kind of source the data comes from, database or formula, it should be of the Number data type.
  • groupby - Can be a constant String to indicate the group-by field name, or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Note: If a field_variable is assigned to a variable, for example X, the variable X will lose the characteristic of representing a group of values. The following formula will then be treated as incorrect.

Number x = @dbfield;
Number y = StdDev(x); // system will prompt you that there is no such kind of function.

A Number value.
  • Number x = StdDev([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
  • Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
    Number y = StdDev(x)
  • Number x = StdDev(@dbfield)
  • Number x = StdDev(@formula)
  • Number x = StdDev(@dbfield, "group_field")
  • Number x = StdDev(@dbfield, @"group_field")
  • Number x = StdDev(@dbfield, @parameter)
Sum()
  • Sum(array_variable);
  • Sum(field_variable);
  • Sum(field_variable, groupby);
This function is used to add a group of values (referred to by the first argument) together. The second argument, if there is one, gives the group-by field name.
  • array_variable - Refers to an array whose element should be Number or Currency value.
  • field_variable - A special variable in formula. It refers to a group of values from a database or formula field. Whatever kind of source the data comes from, database or formula, it should be of the Number or Currency data type.
  • groupby - Can be a constant String to indicate the group-by field name, or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Note: If a field_variable is assigned to a variable, for example X, the variable X will lose the characteristic of representing a group of values. The following formula will then be treated as incorrect.

Number x = @dbfield;
Number y = Sum(x); // system will prompt you that there is no such kind of function.

The result type relies on the first argument. It may be Number or Currency if the data type of each element in the first argument is Number or Currency respectively.
  • Number x = Sum([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
  • Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
    Number y = Sum(x)
  • Currency x = Sum([$1, $2, $3.0, $4.5, $45, $67])
  • Currency x[] = [$1, $2, $3.0, $4.5, $45, $67];
    Currency y = Sum(x)
  • Number x = Sum(@dbfield)
  • Number x = Sum(@formula)
  • Number x = Sum(@dbfield, "group_field")
  • Number x = Sum(@dbfield, @"group_field")
  • Number x = Sum(@dbfield, @parameter)
  • Currency x = Sum(@dbfield)
  • Currency x = Sum(@formula)
  • Currency x = Sum(@formula, "group_field")
  • Currency x = Sum(@formula, @"group_field")
  • Currency x = Sum(@formula, @parameter)
UBound()
  • UBound(array_variable);
This function is used to return the number of elements in an array.
  • array_variable - Refers to an Array value, expression or variable.
A Number value.
  • The return value of the following statement is 5.

    integer a[] = [1,1,1,1,1];
    UBound(a)

  • The return value of the following statement is 6.

    UBound([$1, $2, $3.0, $4.5, $45, $67]).

  • if(isNull(@"Customer Name"))then
    {
    return "No Data";
    }
    else{
    string s[]=StringSplit(@"Customer Name");
    string s1="Java";
    string s2[]=filter(s,s1);
    if (UBound(s2)!=0) then
    return right(s2[0],4)
    else
    return "No matching data ";
    }

    This formula searchs for the customer names containing the word "Java".

Variance()
  • Variance(array_variable);
  • Variance(field_variable);
  • Variance(field_variable, groupby);
This function is used to find the variance of a group of values referred to by the first argument. The second argument, if there is one, gives the group-by field name.
  • array_variable - Refers to an array whose element should be Number value.
  • field_variable - A special variable in Formula, and refers to a group of values from a database or formula field. Whatever kind of source the data comes from, database or formula, it should be of the Number data type.
  • groupby - Can be a constant String to indicate the group-by field name, or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Note: If a field_variable is assigned to a variable, for example X, the variable X will lose the characteristic of representing a group of values. The following formula will then be treated as incorrect.

Number x = @dbfield;
Number y = Variance(x); // system will prompt you that there is no such kind of function.

A Number value.
  • Number x = Variance([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
  • Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
    Number y = Variance(x)
  • Number x = Variance(@dbfield)
  • Number x = Variance(@formula)
  • Number x = Variance(@dbfield, "group_field")
  • Number x = Variance(@dbfield, @"group_field")
  • Number x = Variance(@dbfield, @parameter)

Back to top

Date Range

You cannot used any of the Date Range functions to return a value singly.

Function Description Example
Aged0To30Days() This function returns a range of Date values that is within the previous 30 days from today. For example, if today is 10/14/99, the return range is from 09/15/99 to 10/14/99. The return value of the following statement is Not Expired.

if (ToDate(1999,9,20) in Aged0To30Days())
return "Not Expired"
else
return "Expired"

Aged31To60Days() This function returns a range of Date values that is within the previous 31 to 60 days from today date. For example, if today is 10/13/99, the return range is from 08/12/99 to 09/13/99. If today is 10/13/99, the return value of the following statement is false.

return ToDate(1998, 4, 20) in Aged31To60Days()

Aged61To90Days() This function returns a range of Date values that is within the previous 61 to 90 days from today date. For example, if today is 10/13/99, the return range is from 07/13/99 to 08/11/99. If today is 10/13/99, the return value of the following statement is false.

return ToDate(1998, 4, 20) in Aged61To90Days()

AllDatesFromToday() This function returns a range of Date values that include any date from the present day to a date about 10000 years in the future Date value. For example, if today is 04/15/98, the return range is from 04/15/98 to the future. If today is 10/10/99, the return value of the following statement is false.

return ToDate(1998, 4, 20) in AllDatesFromToday()

AllDatesFromTomorrow() This function returns a range of Date values that include any date from tomorrow to any future Date value. For example, if today is 04/15/98, the return range is from 04/16/98 to the future. If today is 10/10/99, the return of the following statement is false.

return ToDate(1998, 4, 20) in AllDatesFromTomorrow(

AllDatesToToday() This function returns a range of Date values that include all dates up through the present day. For example, if today is 4/15/98, the return range is from the past to 04/15/1998. If today is 10/10/99, the return value of the following statement is true.

return ToDate(1998, 4, 20) in AllDatesToToday()

AllDatesToYesterday() This function returns a range of Date values that include all dates up through the previous day. For example, if today is 4/15/98, the return range is from the past to 04/14/1998. If today is 10/10/99, the return value of the following statement is true.

return ToDate(1998, 4, 20) in AllDatesToYesterday()

Calendar1stHalf() This function returns a range of Date values that include all dates from January 1st through June 30th. If today is 10/10/99, the return value of the following statement is false.

return Today() in Calendar1stHalf()

Calendar1stQtr() This function returns a range of Date values that include all dates from January 1st through to March 31st. If today is 10/10/99, the return value of the following statement is false.

return Today() in Calendar1stQtr()

Calendar2ndHalf() This function returns a range of Date values that include all dates from July 1st through to December 31st. If today is 10/10/99, the return value of the following statement is true.

return Today() in Calendar2ndHalf()

Calendar2ndQtr() This function returns a range of Date values that include all dates from April 1st through to June 30th. If today is 10/10/99, the return value of the following statement is false.

return Today() in Calendar2ndQtr()

Calendar3rdQtr() This function returns a range of Date values that include all dates from July 1st through to September 30th. If today is 10/10/99, the return value of the following statement is false.

return Today() in Calendar3rdQtr()

Calendar4thQtr() This function returns a range of Date values that include all dates from October 1st through December 31st. If today is 10/10/99, the return value of the following statement is true.

return Today() in Calendar4thQtr()

Last4WeeksToSun() This function returns a range of Date values that include the four weeks previous to last Sunday. For example, if today is 4/15/95, the return range is from 3/13/95 to 4/9/95. If today is 10/10/99, the return value of the following statement is false.

return ToDate(1999, 4, 20) in Last4WeeksToSun()

Last7Days() This function returns a range of Date values that include all dates from seven days ago to today (including today). For example, if today is 04/15/98, the return range is from (include) 04/09/98 to 04/15/98. If today is 10/10/99, the return value of the following statement is false.

return ToDate(1999, 4, 20) in Last7Days()

LastFullMonth() This function returns a range of Date values that include all dates from the first to the last day of the previous month. For example, if today is 04/15/98, the return range is from 03/01/98 to 03/31/98. If today is 10/10/99, the return value of the following statement is false.

return ToDate(1999, 4, 20) in LastFullMonth()

LastFullWeek() This function returns a range of Date values that include all dates from the Sunday to the Saturday of the previous week. For example, if today is 04/10/95, the return range is from 04/02/95 to 04/08/95. If today is 10/10/99, the return value of the following statement is false.

return ToDate(1999, 4, 20) in LastFullWeek()

LastYearMTD() This function returns a range of Date values that fall on any date in the current month last year, up to the current date last year. For example, if today is 04/15/98, the return range is from 04/01/97 to 04/15/97. If today is 10/10/99, the return value of the following statement is false.

return ToDate(1999, 4, 20) in LastYearMTD()

LastYearYTD() This function returns a range of Date values that include all dates in the last year, up to the current date last year. For example, if today is 04/15/98, the return range is from 01/01/97 to 04/15/97. If today is 10/10/99, the return value of the following statement is true.

return ToDate(1998, 4, 20) in LastYearYTD()

MonthToDate() This function returns a range of Date values that include all dates from the first day of the month to today. For example, if today is 04/15/98, the return range is from 04/01/98 to 04/15/98. If today is 10/10/99, the return value of the following statement is true.

return ToDate(1999, 10, 2) in MonthToDate()

Next30Days() This function returns a range of Date values that include all days in the next 30 days starting from today (including today). For example, if today is 04/15/98, the return range is from 04/15/98 to 05/15/98. If today is 10/10/99, the return value of the following statement is false.

return ToDate(1999, 10, 2) in Next30Days()

Next31To60Days() This function returns a range of Date values that include all days in the next 31 to 60 days starting from today. For example, if today is 04/15/98, the return range is from 05/16/98 to 06/14/98. If today is 10/10/99, the return value of the following statement is false.

return ToDate(1999, 10, 2) in Next31To60Days()

Next61To90Days() This function returns a range of Date values that include all days in the next 61 to 90 days starting from today. For example, if today is 04/15/98, the return range is from 06/15/98 to 07/14/98. If today is 10/10/99, the return value of the following statement is false.

return ToDate(1999, 4, 20) in Next61To90Days()

Next91To365Days() This function Returns a range of Date values that include all days in the next 91 to 365 days starting from today. For example, if today is 04/15/98, the return range is from 07/15/98 to 04/15/99. If today is 10/10/99, the return value of the following statement is false.

return ToDate(1999, 4, 20) in Next91To365Days()

Over90Days() This function returns a range of Date values that include all days that are more than 90 days older than the current date. For example, if today is 04/15/98, the return range is from the past to 01/14/98. If today is 10/10/99, the return value of the following statement is true.

return ToDate(1999, 4, 20) in Over90Days()

WeekToDateFromSun() This function returns a range of Date values that include all days from last Sunday to today (including today). For example, if today is 04/13/95, the return range is from 04/09/95 to 04/13/95. If today is 10/10/99, the return value of the following statement is false.

return ToDate(1999, 4, 20) in WeekToDateFromSun()

YearToDate() This function returns a range of Date values that include all days from the first day of the calendar year to today (including today). For example, if today is 04/15/98, the return range is from 01/01/98 to 04/15/98. If today is 10/10/99, the return value of the following statement is true.

return ToDate(1999, 4, 20) in YearToDate()

Back to top

Date/Time

Date functions allow you to convert numbers to dates and dates to numbers.

Function Description Parameter Return Value Example
CurrentDate() This function returns the current date on a report.   A Date value. If today is Oct. 6,1999, the return value of the following statement is 10/06/99.

CurrentDate()

CurrentDateTime() This function returns the current date and time on a report.   A DateTime value. If today is Oct.6,1999, the current time is 5:10:27 p.m., the return value of the following statement is 1999-10-06 05:10:27 PM.

CurrentDateTime()

CurrentTime() This function returns the current time on a report.   A Time value. If the current time is 5:10:27 p.m., the return value of the following statement is 05:10:27 PM.

CurrentTime()

DateAdd(intervalType, nIntervals, startDateTime)

This function returns a DateTime value to which a specified number of time intervals have been added.

It is used to add interval of time to a DateTime. Its main feature is that the DateTime returned will always be valid. For example, the function takes into account such factors as the number of days in a month and leap years. If you want to add or subtract days to a DateTime, you could use the addition and subtraction operators instead of the function with the "d" parameter. However, this function also handles other types of intervals such as adding months or hours to a DateTime.

  • intervalType - A String expression specifying the interval of time to be added.

    Possible values can be:

    • YYYY - Year
    • Q - Quarter (3-month period)
    • M - Month
    • Y - Day of year
    • D - Day
    • W - Weekday
    • WW - Week (7-day period)
    • H - Hour
    • N - Minute
    • S - Second
  • nIntervals - A Number or numeric expression specifying the number of intervals to be added. It can be positive (to get DateTimes from the future), or negative (to get DateTimes from the past).
  • startDateTime - The DateTime value to which the intervals are to be added.

Notes:

  • To add days to a DateTime, you can use any of the interval type parameters "Y", "D" or "W". They all have the same effect for DateAdd.
  • DateAdd returns a DateTime value and not a Date value. However, you may need to convert this DateTime value to a Date value in certain situations (such as if you wanted to assign the value returned by DateAdd to a Date type variable). To convert to a Date value, use DateAdd in combination with the ToDate type conversion function. For example, the following returns the Date value for October 6, 1997.

    ToDate(DateAdd("YYYY",-2,ToDateTime(1999,10,6)))

A DateTime value.
  • DateAdd("YYYY",1,CurrentDateTime()) - Returns the DateTime value for current datetime plus one year.
  • DateAdd("Q",1,CurrentDateTime()) - Returns the DateTime value for current datetime plus three months.
  • DateAdd("M",1,CurrentDateTime()) - Returns the DateTime value for current datetime plus one month.
  • DateAdd("Y",1,CurrentDateTime()) - Returns the DateTime value for current datetime plus one day.
  • DateAdd("D",1,CurrentDateTime()) - Returns the DateTime value for current datetime plus one day.
  • DateAdd("W",1,CurrentDateTime()) - Returns the DateTime value for current datetime plus one day.
  • DateAdd("WW",1,CurrentDateTime()) - Returns the DateTime value for current datetime plus seven days.
  • DateAdd("H",1,CurrentDateTime()) - Returns the DateTime value for current datetime plus one hour.
  • DateAdd("N",1,CurrentDateTime()) - Returns the DateTime value for current datetime plus one minute.
  • DateAdd("S",1,CurrentDateTime()) - Returns the DateTime value for current datetime plus one second.
  • DateAdd("D",-32,ToDateTime(1999,9,28)) - Returns the DateTime value 1999-8-27 00:00:00.
  • DateAdd("M",1,ToDateTime(1996,1,31)) - Returns the DateTime value 1996-2-29 00:00:00. Notice that DateAdd will not return the invalid value 1996-2-31 00:00:00.
  • DateAdd("Q",17,ToDateTime(1999,9,28)) - Returns the DateTime value 2003-12-28 00:00:00.
  • DateAdd("H",-400,ToDateTime(1999,9,28)) - Returns the DateTime value 1999-9-11 08:00:00. In other words, this is the result of subtracting 400 hours from 1999-9-28 00:00:00.
DateDiff(intervalType, startDateTime, endDateTime, firstDayOfWeek)

Overloads:

  • DateDiff(intervalType, startDateTime, endDateTime)
  • DateDiff(intervalType, startDateTime, endDateTime, firstDayOfWeek)
This function returns a number of time intervals between two specified dates.
  • intervalType - A string expression that is the interval of time you use to calculate the difference between startDateTime and endDateTime.

    Possible values can be:

    • YYYY - Year
    • Q - Quarter
    • M - Month
    • Y - Day of year
    • D - Day (both "Y" and "D" find the difference in days)
    • W - Number of weeks between startDateTime and endDateTime
    • WW - Number of firstDayOfWeek's between startDateTime and endDateTime
    • H - Hour
    • N - Minute
    • S - Second
  • startDateTime - The first DateTime value used in calculating the difference.
  • endDateTime - The second DateTime value used in calculating the difference.
  • firstDayOfWeek - An optional constant specifying the first day of the week. If not specified, jrSunday will be used.
    • jrUseSystem - 0 (Use the current system date)
    • jrSunday - 1
    • jrMonday - 2
    • jrTuesday - 3
    • jrWednesday - 4
    • jrThursday - 5
    • jrFriday - 6
    • jrSaturday - 7
A Number value.
  • Use DateDiff with the "D" or "Y" interval type parameter to find the number of days between two dates.

    DateDiff("D", ToDateTime(1999,10,7), ToDateTime(1999,10,10)) - Returns 3

  • Use DateDiff with the "YYYY" interval type parameter to find the number of years difference between two dates. This use of DateDiff is the same as finding the difference between the year of endDateTime and the year of startDateTime.
    • DateDiff("YYYY", ToDateTime(1999,10,7), ToDateTime(2005,2,10)) - Returns 6.
    • DateDiff("YYYY", ToDateTime(1999,12,31), ToDateTime(2000,1,1)) - Returns 1 (a 1 year difference), even though there is only a 1 day difference between the dates.
    • DateDiff("YYYY", ToDateTime(1999,1,1), ToDateTime(1999,12,31)) - Returns 0 (a 0 year difference), even though there is a 364 days difference.

    Suppose that for the above examples, the first date is the date that you bought a mutual fund, and the second date is the date you sold it. The mutual fund company must send you an annual report for every year in which you owned units in the fund. You would then get 7, 2 and 1 annual reports respectively, in the above cases.

  • Use DateDiff with the "Q" parameter to find the number of quarters (3 month periods) difference between two dates.
    • DateDiff("Q", ToDateTime(1999,10,6), ToDateTime(2003,5,20)) - Returns 14.
    • DateDiff("Q", ToDateTime(1999,3,31), ToDateTime(1999,4,1)) - Returns 1. The two dates are in adjacent quarters.
    • DateDiff("Q", ToDateTime(1999,1,1), ToDateTime(1999,3,31)) - Returns 0. The two dates are in the same quarter.

    Suppose the mutual fund company in the "YYYY" example mailed out quarterly reports. It would need to mail out 15, 2 and 1 quarterly reports respectively, in the above cases.

  • Use DateDiff with the "M" parameter to find the number of months difference between two dates.

    DateDiff("M", ToDateTime(1999,3,15), ToDateTime(1999,7,13)) - Returns 4.

  • Use DateDiff with the "W" parameter to calculate the number of weeks between two dates. For example, if startDateTime is on a Tuesday, DateDiff counts the number of Tuesdays between startDateTime and endDateTime not including the initial Tuesday of startDateTime. Note however that it counts endDateTime if endDateTime is on a Tuesday.
    • DateDiff("W", ToDateTime(1999,10,19), ToDateTime(1999,10,25)) - Returns 0.
    • DateDiff("W", ToDateTime(1999,10,19), ToDateTime(1999, 10,26)) - Returns 1.
DatePart(intervalType, inputDateTime, firstDayOfWeek, firstWeekOfYear)

Overloads:

  • DatePart(intervalType, inputDateTime)
  • DatePart(intervalType, inputDateTime, firstDayOfWeek)
  • DatePart(intervalType, inputDateTime, firstDayOfWeek, firstWeekOfYear)
  • DatePart(intervalType, inputTime)
  • DatePart(intervalType, inputTime, firstDayOfWeek)
  • DatePart(intervalType, inputTime, firstDayOfWeek, firstWeekOfYear)
This function returns a number that specifies a given part of a given date.
  • intervalType - A String expression that specifies the part of a date to be returned.

    Possible values can be:

    • YYYY - Extracts the year
    • Q - Quarter (the result is 1, 2, 3 or 4)
    • M - Month (the result is from 1 to 12)
    • Y - Day of year (1 to 365 or 366 in a leap year)
    • D - Day part of the date (1 to 31)
      Year
    • W - Day of week (1 to 7 with the result depending on firstDayOfWeek)
    • WW - Week of year (1 to 53 with firstDayOfWeek and firstWeekOfYear determining the exact days of the first calendar week of the year)
    • H - Extracts the hour part of the given DateTime (0 to 23)
    • N - Minute part (0 to 59)
    • S - Second part (0 to 59)
  • inputDateTime - The DateTime value whose part will be extracted.
  • firstDayOfWeek - An optional constant used to specify the first day of the week. If not specified, jrSunday will be used.
    • jrUseSystem - 0 (Use the current system date)
    • jrSunday - 1
    • jrMonday - 2
    • jrTuesday - 3
    • jrWednesday - 4
    • jrThursday - 5
    • jrFriday - 6
    • jrSaturday - 7
  • firstWeekOfYear - An optional constant specifying the first week of the year. If not specified, the first week is assumed to be the one in which Jan. 1 occurs (jrFirstJan1).
    ConstantValueDescription
    jrUseSystem0Start with week in which system date occurs
    jrFirstJan11Start with week in which January 1 occurs (default)
    jrFirstFourDays2Start with the first week that has at least four days in the new year
    jrFirstFullWeek3Start with first full week of the year

Notes:

  • The DatePart function with "YYYY" intervalType parameter is the same as the Year function. Similarly, the DatePart function with "M", "D", "W", "H", "N" and "S" intervalType argument is the same as the functions Month, Day, Weekday (or DayOfWeek), Hour, Minute and Second respectively. On the other hand, there is no easy alternative to using the DatePart function for the "Q", "Y" and "WW" intervalType parameters.
  • The firstDayOfWeek parameter affects the DatePart function when the interval type parameter is "W" or "WW". For all other intervalType parameter values, it is ignored.
  • The firstWeekOfYear parameter affects the DatePart function only when the intervalType argument is "WW". For all other intervalType argument values, it is ignored.
A Number value.
  • DatePart("D", ToDateTime(1999,8,15)) - Returns 15.
  • DatePart("M", ToDateTime(1999,8,15)) - Returns 8.
  • DatePart("N", ToDateTime(1999,8,15,10,35,0)) - Returns 35.
  • DatePart("Q", ToDateTime(1999, 9,29)) - Returns 3 since September 29 is in the third quarter of the year.
  • DatePart("WW", ToDateTime(1997, 9,14)) - Returns 38 since 1997-9-14 is in the 38th week of 1997.
DateTimeTo2000(Datetime, Number)
  • 2 digit years(xx)
    If the Year value is greater than the windowing number, 19 is appended before the 2 digits (19xx). If the Year value is less than or equal to the windowing number, 20 is appended (20xx).
  • 4 digit years(19xx)
    If the last two digits in the Year value are greater than the windowing number, the Year is retained as found in the Date field (19xx). If the two digits in the Year value are less than or equal to the windowing number, the first two digits are changed to 20 (20xx). If the first two digits in the year field are 20, the Year is retained as found in the Date field (20xx).
  • DateTime - Accepts only valid DateTime fields, with either 2 digit or 4 digit years.
  • Number - A number between 0 and 99 corresponding with the desired windowing year.

Note: If the year is less than or equal to 1899, and greater than or equal to 100, there will be no change to the date.

 

A Date field with a four digit year.
  • Here the window value is greater than the year, and so the year is changed to 20XX.

    DateTimeTo2000(ToDateTime(1998, 12, 12, 3, 2, 1), 99) - Returns 2098-12-12 3:02:01.

  • Here the window value is less than the year, and so it will not change the year.

    DateTimeTo2000(ToDateTime(1995, 1, 2, 3, 2, 1), 94) - Returns 1995-01-02 3:02:01.

  • These are examples of dates that will not be affected by this function.
    • DateTimeTo2000(ToDateTime(1899, 12, 12, 5, 6, 7), 99) - Returns 1899-12-12 5:06:07.
    • DateTimeTo2000(ToDateTime(999, 12, 12, 5, 6, 7), 99) - Returns 999-12-12 5:06:07.
  • Some databases maintain the year of a Date as a two-digit field. These samples simulate that Date field.
    • DateTimeTo2000(ToDateTime(93, 12, 12, 5, 6, 7), 96) - Returns 2093-12-12 5:06:07.
    • DateTimeTo2000(ToDateTime(98, 12, 12, 5, 6, 7), 50) - Returns 1998-12-12 5:06:07.
DateTimeToDate(Datetime a) This function evaluates the specified DateTime value and returns only the date.
  • a - A DateTime value.
A Date value. If the current date is Oct. 17, 1999 and the current time is 12:27:15, the return value of the following statement is 10/17/99.

DateTimeToDate(CurrentDateTime())

DateTimeToSeconds(Datetime a) This function evaluates the specified value and converts the time to the number of seconds from 00:00:00 to the specified time.
  • a - A DateTime value.
An Integer value. If the current date is Oct. 17, 1999 and the current time is 12:27:15, the return value of the following statement is 44835.

DateTimeToSeconds(CurrentDateTime())

DateTimeToTime(Datetime a) This function evaluates the specified DateTime value and returns only the time.
  • a - A DateTime value.
A Time value. If the current date is Oct. 17, 1999 and the current time is 12:27:15, the return value of the following statement is 12:27:15.

DateTimeToTime(CurrentDateTime())

DateTo2000(date, number)
  • 2 digit years(xx)
    If the Year value is greater than the windowing number, 19 is appended before the 2 digits (19xx). If the Year value is less than or equal to the windowing number, 20 is appended (20xx).
  • 4 digit years(19xx)
    If the last two digits in the Year value are greater than the windowing number, the Year is preserved as found in the date field (19xx). If the last two digits in the Year value are less than or equal to the windowing number, the first two digits are changed to 20 (20xx). If the first two digits in the year field are 20, the Year is preserved as found in the date field (20xx).
  • date - Accepts only valid Date fields, with either 2 digit or 4 digit years.
  • number - A number between 0 and 99 corresponding with the desired windowing year.

Notes:

  • If the year is less than or equal to 1899 and greater or equal to 100, there will be no change to the date.
  • Whether your database dates are interpreted as two digit years or 4 digit years (19xx) is dependent on the database driver you are using. The result of the formula will be the same in either case.
A Date field with a four digit year.
  • DateTo2000(ToDate(1993,12,12), 99)- Returns 2093/12/12 because the window value is greater than the year, and so the date will be changed to 20XX.
  • DateTo2000(ToDate(1993,12,12), 92)- Returns 1993/12/12 because the window value is less than the year, and so the year will not be changed.
  • This is an example of a date that is not affected by this function:

    DateTo2000(ToDate(1899,12,12), 99)- Returns 1899/12/12.

  • This is an example of a date that is not affected by this function.

    DateTo2000(ToDate(100,12,12), 99)- Returns 100/12/12.

  • Some databases maintain the year of a date as a two-digit field. The following simulates this type of date field.
    • DateTo2000(ToDate (98,12,12), 99)- Returns 2098/12/12.
    • DateTo2000(ToDate (98,12,12), 97)- Returns 1998/12/12.
    • DateTo2000(ToDate (9,12,12), 10)- Returns 2009/12/12.
    • DateTo2000(ToDate (1,12,12), 0)- Returns 1901/12/12.
Day() This function returns the day of the month.   An Integer value. If today is Oct. 17, 1999, the return value of the following statement is 17.

Day()

Day(Date a) This function extracts the day portion of a specified Date value.
  • a - A Date value.
An Integer value. If the current date is Oct. 17, 1999, the return value of the following statement is 17.

Day(CurrentDate())

Day(Datetime a) This function extracts the day portion of a specified Date value.
  • a - A DateTime value.
An Integer value. If the current date is Oct. 17, 1999 and the current time is 12:27:15, the return value of the following statement is 17.

Day(CurrentDateTime())

DayOfWeek() This function returns the day of the week.   An Integer value. If today is Thursday, the return value of the following statement is 5.

DayOfWeek()

DayOfWeek(Date a) This function extracts the day portion of a specified Date value, and indicates which day of the week.
  • a - A Date value.
An Integer value. If today is Oct. 17, 1999 and today is Sunday, the return value of the following statement is 1.

DayOfWeek(CurrentDate())

DayOfWeek(Datetime a) This function extracts the day portion of a specified Date value, and indicates which day of the week.
  • a - A DateTime value.
An Integer value. If the current date is Oct. 17, 1999, current time is 12:27:15 and today is Sunday, the return value of the following statement is 1 (Sunday is the first day of the week).

DayOfWeek(CurrentDateTime())

DayOfYear() This function returns the day of the year.   An Integer value. If today is Oct. 17, 1999, the return value of the following statement is 290.

DayOfYear()

DTSTo2000(DateString, Number)
  • 2 digit years(xx)
    If the Year value is greater than the windowing number, 19 is appended before the 2 digits(19xx). If the Year value is less than or equal to the windowing number, 20 is appended (20xx).
  • 4 digit years(19xx)
    If the last two digits in the Year value are greater than the windowing number, the Year is retained as found in the Date field (19xx). If the two digits in the Year value are less than or equal to the windowing number, the first two digits are changed to 20 (20xx). If the first two digits in the year field are 20, the Year is retained as found in the Date field (20xx).

 

  • Date String - Accepts only valid Date fields, with either 2 digit or 4 digit years. A DateTime string entered in the format: "yyyy/MM/dd HH: mm: ss.00" or "yy/MM/dd HH:mm:ss:00". For example, "1997/04/11 12:12:12.00" or "97/04/11 12:12:12.00".
  • Number - A number between 0 and 99 corresponding with the desired windowing year.

Note: If the year is less than or equal to 1899 and greater than or equal to 100, there will be no change to the date.

 

A DateTime string with a four digit year.
  • DTSTo2000("1988-12-12 12:12:12", 90)- Returns 2088-12-12 12:12:12 because the window value is greater than the year, and so the year will be changed to 20XX.
  • DTSTo2000("1988-12-12 12:12:12", 85)- Returns 1988-12-12 12:12:12 because the window value is less than the year, and so the year will not change.
  • These are examples of a date that will not be affected by the function:
    • DTSTo2000("1899/12/12 1:2:3am", 99)- Returns 1899/12/12 1:2:3am.
    • DTSTo2000("100/12/12 1:02:03AM", 99)- Returns 100/12/12 1:02:03AM.
  • Some databases maintain the year of a date as a two-digit field and so these samples will simulate that date field:
    • DTSTo2000("98/12/12 1:02:03AM", 99)- Returns 2098/12/12 1:02:03AM.
    • DTSTo2000("98/12/12 1:02:03", 97)- Returns 1998/12/12 1:02:03AM.
    • DTSTo2000("9/12/12 1:02:03AM", 10)- Returns 2009/12/12 1:02:03AM.
    • DTSTo2000("1/12/12 1:02:03AM", 0)- Returns 1901/12/12 1:02:03AM.
DTSToSeconds(string) This function evaluates the specified string, and converts the Time value to the number of seconds from 00:00:00 (12:00 midnight) to the specified time.
  • string - A String which includes a Date and a Time value.
A Whole number value. DTSToSeconds("2004/06/11 13:42:15")- Returns 49335.
FirstDayOfMonth(Date a) This function returns the first day of the month.
  • a - A Date value.
A Date value. The return value of the following statement is 10/01/2008.

FirstDayOfQuarter(ToDate("11/24/2008"))

FirstDayOfQuarter(Date a) This function returns the first day of the quarter.
  • a - A Date value.
A Date value. The return value of the following statement is 10/01/2008.

FirstDayOfQuarter(ToDate("11/24/2008"))

FirstDayOfWeek(Date a) This function returns the first day of the week.
  • a - A Date value.
A Date value. The return value of the following statement is 11/23/2008.

FirstDayOfWeek(ToDate("11/24/2008"))

FirstDayOfYear(Date a) This function returns the first day of the year.
  • a - A Date value.
A Date value.

The return value of the following statement is 01/01/2008.

FirstDayOfYear(ToDate("11/24/2008"))

FirstSundayOfMonth(Date a) This function returns the first Sunday of the month.
  • a - A Date value.
A Date value. The return value of the following statement is 11/02/2008.

FirstSundayOfMonth(ToDate("11/24/2008"))

FirstSundayOfYear(Date a) This function returns the first Sunday of the year.
  • a - A Date value.
A Date value. The return value of the following statement is 01/06/2008.

FirstSundayOfYear(ToDate("11/24/2008"))

ForEachDay(Datetime a) This function returns the Date portion of a specified DateTime value.
  • a - A DateTime value.
A Date value. If the DateTime is Dec.25,1999 20:12:50, the return value of the following statement is 12/25/99.

ForEachDay(ToDateTime(1999, 12, 25, 20, 12, 50))

ForEachHalfMonth(DateTime a) If the day in the DateTime is less than 15, this function will return to the first day of the month. If the day is more than 15, it will return to the 16th day of the month.
  • a - A DateTime value.
A Date value.
  • If the datetime is Sep.13,1999 12:27:15, the return value of the following statement is 09/01/99.

    ForEachHalfMonth(ToDateTime(1999,9,13,12,27,15))

  • If the datetime is Oct.18,1999 5:21:22, the return value of the following statement is 10/16/99.

    ForEachHalfMonth(ToDateTime(1999,10,18,5,21,22))

ForEachHalfYear(DateTime a) If the month of a specified DateTime is before July, this function will return Jan.1 of the year; otherwise it will return July 1 of the year.
  • a - A DateTime value.
A Date value.
  • If the datetime is May. 28,1999 10:10:25, the return value of the following statement is 01/01/99.

    ForEachHalfYear(ToDateTime(1999, 5, 28, 10, 10, 25))

  • If the datetime is Nov. 15,1998 11:40:23, the return value of the following statement is 07/01/99.

    ForEachHalfYear(ToDateTime(1998, 11, 15, 11, 40, 23))

ForEachMonth(DateTime a) This function returns the first day of the month in a specified DateTime value.
  • a - A DateTime value.
A Date value. If the datetime is Sep.24,1999 12:05:56, the return value of the following statement is 09/01/99.

ForEachMonth(ToDateTime(1999,9,24,12,5,56))

ForEachQuarter(DateTime a) If the month of a specified DateTime is before April, the function will return Jan. 1 of the year. If the month is after April but before July, the function will return April. 1 of the year. If the month is after July but before October, the function will return July. 1 of the year. If the month is after October, the function will return Oct.1 of the year.
  • a - A DateTime value.
A Date value.
  • If the datetime is Feb.14,1997 2:10:23, the return value of the following statement is 01/01/97.

    ForEachQuarter(ToDateTime(1997,2,14,2,10,23))

  • If the datetime is May. 28,1999 10:10:25, the return value of the following statement is 04/01/99.

    ForEachQuarter(ToDateTime(1999,5,28,10,10,25))

  • If the datetime is Aug. 28,1999 10:10:25, the return value of the following statement is 07/01/99.

    ForEachQuarter(ToDateTime(1999,8,28,10,10,25))

  • If the datetime is Nov. 11,1998 11:40:23, the return value of the following statement is 10/01/98.

    ForEachQuarter(ToDateTime(1998,11,11,11,40,23))

ForEachWeek(DateTime a) This function returns the Date of the first day of the week.
  • a - A DateTime value.
A Date value. If the DateTime is Oct. 15,1999 5:15:20 and it is Friday, the return value of the following statement is 10/10/99.

ForEachWeek(ToDateTime(1999, 10, 15, 5, 15, 20))

ForEachYear(DateTime a) This function returns Jan.1 of the year.
  • a - A DateTime value.
A Date value. If the datetime is Aug. 28,1999 10:10:25, the return value of the following statement is 01/01/99.

ForEachYear(ToDateTime(1999, 8, 28, 10, 10, 25))

Hour() This function extracts the hour portion of the current time.   An integer value. If the current time is 8:15:30, the return value of the following statement is 8.

Hour()

Hour(DateTime a) This function extracts the hour portion of a specified DateTime value.
  • a - A DateTime value.
An integer value. Suppose the datetime is Oct. 15,1999 9:35:22. The return value of the following statement is 9.

Hour(ToDateTime(1999,10,15,9,35,22))

Hour(Time a) This function extracts the hour portion of a specified Time value.
  • a - A Time value.
An integer value. Suppose the time is 10:05:26. The return value of the following statement is 10.

Hour(ToTime(10, 5, 26))

IsDate(number or string) This function returns true if the given String or Number value can be converted to a valid Date and returns false otherwise. A valid Date is any Date between 100-1-1 and 9999-12-31.
  • number - A Number value representing the number of days starting from January 1, 1900. It can be positive or negative, and is truncated if fractional.
  • string - A text string representing a date and many forms can be accepted. If the string is a right Date value and format, the function returns true, otherwise false.
A Boolean value.
  • IsDate(50) - Returns true since the number 50 is interpreted as 50 days from Jan. 1, 1900, which is Feb 19, 1900.
  • IsDate(-50) - Returns true since the number –50 is interpreted as 50 days before Jan 1, 1900, which is Nov 11, 1899.
  • IsDate("Feb 1, 2004") - Returns true since the string is a right Date value and there is only one blank between 'Feb' and '1,' and '2004'.
  • IsDate("2004 - 2 - 19") - Returns false since the String format is not right Date format.
  • IsDate("2003/2/29") - Returns false since 2003 is not leap year.
  • IsDate("2004*2*19") - Returns false since the String format is not right Date format.
IsDateTime(number or string) This function returns True if the given Number or String value can be converted to a valid DateTime, and returns False otherwise.
  • number - A Number value or expression to be tested for convertibility to a DateTime value. It can be positive, negative or fractional. It is interpreted as a number of days from Jan 1, 1900.
  • string - A String value or expression to be tested for convertibility to a DateTime value. Many forms are accepted. If the given String value and format are right DateTime value and format, the function will return true, otherwise false.
A Boolean value.
  • IsDateTime(15.2) - Returns true since the given number argument is interpreted as the DateTime value 1900-01-15 04:48:00.
  • IsDateTime("Feb 23, 2004 15:23:25") - Returns true.
  • IsDateTime("2004-2-23") - Returns true.
  • IsDateTime("15:23:25") - Returns true.
IsTime(number or string) This function returns True if the given Number or String value can be converted to a valid Time, and returns False if otherwise.
  • number - A Number value or expression to be tested for convertibility to a Time value. It can be positive, negative or fractional. It is interpreted as units of 24 hours. If the number makes the Time value between 0:0:0: and 23:59:59, the function will return true, otherwise false.
  • string - A String value or expression to be tested for convertibility to a Time value. Many forms are accepted. If the String value and the format are right Time value and format, the function will return true, otherwise false.
A Boolean value.
  • IsTime(0.5) - Returns true since the given number argument is interpreted as 0.5 units of 24 hours, which is 12:00:00.
  • IsTime(3.8) - Returns true since the given number is interpreted as 3.8 units of 24 hours, which is 19:12:00.
  • IsTime("8:30:00") - Returns true since 8:30:00 is right Time value and format.
  • IsTime("8 30:00") - Returns false since the given string is not right Time format.
  • IsTime("8:30:62") - Returns false since the given string is not right Time value.
Minute() This function returns the minute portion of the current time.   An Integer value. If the current time is 12:41:27, the return value of the following statement is 41.

Minute()

Minute(DateTime a) This function returns the minute portion of a specified DateTime.
  • a - A DateTime value.
An Integer value. Suppose the datetime is Sep. 21,1997 12:20:25. The return value of the following statement is 20.

Minute(ToDateTime(1997, 9, 21, 12, 20, 25))

Minute(Time a) This function returns the minute portion of a specified time.
  • a - A Time value.
An Integer value. Suppose the time is 8:30:27. The return value of the following statement is 30.

Minute(ToTime(8,30,27))

Month() This function adding one returns the current month of the year.   An Integer value, which ranges from 0 to 11. If the current month is September, the return value of the following statement is 8.

Month()

Month(Date a) This function extracts the month portion from a specified Date value.
  • a - A Date value.
An Integer value. If the date is June 15,1999, the return value of the following statement is 6.

Month(ToDate(1999, 6, 15))

Month(DateTime a) This function extracts the month portion from a specified DateTime value.
  • a - A DateTime value.
An Integer value. If the date time is July 15, 1999 10:10:10, the return value of the following statement is 7.

Month(ToDateTime(1999, 7, 15, 10, 10, 10))

MonthName(month, abbreviate) This function returns a String name for the specified month.
  • month - A whole number representing the month of the year, with values between 1 and 12, where 1 represents January.
  • abbreviate - An optional Boolean value that indicates if the month name is to be abbreviated. If the abbreviate value is true, the function will return the abbreviated month name. Otherwise the function will return the full month name.
A String value.
  • MonthName(4) - Returns String value "April".
  • MonthName(12, true) - Returns String value "Dec".
  • MonthName(12, false) - Returns String value "December".
Now() This function prints the current time on a report.   A Time value. If the current time is 1:24:35 in the afternoon, the return value of the following statement is 1:24:35 PM.

Now()

Quarter()

Overloads:

  • Quarter()
  • Quarter(Date,Integer)
  • Quarter(Datetime,Integer)
This function returns the quarter for the given date (1 for the first quarter).
  • Date - A Date value.
  • Integer - An Integer value in the range of 1 to 12. It is an optional argument which indicates the beginning month of the first quarter. Specifying the number is useful for fiscal quarter. If b is not specified, the default is 1 which means January is the beginning month of the first quarter as same as the calendar quarter.
  • Datetime - A Datestamp value.
An integer value (1, 2, 3, 4).
  • Quarter() - Return 3 if it is September according to the current system date.
  • Quarter(ToDate(2006,11,28),4) - Return 3. In this example, the first quarter begins from the 4th month - April, so the 11th month is in the third quarter.
  • Quarter(ToDateTime(2006,7,15,10,10,10),4) - Return 2.
Second() This function extracts the second portion of the current time.   An Integer value. If the current time is 4:55:03, the return value of the following statement is 3.

Second()

Second(Datetime a) This function extracts the second portion of a specified DateTime.
  • a - A DateTime value.
An Integer value. Suppose the datetime is Oct. 15,1999 10:15:17, the return value of the following statement is 17.

Second(ToDateTime(1999, 10, 15, 10, 15, 17 ))

Second(Time a) This function extracts the second portion of a specified time.
  • a - A Time value.
An Integer value. Suppose the time is 7:51:59, the return value of the following statement is 59.

Second(ToTime(7, 51, 59))

SelectedDate()

This function returns the date selected in the calendar or returns today() when no calendar is available for selecting a date.

The built-in expression Selected Date in Calendar calls this function. You can find the expression in the Template drop-down list in the Calendar dialog.

  A Date value. If the calendar date is March 12, 2012, the function returns 03/12/2012.
Timer() This function returns the number of seconds elapsed since midnight.   A Number value. If the current time is 9:28:30am, the function returns 34110.
ToDate(number or string) This function uses IsDate(number or string) to check whether the number/string is right or not. If the number/string is right, the function returns a Date value between 100-1-1 and 9999-12-31. If the number/string is wrong, it returns null.
  • number - A value representing the number of days starting from January 1, 1900. It can be positive or negative, and is truncated if fractional.
  • string - A text string representing a date and many forms can be accepted.
A Date value or null.
  • ToDate(50.5) - Returns 1900-2-19 since fraction is truncated.
  • ToDate(-50) - Returns 1970-01-01.
  • ToDate(22222222) - Returns 1970-01-01.
  • ToDate("2004 2 19") - Returns 2004-2-19.
  • ToDate("99-5") - Returns 1999-5-1.
  • ToDate("1999-2-29") - Returns null since 1999 is not leap year.
ToDate(Date a) This function returns the specified date.
  • a - A Date value.
A Date value. Suppose the date is July 15, 1999, the return of the following statement is 07/15/99.

ToDate(ToDate(1999, 7, 15))

ToDate(Datetime a) This function extracts the date portion of a specified DateTime.
  • a - A DateTime value.
A Date value. Suppose the DateTime is July 15,1999 12:20:30, the return value of the following statement is 07/15/99.

ToDate(ToDateTime(1999, 7, 15, 12, 20, 30 ) )

ToDate(integer d) This function converts the argument d (a millisecond value represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT) to a date value.
  • d - A millisecond value, a DBField value or a parameter. It should be a long integer.

Note: When the millisecond value is larger than 2³², you should use DBField or parameter instead.

A Date value. The return value of the following statement is 01/02/70.

ToDate(72000000)

ToDate(integer y, Integer m, Integer d) This function creates a Date value from arguments y, m and d.
  • y - An Integer value indicating year.
  • m - An Integer value indicating month.
  • d - An Integer value indicating day.
A Date value. The return value of the following statement is 07/15/99.

ToDate(1999, 7, 15)

ToDate(integer y, Integer m, Integer d, String timezone) This function creates a Date value from arguments y, m, d, and timezone.
  • y - An Integer value indicating year.
  • m - An Integer value indicating month.
  • d - An Integer value indicating day.
  • timezone - A String value indicating the time zone. For the detailed usage, refer to java.util.TimeZone.
A Date value. The return value of the following statement is 9/27/2018.

ToDate(2018, 9, 27, "GMT")

ToDateTime(number or string) This function use IsDateTime(number or string) to check whether the number/string is right or not. If it is right, the function returns a DateTime value between 100-1-1 and 9999-12-31, otherwise the function returns null.
  • number - A Number value or expression to be tested for convertibility to a DateTime value. It can be positive, negative or fractional. It is interpreted as a number of days from Jan 1, 1900.
  • string - A String value or expression to be tested for convertibility to a DateTime value. Many forms are accepted.
A DateTime value or null.
  • ToDateTime(255.35) - Returns 1900-09-12 08:23:59.
  • ToDateTime("Jan 3, 2004") - Returns 2004-01-03 00:00:00.
  • ToDateTime("5:25:26pm") - Returns 2004-02-23 17:25:26 because the current date is 2004-2-23.
  • ToDateTime("2003-2-29 15:26:25") - Returns null because the given String value is not right DateTime value.
  • ToDateTime("2004 *2 *29 15:26:25") - Returns null because the given String format is not right DateTime format.
ToDateTime(date a)  
  • a - A Date value that can be converted to a DateTime type value by this function.
A DateTime value. ToDateTime(ToDate("2004-2-23")) - Returns 2004-02-23 00:00:00.
ToDateTime(Date a, Time b) This function creates a DateTime value from arguments a and b.
  • a - A Date value.
  • b - A Time value.
A DateTime value. Suppose the date is July 15,1999 and the time is 12:20:30, the return value of the following statement is 1999-07-15 12:20:30.

ToDateTime(ToDate(1999, 7, 15), ToTime(12, 20, 30))

ToDateTime(Integer d) This function converts the argument d (a millisecond value represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT) to a DateTime value. The function calculating is based on the time zone. You must adjust argument d to reflect your desired time zone.
  • d - A millisecond value, a DBField value or a parameter. It should be a long integer.

Notes:

  • In this function, you can decide the display format by selecting the format from the Report Inspector. The format of the example above is yyyy-mm-dd hh:mm:ss.
  • When the millisecond value is larger than 2³² (4294967296), you should use DBField, parameter or assign integers to variables.
A DateTime value.
  • The time zone is UTC+08:00. The return value of the following statement is 11/9/2016 3:10:17 AM.

    Integer i = 1478632217;
    Integer j = 1000;

    ToDateTime(I * j)

  • The time zone is UTC+08:00. The return value of the following statement is 1/2/1970 04:00:00 AM.

    ToDateTime(72000000)

ToDateTime(Integer y, Integer m, Integer d) This function creates a DateTime value from arguments y, m and d.
  • y - An Integer value indicating year.
  • m - An Integer value indicating month.
  • d - An Integer value indicating day.

Note: In this function, because you only specify year, month and day, the system will output 12:00:00 as the default time. Also you can decide the display format by selecting the format from the Report Inspector. The format of the example above is yyyy-mm-dd hh:mm:ss.

A DateTime value. Suppose the date is July 15,1999, the return value of the following statement is 1999-07-15 12:00:00.

ToDateTime(1999, 7, 15)

ToDateTime(Integer y, Integer m, Integer d, Integer h, Integer i, Integer s) This function creates a DateTime value from arguments y, m, d, h, i and s.
  • y - An Integer value indicating year.
  • m - An Integer value indicating month.
  • d - An Integer value indicating day.
  • h - An Integer value indicating hour.
  • i - An Integer value indicating minute.
  • s - An Integer value indicating second.
A DateTime value. Suppose the date is July 15,1999 and time is 8:05:30, the return value of the following statement is 1999-07-15 08:05:30.

ToDateTime(1999, 7, 15, 8, 5, 30)

ToDateTime(Integer y, Integer m, Integer d, Integer h, Integer i, Integer s, String timezone) This function creates a DateTime value while converting the time zone from arguments y, m, d, h, i, s, and timezone.
  • y - An Integer value indicating year.
  • m - An Integer value indicating month.
  • d - An Integer value indicating day.
  • h - An Integer value indicating hour.
  • i - An Integer value indicating minute.
  • s - An Integer value indicating second.
  • timezone - A String value indicating the time zone. For the detailed usage, refer to java.util.TimeZone.
A DateTime value. The return value of the following statement is 10/19/2018 6:23:54 PM.

ToDateTime(2018,10,19,10,23,54, "UTC")

Today() This function returns the current date in a report.   A Date value. If the current date is Oct. 11, 1999, the return value of the following statement is 10/11/99.

Today()

ToTime(number or string) This function uses IsTime(number or string) to check whether the number/string is right or not. If the number/string is right, the function returns a Time value between 00:00:00 and 23:59:59, otherwise it will return null.
  • number - A Number value or expression to be tested for convertibility to a Time value. It can be positive, negative or fractional. It is interpreted as units of 24 hours.
  • string - A String value or expression to be tested for convertibility to a Time value. Many forms are accepted.
A Time value or null.
  • ToTime(0.2) - Returns 04:48:00.
  • ToTime(-0.2) - Returns 19:12:00.
  • ToTime(50) - Returns 00:00:00.
  • ToTime("10:34:25") - Returns 10:34:25.
  • ToTime("2:25pm") - Returns 02:25:00.
  • ToTime("2004-2-20") - Returns 00:00:00.
  • ToTime("24:25:23") - Returns 00:25:23.
  • ToTime("20*23:25") - Returns null since the given String format is not time format.
ToTime(DateTime a) This function extracts the time portion from a specified DateTime value.
  • a - A DateTime value.
A Time value. Suppose the date is July 15,1999 and time is 10:55:45. The return value of the following statement is 10:55:45.

ToTime(ToDateTime(1999, 7, 15, 10, 55, 45 ))

ToTime(Integer t) This function converts the argument t (a millisecond value represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT) to a Time value.
  • t - A millisecond value, a DBField value or a parameter. It should be a long integer.

Note: When the millisecond value is larger than 2³², you should use DBField or parameter instead.

A Time value. The return value of the following statement is 08:00:01.

ToTime(1000)

ToTime(Integer a, Integer b, Integer c) This function creates a Time value from arguments a, b and c.
  • a - An Integer value, indicating hours.
  • b - An Integer value, indicating minutes.
  • c - An Integer value, indicating seconds.
A Time value. The return value of the following statement is 10:10:10.

ToTime(10, 10, 10)

ToTime(Integer a, Integer b, Integer c, String timezone) This function creates a Time value from arguments a, b, c and timezone.
  • a - An Integer value, indicating hours.
  • b - An Integer value, indicating minutes.
  • c - An Integer value, indicating seconds.
  • timezone - A String value indicating the time zone. For the detailed usage, refer to java.util.TimeZone.
A Time value. The return value of the following statement is 4:20:15 PM.

ToTime(8, 20, 15, "GMT")

WeekdayName(weekday, abbreviate, firstDayOfWeek)

Overloads:

  • WeekdayName(weekday)
  • WeekdayName(weekday, abbreviate)
  • WeekdayName(weekday, abbreviate, firstDayOfWeek)
This function returns a string indicating the name of the specified day of the week.
  • Weekday - The numeric designation for the day of the week.
  • abbreviate - An optional Boolean value that indicates whether or not the weekday name is to be abbreviated.
  • firstDayOfWeek - An optional number indicating the first day of the week.
    • jrUseSystem - 0 (Use the current system date)
    • jrSunday - 1
    • jrMonday - 2
    • jrTuesday - 3
    • jrWednesday - 4
    • jrThursday - 5
    • jrFriday - 6
    • jrSaturday - 7
A String value.
  • WeekdayName (2) - Returns String value Monday.
  • WeekdayName (1) - Returns String value Sunday.
  • WeekdayName (4, true) - Returns String value Wed.
  • WeekdayName (4, false) - Returns String value Wednesday.
  • WeekdayName (3, true, "jrMonday") - Returns Wed, since abbreviation is selected and the first day of the week is specified to be Monday.
  • WeekdayName (3, true, "jrUseSystem") - If the current day is Friday, the function returns Sun, since the first day of week is specified to the current day, and the third day from Friday is Sunday.
WeekFrom1970() This function computes the total number of weeks from 1970 to the current date.   An Integer value. If the current date is Oct. 13,1999, the return value of the following statement is 1554.

WeekFrom1970()

WeekFrom1970(Date, a) This function computes the total number of weeks from 1970 to a specified date.
  • a - A Date value.
An Integer value. Suppose the date is July 8,1995. The return value of the following statement is 1331.

WeekFrom1970(ToDate(1995, 7, 8))

WeekFrom1970(DateTime, a) This function computes the total number of weeks from 1970 to a specified DateTime.
  • a - A DateTime value.
An Integer value. Suppose the DateTime is Jan. 2,1988 10:40:50. The return value of the following statement is 940.

WeekFrom1970(ToDateTime(1988, 1, 2, 10, 40, 50))

WeekOfMonth() This function returns the week of the current month.   An Integer value. If the current date is Oct. 13,1999, the return value of the following statement is 3.

WeekOfMonth()

WeekOfMonth(Date a) This function returns the week of the month of a specified date.
  • a - A Date value.
An Integer value. If the current date is Oct. 13,1999, the return value of the following statement is 3.

WeekOfMonth(ToDate(1999, 10, 13))

WeekOfMonth(DateTime a) This function evaluates the week of the month of a specified DateTime.
  • a - A DateTime value.
An Integer value. Suppose the date time is Jan. 20,1999 10:40:50. The return value of the following statement is 4.

WeekOfMonth(ToDateTime(1999, 1, 20, 10, 40, 50))

WeekOfYear() This function returns the week of the current year.   An Integer value. If the current date is Oct. 13,1999, the return value of the following statement is 42.

WeekOfYear()

WeekOfYear(Date a) This function returns the week of the year in a specified date.
  • a - A Date value.
An Integer value. Suppose the date is July 8,1999. The return value of the following statement is 28.

WeekOfYear(ToDate(1999, 7, 8))

WeekOfYear(DateTime a) This function returns the week of the year in a specified DateTime.
  • a - A DateTime value.
An Integer value. Suppose the date time is Feb. 4,1999 10:40:50. The return value of the following statement is 6.

WeekOfYear(ToDateTime(1999, 2, 4, 10, 40, 50))

Year() This function returns the current year.   An Integer value. If the current year is 1999, the return value of the following statement is 1999.

Year()

Year(Date a) This function extracts the year portion of a specified date.
  • a - A Date value.
An Integer value. Suppose the date is Jan.15,1995. The return value of the following statement is 1995.

Year(ToDate(1995, 1, 15))

Year(DateTime a) This function extracts the year portion of a specified DateTime value.
  • a - A DateTime value.
An Integer value. Suppose the date time is July 15,1988 10:40:50. The return value of the following statement is 1988.

Year(ToDateTime(1988, 7, 15, 10, 40, 50))

Back to top

Financial

Function Description Parameter Return Value Example
DDB(cost, salvage, life, period, factor)

Overloads:

  • DDB(cost, salvage, life, period)
  • DDB(cost, salvage, life, period, factor)
This function returns a number specifying the depreciation of an asset for a specific time period, using the double-declining balance method or another method as specified by the factor argument.
  • cost - A Number or Currency that specifies the initial cost of the asset. The value is nonnegative and greater than or equal to salvage.
  • salvage - A Number or Currency that specifies the value of the asset at the end of its useful life. The value is nonnegative.
  • life - A positive Number that specifies the length of the useful life of the asset.
  • period - A Number that specifies the period for which asset depreciation is calculated. The value is positive and less than or equal to life. The parameters life and period must have the same units.
  • factor - An optional positive number that specifies the rate at which the balance declines. If omitted, 2 (double-declining method) will be used.
A Number value. Suppose a company purchases a fleet of cars for $560,000. The cars have a lifetime of 12 years and a salvage value of $30,000. They are depreciated using the double-declining method.
  • DDB(560000, 30000, 12, 1) - Returns 93333.33. The first year's depreciation is $93,333.33.
  • DDB(560000, 30000, 13, 5) - Returns 44164.37. The fourth year's depreciation is $44,164.37.
  • DDB(560000, 30000, 13, 13) - Returns 11605.58. The final year's depreciation is $11,605.58.
FRAccRecTurnover(Number accountReceivable, Number sales, DbDouble numOfDays) This function computes the turnover of the account receivable.
  • accountReceivable - A Double value indicating the account receivable.
  • sales - A Double value indicating the sales.
  • numOfDays - A Double value indicating the number of days.
A Number value.
  • If the account receivable is 220000.00, the sales value is 450000.00, the number of days is 100, the return value of the following statement is 48.89.

    FRAccRecTurnover(220000.00, 450000.00, 100)

  • FRAccRecTurnover(10000,100000,360) - Returns 36 days.
FRCashFlowVsTotalDebt(Number cashFlow,Number totalDebt) This function returns the result of cash flow vs total debt.
  • cashFlow - A Double value indicating the cash flow.
  • totalDebt - A Double value indicating the total debt.
A Number value. If the cash flow is 250000.00, the total debt is 280000.00, the return value of the following statement is 0.89.

FRCashFlowVsTotalDebt(250000.00, 280000.00)

FRCurrentRatio(Number curAssets, Number curLiabilities) This function computes the current ratio.
  • curAssets - A Double value indicating the current ratio.
  • curLiabilities - A Double value indicating the current liabilities.
A Number value. If the current assets are 1800000.00, the current liabilities are 150000.00, the return value of the following statement is 12.00.

FRCurrentRatio(1800000.00, 150000.00)

FRDebtEquityRatio(Number totalLiabilities, Number totalEquity) This function computes the debt equity ratio.
  • totalLiabilities - A Double value indicating the total liabilities.
  • totalEquity - A Double value indicating the total equity.
A Number value. If the total liabilities are 175000.00, the total equity is 215000.00, the return value of the following statement is 0.81.

FRDebtEquityRatio(175000.00, 215000.00)

FRDividendYield(Number dividEnd, Number marketPrice) This function computes the dividend yield.
  • dividend - A Double value indicating the dividend.
  • marketPrice - A Double value indicating the market price.
A Number value. If the dividend is 9.85, the market price is 10.87, the return value of the following statement is 0.91.

FRDividendYield(9.85, 10.87)

FREarningsPerCommonShare(Number netProfit, Number preferredDividend, DbDouble numOfCommonShare) This function computes the earnings of per common share.
  • netProfit - A Double value indicating the net profit.
  • preferredDividend - A Double value indicating the preferred dividend.
  • numOfCommonShare - A Double value indicating the number of common shares.
A Number value. If the net profit is 300000.00, the preferred dividend is 180000.00, the number of common shares is 10000, the return value of the following statement is 12.

FREarningsPerCommonShare(300000.00, 180000.00, 10000)

FREquityVsTotalAssets(Number totalEquity, Number totalAssets) This function returns the result of equity vs. total assets.
  • totalEquity - A Double value indicating the total equity.
  • totalAssets - A Double value indicating the total assets.
A Number value. If the total equity is 215000.00, the total assets are 2200000.00, the return value of the following statement is 0.10.

FREquityVsTotalAssets(215000.00, 2200000.00)

FRGrossProfitMargin(Number grossProfit, Number sales) This function is used to compute the gross profit margin.
  • grossProfit - A Double value indicating the gross profit.
  • sales - A Double value indicating the sales.
Number value. If the gross profit is 350000.00, the sales value is 450000.00, the return value of the following statement is 0.78.

FRGrossProfitMargin(350000.00, 450000.00)

FRInterestCoverage(number cashFlow, number interestExpenses) This function computes the interest coverage.
  • cashFlow - A Double value indicating the cash flow.
  • interestExpenses - A Double value indicating the interest expenses.
A Number value. If the cash flow is 250000.00, the interest expenses are 350000.00, the return value of the following statement is 0.71.

FRInterestCoverage(250000.00, 350000.00)

FRInventoryTurnover(Number inventory, Number sales, Number numOfDays) This function computes the turnover of the inventory.
  • inventory - A Double value indicating the inventory.
  • sales - A Double value indicating the sales.
  • numOfDays - A Double value indicating the number of days.
A Number value. If the inventory is 165000.00, the sales value is 450000.00, the number of days is 100, the return value of the following statement is 36.67.

FRInventoryTurnover(165000.00, 450000.00, 100)

FRNetProfitMargin(Number netProfit, Number sales) This function is used to compute the net profit margin.
  • netProfit - A Double value indicating the net profit.
  • sales - A Double value indicating the sales.
A Number value. If the net profit is 300000.00, the sales value is 450000.00, the return value of the following statement is 0.67.

FRNetProfitMargin(300000.00, 450000.00)

FROperatingProfitMargin(Number operatingProfit, Number sales) This function computes the operating profit margin.
  • operatingProfit - A Double value indicating the operating profit.
  • sales - A Double value indicating the sales.
A Number value. If the operating profit is 380000.00, the sales value is 450000.00, the return value of the following statement is 0.84.

FROperatingProfitMargin(380000.00, 450000.00)

FRPriceEarningsRatio(Number marketPrice, Number earningsPerShare) This function computes the price earning ratio.
  • marketPrice - A Double value indicating the market price.
  • earningPerShare - A Double value indicating the earning per share.
A Number value. If the market price is 10.87, the earning per share is 12, the return value of the following statement is 0.91.

FRPriceEarningsRatio(10.87, 12)

FRQuickRatio(Number curAssets, Number inventories, Number curLiabilities) This function computes the quick ratio.
  • curAssets - A Double value indicating the current assets.
  • inventories - A Double value indicating the inventory.
  • curLiabilities - A Double value indicating the current liabilities.
A Number value. If the current assets are 1800000.00, the inventory is 165000.00, the current liabilities are 150000.00, the return value of the following statement is 10.90.

FRQuickRatio(1800000.00, 165000.00, 150000.00)

FRReturnOnCommonEquity(Number netProfit, Number preferredDividend, Number commonEquity) This function returns the result of return on common equity.
  • netProfit - A Double value indicating the net profit.
  • preferredDividend - A Double value indicating the preferred dividend.
  • commonEquity - A Double value indicating the common equity.
A Number value. If the net profit is 300000.00, the preferred dividend is 180000.00, the common equity is 208000.00, the return value of the following statement is 0.58.

FRReturnOnCommonEquity(300000.00, 180000.00, 208000.00)

FRReturnOnEquity(Number netProfit, Number totalEquity) This function returns the results of return on equity.
  • netProfit - A Double value indicating the net profit.
  • totalEquity - A Double value indicating the total equity.
A Number value. If the net profit is 300000.00, the total equity is 215000.00, the return value of the following statement is 1.40.

FRReturnOnEquity(300000.00, 215000.00)

FRReturnOnInvestedCapital(Number netProfit, Number totalBankDebts, Number totalEquity) This function returns the result of return on invested capital.
  • netProfit - A Double value indicating the net profit.
  • totalBankDebts - A Double value indicating the total bank debts.
  • totalEquity - A Double value indicating the total equity.
A Number value. If the net profit is 300000.00, the total bank debts are 100000.00, the total equity is 215000.00, the return value of the following statement is 0.95.

FRReturnOnInvestedCapital(300000.00, 100000.00, 215000.00)

FRReturnOnNetFixedAssets(Number netProfit, Number netFixedAssets) This function returns the result of return on net fixed assets.
  • netProfit - A Double value indicating the net profit.
  • netFixedAssets - A Double value indicating the net fixed assets.
A Number value. If the net profit is 300000.00, the net fixed assets are 400000.00, the return value of the following statement is 0.75.

FRReturnOnNetFixedAssets(300000.00, 400000.00)

FRReturnOnTotalAssets(Number netProfit, Number totalAssets) This function returns the results of return on total assets.
  • netProfit - A Double value indicating the net profit.
  • totalAssets - A Double value indicating the total assets.
A Number value. If the net profit is 300000.00, the total assets are 2200000.00, the return value of the following statement is 0.14.

FRReturnOntotalAssets(300000.00, 2200000.00)

FV(rate, periods, payment, presentMoney, type)

Overloads:

  • FV(rate, periods, payment)
  • FV(rate, periods, payment, presentMoney)
  • FV(rate, periods, payment, presentMoney, type)
This function returns a number specifying the future value of an annuity based on periodic, fixed payments and a fixed interest rate.
  • rate - A Number that specifies the interest rate per period.
  • periods - A positive Number that specifies the total number of payment periods in the annuity. The units used for specifying rate and periods must consistent. For example, if periods is the number of periods in months, then rate is a monthly interest rate.
  • payment - A Number or Currency that specifies the payment to be made each period.
  • presentMoney - An optional Number or Currency that specifies the present value of a series of future payments.
  • type - An optional Number that specifies when payments are due. Specify 0 if payments are due at the end of the payment period, and 1 if payments are due at the beginning of the period. If omitted, 1 will be used.
A Number value.
  • Suppose that you put $1100 per month into a retirement savings plan that pays 6 percent annual interest, compounded monthly. How much will the account be worth after 20 years?

    FV(0.06 / 12, 20 * 12, -1100) - Returns 507144.99. So your account will have $507,145. The payment (-1100) is negative since you are paying out the money to the plan.

    The above example assumes that you make your payments into the plan at the end of the month. Thus, after the first month, your plan would have $1100 in it, since there wouldn't have been enough time for any interest to accrue.

  • Suppose that instead, you make your payments at the start of the month,

    FV(0.06 / 12, 20 * 12, -1100, 0, 1) - Returns 510786.21 (rounded to the nearest integer). So your account will have $510,786. You will save $3,642 more by depositing at the beginning of the month.

  • Now suppose that in addition to making payments at the start of the month, you start your plan with an initial deposit of $25,000,

    FV(0.06 / 12, 20 * 12, -1100, -25000, 1) - Returns 593541.33 (rounded to the nearest integer). Your account will have $ 593,541 after 20 years.

  • You can also use the FV function to calculate the future value of a lump sum deposit. For example, if you deposit $25,000 into a plan that pays 6 percent annual interest compounded monthly for 20 years,

    FV(0.06 / 12, 20 * 12, 0, -25000) - Returns 82755.12 (rounded to the nearest integer). You would have $82755.12 in your account.

IPmt(rate, period, periods, presentMoney, futureMoney, type)

Overloads:

  • IPmt(rate, period, periods, presentMoney)
  • IPmt(rate, period, periods, presentMoney, futureMoney)
  • IPmt(rate, period, periods, presentMoney, futureMoney, type)
This function returns a number specifying the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.
  • rate - A Number that specifies the interest rate per period.
  • period - A Number that specifies the payment period in the range 1 through periods.
  • periods - A positive Number that specifies the total number of payment periods in the annuity. The units used for specifying rate, period and periods must be consistent. For example, if periods is the number of periods in months, then rate is a monthly interest rate and period specifies a month.
  • presentMoney - A Number or Currency that specifies the present value, or value today, of a series of future payments or receipts.
  • futureMoney - An optional Number or Currency that specifies the future value, or cash balance you want after you've made the final payment. If omitted, 0 will be used.
  • type - An optional Number that specifies when payments are due. Specify 0 if payments are due at the end of the payment period, and 1 if payments are due at the beginning of the period. If omitted, 0 will be used.
A Number value.
  • Suppose that you want to take out a $250,000 loan payable monthly over 15 years at an annual interest rate of 6.5 percent. The following formula returns the amount of interest that you pay in your first loan payment. Note that the monthly interest rate is 0.065 / 12 and the number of months of the loan is 15 * 12.

    IPmt(0.065 / 12, 1, 15 * 12, 250000) - Returns the Number value -1354.17 (rounded to 2 decimals). The value is negative because it represents a payment out from you, whereas the loan amount of $250,000 is positive because it represents a payment in to you.

  • The following formula returns the amount of interest that you pay in your 97th payment (after 10 years of payments).

    IPmt(0.065 / 12, 9*12 + 1, 15 * 12, 250000) - Returns -701.74 (rounded to 2 decimals). This shows that you would be making progress on the loan at this stage, with less of your monthly payment paying the interest.

IRR(values, guess)

Overloads:

  • IRR(values)
  • IRR(values, guess)
This function returns a number specifying the internal rate of return for a series of periodic cash flows (payments and receipts).
  • values - A Number or Currency type array that specifies cash flow values. The array must contain at least one negative value (a payment) and one positive value (a receipt). The cash flows must occur at regular intervals such as monthly or yearly.
  • guess - An optional Number value that is estimated to be returned by IRR. If omitted, guess is 0.1 (10 percent).

Note: The NPV and IRR functions are related since NPV (IRR (values), values) = 0. That is, the internal rate of return of a sequence of cash flows is the interest rate for which that sequence of cash flows has a net present value of 0. There is no direct formula for the IRR function and so Logi Report calculates the value by iteration. The process depends on the initial guess for the internal rate of return. If the program reports an error, try changing the value of the guess argument to be closer to what you expect the internal rate of return to be.

A Number value. Suppose that you can choose one of two offers: $20,000 now or guaranteed payments of $5,000 after 1 year, $10,000 after 2 years and $15,000 after 3 years. Which is the better offer? One way to quantify this is to calculate the internal rate of return. If you take the second offer, you cannot take the first, which is like experiencing an initial payment of $20,000 followed by the receipts:

inArray = ["-20000", "15000", "-10000", "25000"];
IRR(inArray)

Returns 0.201 (rounded to 3 decimals) or 20.1 percent interest. With all other things being equal, if you think that 20.1 percent is a good rate of return, you would prefer the second offer.

MIRR(valueArray, financeRate, reinvestRate) This function returns a number specifying the modified internal rate of return for a series of periodic cash flows (payments and receipts).
  • valueArray - A Number or Currency type array that specifies cash flow values. The array must contain at least one negative value (a payment) and one positive value (a receipt). The cash flows must occur at regular intervals such as monthly or yearly.
  • financeRate - A Number that specifies the interest rate paid as the cost of financing.
  • reinvestRate - A Number that specifies the interest rate received on gains from cash reinvestment.
A Number value. Suppose that you run a business that makes equipment investments, which results in a loss in the first and fourth years. Your expected annual returns are: -$60,000, $60,000, $45,000, -$50,000, $65,000, $40,000. Your losses are financed at 10 percent while you reinvest your earnings in an account at 6 percent. The modified internal rate of return is:

MIRR([-60000, 60000, 45000, -50000, 65000, 40000], 0.10, 0.06) - Returns 0.1929 (rounded to 4 decimals) or 19.29 percent.

NPer(rate, payment, presentMoney, futureMoney, type)

Overloads:

  • NPer (rate, payment, presentMoney)
  • NPer (rate, payment, presentMoney, futureMoney)
  • NPer (rate, payment, presentMoney, futureMoney, type)
This function returns a number specifying the number of periods for an annuity based on periodic, fixed payments and a fixed interest rate.
  • rate - A Number that specifies the interest rate per period.
  • payment - A Number or Currency that specifies the payment to be made each period. Payments usually contain principal and interest that doesn't change over the life of the annuity.
  • presentMoney - A Number or Currency that specifies the present value, or value today, of a series of future payments or receipts.
  • futureMoney - An optional number or Currency that specifies the future value or cash balance you want after you've made the final payment. If omitted, 0 will be used.
  • type - An optional cumber that specifies when payments are due. Specify 0 if payments are due at the end of the payment period, and 1 if payments are due at the beginning of the period. If omitted, 0 will be used.
A Number value.
  • Suppose that you want a $250,000 loan to buy a house. The interest rate is 6.5 percent and you can afford to pay $3,000 per month. How long a mortgage would you need?

    NPer(0.075/12, -3000, 250000) - Returns 118.08 (rounded to 2 decimals) months.

  • Rather than seeking a loan, suppose that you want to save $500,000 and then buy the house. You can get a 6.5 percent interest rate compounded monthly from your savings plan, and you want to save $3,000 per month. How long would you need to save before you had the required amount of money?

    NPer(0.065/12, -3000, 0, 250000) - Returns 68.96 (rounded to 2 decimals) months.

NPV(rate, values) This function returns a number specifying the net present value of an investment based on a series of periodic cash flows (payments and receipts) and a discount rate.
  • rate - A Number that specifies the discount rate over the length of the period, expressed as a decimal.
  • value - A Number or Currency type array that specifies cash flow values. Negative values represent payments and positive values receipts. The cash flow must occur at regular intervals, such as monthly or yearly.

Note: The NPV and IRR functions are related since NPV (IRR (values), values) = 0, which means that the internal rate of the return of a sequence of cash flow is the discount rate for which that sequence of cash flow has a net present value of 0.

A Number value. Suppose that someone offers to pay you $15000 after 1 year, $20000 after 2 years, $25000 after 3 years and $12000 after 4 years. If the discount rate (the Time value of money) is 6.5 percent, the value of this offer to you today is:

NPV(0.065, [15000, 20000, 25000, 12000])

The formula returns 61741.8. So this scheme is worth $61,741.8 to you today. This is less than the sum of the payments, which is $72000, since you have to wait for this money.

Pmt(rate, periods, presentMoney, futureMoney, type)

Overloads:

  • Pmt(rate, periods, presentMoney)
  • Pmt(rate, periods, presentMoney, futureMoney)
  • Pmt(rate, periods, presentMoney, futureMoney, type)
This function returns a number specifying the payment for an annuity based on periodic, fixed payments and a fixed interest rate. To find the total amount paid out over the whole loan, multiply the payment per period (the value returned by Pmt) by the total number of periods.
  • rate - A Number that specifies the interest rate per period.
  • periods - A positive Number that specifies the total number of payment periods in the annuity. The units used for specifying rate and periods must be consistent. For example, if periods is the number of periods in months, then rate will be a monthly interest rate.
  • presentMoney - A Number or Currency that specifies the present value or principal. That is, the amount that a series of payments in the future is worth now.
  • futureMoney - An optional Number or Currency that specifies the future value or cash balance you want after you've made the final payment. If omitted, 0 will be used.
  • type - An optional Number that specifies when payments are due. Specify 0 if payments are due at the end of the payment period, and 1 if payments are due at the beginning of the period. If omitted, 0 will be used.
A Number value.
  • Suppose that you want to take out a $250,000 loan payable monthly over 15 years at an annual interest rate of 6.5 percent. The following formula returns your monthly loan payment. Note that the monthly interest rate is 0.065 / 12, and the number of months of the loan is 15 * 12.

    Pmt(0.065 / 12, 15 * 12, 250000) - Returns the Number value -2177.77 (rounded to 2 decimals). The value is negative because it represents a payment out from you whereas the loan amount of $250,000 is positive because it represents a payment in to you.

  • Now suppose that the payments are made at the beginning of the month instead of the end (default). Your monthly loan payment is calculated as:

    Pmt(0.065 / 12, 15 * 12, 250000, 0, 1) - Returns -2166.04 (rounded to 2 decimals). Note that your monthly payment is about $11.73 less each month than in the previous example where payments are made at the end of the month.

  • Now suppose that you know that you'll receive $80,000 in 15 years. In this case, there is no need to fully pay off the loan. You only need to reduce the amount owed to $80,000 after 15 years. Note that the future value is negative since after 15 years you will need to pay out $80,000 to clear the loan. Your monthly loan payment is calculated as:

    Pmt(0.065 / 12, 15 * 12, 250000, -80000) - Returns -1914.22 (rounded to 2 decimals).

PPmt(rate, period, periods, presentMoney, futureMoney, type)

Overloads:

  • PPmt(rate, period, periods, presentMoney)
  • PPmt(rate, period, periods, presentMoney, futureMoney)
  • PPmt(rate, period, periods, presentMoney, futureMoney, type)
 
  • rate - A Number that specifies the interest rate per period.
  • period - A Number that specifies the payment period in the range 1 through periods.
  • periods - A positive Number that specifies the total number of payment periods in the annuity. The units used for specifying rate, period and periods must be consistent. For example, if periods is the number of periods in months, then rate is a monthly interest rate and period specifies a month.
  • presentMoney - A Number or Currency that specifies the present value, or value today, of a series of future payments or receipts.
  • futureMoney - An optional Number or Currency that specifies the future value or cash balance you want after you've made the final payment. If omitted, 0 will be used.
  • type - An optional Number that specifies when payments are due. Specify 0 if payments are due at the end of the payment period, and 1 if payments are due at the beginning of the period. If omitted, 0 will be used.
A Number value.
  • Suppose that you want to take out a $250,000 loan payable monthly over 15 years at an annual interest rate of 6.5 percent. The following formula returns the amount of principal that you pay in your first loan payment. Note that the monthly interest rate is 0.065 / 12 and the number of months of the loan is 15 * 12.

    PPmt (0.065 / 12, 1, 15 * 12, 250000) - Returns the Number value -823.6 (rounded to 2 decimals). The value is negative because it represents a payment out from you whereas the loan amount of $250,000 is positive because it represents a payment in to you.

  • The following formula returns the amount of principal that you pay in your 121st payment (after 10 years of payments):

    PPmt (0.065 / 12, 9*12 + 1, 15 * 12, 250000) - Returns -1476.03 (rounded to 2 decimals).

    You've made progress on the loan and are paying off more of the principal per payment. This is because less interest can accrue each month since more of the loan is paid off so that your fixed monthly payment is applied more to the principal.

PV(rate, periods, payment, futureMoney, type)

Overloads:

  • PV (rate, periods, payment)
  • PV (rate, periods, payment, futureMoney)
  • PV (rate, periods, payment, futureMoney, type)
This function returns a number specifying the present value of an annuity based on periodic, fixed payments to be paid in the future and at a fixed interest rate.
  • rate - A Number that specifies the interest rate per period.
  • periods - A positive Number that specifies the total number of payment periods in the annuity. The units used for specifying rate and periods must be consistent. For example, if periods is the number of periods in months, rate will then be a monthly interest rate.
  • payment - A Number or Currency that specifies payment to be made each period.
  • futureMoney - an optional Number or Currency that specifies the future value or cash balance you want after you've made the final payment. If omitted, 0 will be used.
  • type - An optional Number that specifies when payments are due. Specify 0 if payments are due at the end of the payment period, and 1 if payments are due at the beginning of the period. If omitted, 0 will be used.
A Number value. Suppose that you want to buy a condo and can make payments of $1100 twice a month (24 annual payments). If the mortgage rates are 6.5 percent, and you want to pay off the condo in 10 years, what is the maximum loan that you can take out?

PV(0.065 / 24, 10 * 24, -1100) - Returns 193936 (rounded to the nearest dollar).

You can therefore afford a loan of about $194,000. Notice that the payment argument is negative since you are paying out the money each month.

Rate(periods, payment, presentMoney, futureMoney, type, guess)

Overloads:

  • Rate(periods, payment, presentMoney)
  • Rate(periods, payment, presentMoney, futureMoney)
  • Rate(periods, payment, presentMoney, futureMoney, type)
  • Rate(periods, payment, presentMoney, futureMoney, type, guess)
This function returns a number specifying the interest rate per period for an annuity. The units of the returned value are consistent with the units of periods. For example, if periods is in months, then the rate returned will be a monthly interest rate.
  • periods - A positive Number that specifies the total number of payment periods in the annuity.
  • payment - A Number or Currency that specifies the payment that is to be made for each period.
  • presentMoney - A Number or Currency that specifies the present value, or value today, of a series of future payments or receipts.
  • futureMoney - An optional Number or Currency that specifies the future value or cash balance you want after you've made the final payment. If omitted, 0 will be used.
  • type - An optional Number that specifies when payments are due. Specify 0 if payments are due at the end of the payment period, and 1 if payments are due at the beginning of the period. If omitted, 0 will be used.
  • guess - An optional Number value estimated to be returned by Rate. If omitted, guess is 0.1 (10 percent).

Note: There is no direct formula for the Rate function and so Reports calculates the value by iteration. The process depends on the initial guess for the rate. If the program reports an error, try changing the value of the guess argument to be closer to what you expect the interest rate to be.

 

A Number value.
  • An electronics store offers to finance a $12500 television for $560 per month, over 2 years, with no money down. Is this a good deal? The first step in determining this is to figure out what interest rate the store is charging.

    Rate(2 * 12, -560, 12500) - Returns 0.00588 (rounded to 5 decimals). Notice that periods is 24 months, payment (-560) is negative since monthly payments are being paid, and the present value (12500) is positive since at the start of the loan, $12500 was effectively received (the value of the television). The interest rate returned is a monthly interest rate, since periods is in months.

  • This next expression calculates the interest rate, expressed as a yearly interest rate and as a percent.

    Rate(2 * 12, -560, 12500) * 12 * 100 - Returns 7.06 (rounded to 2 decimals). Thus, the store is charging an effective annual interest rate of 7.06 percent.

SLN(cost, salvage, life) This function returns a number specifying the straight-line depreciation of an asset for a single period.
  • cost - A Number or Currency that specifies the initial cost of the asset.
  • salvage - A Number or Currency that specifies the value of the asset at the end of its useful life.
  • life - A Number that specifies the length of the useful life of the asset. It must not equal 0.
A Number value. Suppose a company purchases a fleet of cars for $560,000. The cars have a lifetime of 12 years and a salvage value of $30,000. The depreciation per year is:

SLN(560000, 30000, 12) - Returns 44166.67. Thus the depreciation per year is $44,166.67.

SYD(cost, salvage, life, period) This function returns a number specifying the sum-of-years' digits depreciation of an asset for a single period.
  • cost - A Number or Currency that specifies the initial cost of the asset.
  • salvage - A Number or Currency that specifies the value of the asset at the end of its useful life.
  • life - A positive Number that specifies the length of the useful life of the asset.
  • period - A Number that specifies the period for which asset depreciation is calculated. The value is positive and less than or equal to life. The Parameters life and period must have the same units.
A Number value. Suppose a company purchases a fleet of cars for $560,000. The cars have a lifetime of 12 years and a salvage value of $30,000. They are depreciated as follows:
  • SYD(560000, 30000, 12, 1) - Returns 80000. The first year's depreciation is $81,538.46.
  • SYD(560000, 60000, 12, 5) - Returns 56000. The fourth year's depreciation is $54,358.97.
  • SYD(560000, 60000, 12, 12) - Returns 8000. The final year's depreciation is $6,794.87.

Back to top

Math

You can use mathematical functions for a variety of mathematics-oriented calculations and operations.

Function Description Parameter Return Value Example
Abs(Integer, a) The function returns the absolute value of an Integer value. If the argument is not negative, the argument will be returned. If the argument is negative, the negation of the argument will be returned.
  • a - An Integer value.
An Integer value. The return value of the following statements is 3534.

Abs(-3534) and Abs(3534)

Abs(Currency, a) The function returns the absolute value of a Currency value. If the argument is not negative, the argument will be returned. If the argument is negative, the negation of the argument will be returned.
  • a - A Currency value.
A Currency value. The return value of the following statement is $3.67.

Abs($3.67) and Abs($-3.67)

Abs(Number, a) The function returns the absolute value of a Number value. If the argument is not negative, the argument will be returned. If the argument is negative, the negation of the argument will be returned.
  • a - A Number value.
A Number value. The return value of the following statements is 2.4785.

Abs(2.4785) and Abs(-2.4785)

atan(Number) The function returns a number specifying the arctangent of the given number parameter. In other words, it returns the angle whose tangent is the given number parameter. This function works in a similar way to the Java Math function with the same name. The range of values returned by atan is between -pi/2 and pi/2 radians.
  • number - A Number value.
A Number value, which is an angle specified in number of radians. atan(1) - Returns an angle of 0.7854 radians (rounded to 4 radians). To convert this angle to degrees multiply by 180 / pi. For instance, atan (1) * 180 / pi is 45 degrees.
Average(DBField a) The function is used to get the average value of the values referred to by the argument.
  • a - Values of DBField or formula field. Can be Number or Currency data type.
A Number or Currency value.
  • Number x = Average(@dbfield)
  • Number x = Average(@formula)
  • Currency x = Average(@dbfield)
  • Currency x = Average(@formula)
Average(DBField a, String groupby) This function is used to get the average value of values referred to by the argument.
  • a - Values of DBField or formula field. Can be Number or Currency data type.
  • groupby - A constant string to indicate the group-by field name or a field variable grouped by or a parameter variable grouped by.
A Number or Currency value, which depends on the data type of the first argument respectively.
  • Number x = Average(@dbfield, @dbfield_groupby)
  • Number x = Average(@dbfield, @formula_groupby)
  • Number x = Average(@dbfield, @parameter_groupby)
  • Currency x = Average(@formula, "group_field")
  • Currency x = Average(@formula, @formula_groupby)
  • Currency x = Average(@formula, @parameter_groupby)
Average(Number a[]) This function computes the average of all the values in the array.
  • a - A Number or Currency array.

Note: If a field is assigned to a variable, for example x, the variable x will lose the characteristic of representing a group of values. The following formula will then be treated as incorrect.

Number x = @dbfield;
Number y = Average(x); // system will prompt you that there is no such kind of function.

 

A Number or a Currency value.
  • The return value of the following statement is 13.5.

    Number x = Average([ 11,12,13,14,15,16 ])

  • The return value of the following statement is -13.5.

    Number x = Average([ -11, -12, -13, -14, -15, -16 ])

  • The return value of the following statement is 11.265.

    Number x = Average([ 11.25,11.26,11.27,11.28 ])

  • The return value of the following statement is -11.265.

    Number x = Average([ -11.25, -11.26, -11.27, -11.28 ])

  • The return value of the following statement is 5.75.

    Currency x = Average([ $5.6, $5.7, $5.8, $5.9 ])

  • The return value of the following statement is -5.75.

    Currency x = Average([ -$5.6, -$5.7, -$5.8, -$5.9 ])

cos(number) The function returns a number specifying the cosine of an angle given in radians. It takes a right-angle triangle, and returns the length of the side adjacent to the specified angle divided by the length of the hypotenuse.
  • number - An angle in number of radians.
A Number value between 1 and -1.
  • cos(1) - Returns 0.54 (rounded to 2 decimals). This is the cosine of 1 radian.
  • cos(60 * pi / 180) - Returns 0.5. This is the cosine of 60 degrees. Before taking the cosine, the angle is converted to radians by multiplying by pi / 180.
Count(Array a[ ]) This function computes the number of the values in an array.
  • a - A Double array.
An Integer value.
  • Count([11.2, 15.4, 13.7, 16.9, 15.2, 14.8, 10.6]) - Returns 7.
  • Count([-20.4, -26.4, -68.7, -84.1]) - Returns 4.
  • Count(["George","Paul","George","John","Ringo","John"]) - Returns 6.
  • Count([11, 15, 13, 16, 15, 14, 10]) - Returns 7.
  • Count([$1, $2, $3.0, $4.5, $45, $67]) - Returns 6.
Count(DBField a) The function is used to count the number of values referred to by the argument.
  • a - Values of DBField or formula field.
A Number value.
  • Number x = Count(@dbfield)
  • Number x = Count(@formula)
Count(DBField a, String groupby) The function is used to count the number of values referred to by the first argument.
  • a - Values of DBField or formula field.
  • groupby - A constant string to indicate the groupby DBField name, formula name grouped by or a parameter name grouped by.

Note: If a field is assigned to a variable, for example x, the variable x will lose the characteristic of representing a group of values. The following formula will then be treated as incorrect.

Number x = @dbfield;
Number y = Count(x); // system will prompt you that there is no such kind of function.

 

A Number value.
  • Number y = Count(@"Customer Name", @Customers_Region)
  • Number x = Count(@formula, @formula_groupby)
  • Number x = Count(@dbfield, @parametger_groupby)
DistinctCount(DBField a) This function computes the number of distinct values referred to by a DBField.
  • a - Values of a DBField or a formula field.
An Integer value. If you build a report about customer order, the return value of the following statement is 19.

DistinctCount(@"Order Date")

DistinctCount(DBField a, String groupby) This function is used to count the number of distinct values referred to by the first argument.
  • a - Values of a DBField or a formula field.
  • groupby - A constant string to indicate the DBField name, formula name or parameter name grouped by.
A Number value.
  • Number x = DistinctCount(@dbfield, @dbfield1)
  • Number x = DistinctCount(@formula, @formula_groupby)
  • Number x = DistinctCount(@dbfield, @parameter_groupby)
DistinctCount(Number a[ ]) This function computes the number of distinct values in an array.
  • a - A Number array.
Note: If a field is assigned to a variable, for example x, the variable x will lose the characteristic of representing a group of values. The following formula will be treated as incorrect.

Number x = @dbfield;
Number y = DistinctCount(x); // system will prompt you that there is no such kind of function.

An Integer value. The return value of the following statement is 4.

DistinctCount([1.45, 2.63, 38.1, 1.45, 23.9])

e()     The Mathematical value of e, which is 2.7182818 (if rounded to 7 decimal places).  
exp(Number) The function returns a number specifying e (the base of natural logarithms) raised to a power. The value of e is approximately 2.7182818284590452354. This function works like the Java Math function with the same name. Numeric overflow occurs if the given number parameter is larger than approximately 705.
  • inNumber - Number value that specifies a power.
A number which is the value of e ^ Number.
  • exp(1.5) - Returns the exponentiation of 1.5, which is approximately 4.4817.
  • exp(10) - Returns 22,026.47.
integDiv(numbera, numberb) The function returns the integer portion of the value numbera/numberb.
  • numbera - A Number value.
  • numberb - A Number value.
A Whole Number value. integDiv(33, 2) - Returns 16.
log(number) The function returns a number specifying the natural logarithm of a given number. The natural logarithm is the logarithm to the base e, where e is approximately 2.7182818284590452354. If the value of number is less than 0, the function will return NaN.
  • number - The Number value that you want to calculate on.
The number which is the value of ln(Number).
  • log(1.5) - Returns 0.406 (rounded to 3 decimals).
  • log(22026.47) - Returns 10.
Maximum(a[ ]) This function returns the highest value in an array.
  • a - An array, data type can be Number or Integer.
A Number or Integer value, which depends on the data type of the parameter a.
  • Maximum([ 22.5, 75.81, 236.47, 56.31, 235.78 ])
  • Maximum([ 4, 9, 34, 80, 200 ])
Maximum(DBField a) This function is used to pick up the maximum value from the values referred to by the argument.
  • a - Values of a DBField or a formula field.
The returned value type is dependent on the data type of the parameter. It may be Number, Currency, String, Date, Time, DateTime or Boolean.
  • Number x = Maximum(@dbfield)
  • Date x = Maximum(@formula)
  • Currency x = Maximum(@dbfield)
  • Boolean x = Maximum(@formula)
Maximum(DBField a, String groupby) This function is used to pick up the maximum value from the values referred to by the first argument.
  • a - Values of a DBField or of a formula field.
  • groupby - A constant string to indicate the DBField name, formula or parameter grouped by.

Note: If a field is assigned to another variable, for example x, the variable x will lose the characteristic of representing a group of values. The following formula will then be treated as incorrect.

Number x = @dbfield;
Number y = Maximum(x); // system will prompt you that there is no such kind of function.

 

The returned type is dependent on the data type of the first argument. It may be Number, Currency, String, Date, Time, DateTime or Boolean.
  • Time x = Maximum(@dbfield, @dbfield1)
  • DateTime x = Maximum(@dbfield, @formula_groupby)
  • String x = Maximum(@dbfield, @parameter_groupby)
  • Date x = Maximum(@formula, @formula_groupby)
  • Currency x = Maximum(@formula, @dbfield1)
  • Currency x = Maximum(@formula, @parameter)
Minimum(a[ ]) This function returns the lowest value in an array.
  • a - An array, data type can be Number or Integer.
A Number or Integer value, which depends on the data type of the parameter a.
  • Number x = Minimum([ 2.36, 0.12, 1.25, 0.25 ])
  • Number x = Minimum([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
  • Integer x = Minimum([ 4, 9, 34, 80, 200 ])
  • Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
    Number y = Minimum(x)
Minimum(DBField a) This function is used to pick up the minimum value from the values referred to by the argument.
  • a - Values of a DBField or a formula field.
The returned value type is dependent on the data type of the parameter. It can be Number, Currency, String, Date, Time, DateTime or Boolean.
  • Number x = Minimum(@dbfield)
  • Date x = Minimum(@formula)
  • Currency x = Minimum(@dbfield)
  • Boolean x = Minimum(@formula)
Minimum(DBfield a, String groupby) This function is used to pick up the minimum value from the values referred to by the first argument.
  • a - Values of a DBField or a formula field.
  • groupby - A constant string to indicate the DBField name, formula or parameter grouped by.

Note: If a field is assigned to a variable, for example x, the variable x will lose the characteristic of representing a group of values. The following formula will then be treated as incorrect.

Number x = @dbfield;
Number y = Minimum(x); // system will prompt you that there is no such kind of function.

The returned value type is dependent on the first argument. It may be Number, Currency, String, Date, Time, DateTime or Boolean.
  • Time x = Minimum(@dbfield, @dbfield_groupby)
  • DateTime x = Minimum(@dbfield, @dbfield_groupby)
  • String x = Minimum(@dbfield, @parameter_groupby)
  • Date x = Minimum(@formula, @formula_groupby)
  • Currency x = Minimum(@formula, @dbfield_groupby)
  • Currency x = Minimum(@formula, @parameter)
MRound()

Overloads:

  • MRound(Number,Number)
  • MRound(Currency,Number)
  • MRound(Currency,Currency)
This function rounds up (away from zero) if the remainder of the division is greater than or equal to half of the value of the second parameter.

Expression (take MRound(Currency,Number) for example):

MRound(Currency,Number) = Number*(Integer(Currency/Number) + (1 if Remainder(Currency/Number) >=Number/2, otherwise 0)

  • Number - A Number value.
  • Currency - A Currency value.

Note: Regardless of the positive/negative sign of the second parameter, the result of the function takes the positive/negative sign of the first parameter.

A number value that is a multiple of the second parameter and at the same time is nearest to the first parameter.
  • MRound(11.0,4.0) - Return 12.0.
  • MRound($11.0,-4.0) - Return 12.0.
  • MRound($-7.0,$2.0) - Return -8.0.
pi()     The Mathematical value of pi, which is 3.1415926 (if rounded to 7 decimal places).  
PopulationStdDev()
  • PopulationStdDev(a[ ])
  • PopulationStdDev(DBField field)
  • PopulationStdDev(DBField field, String groupby)
This function computes the population standard deviation of the values referred to by the argument.
  • a - A Number array.
  • field - Values of a DBField or formula field.
  • groupby - A constant string to indicate the DBField, formula or parameter grouped by.

Note: If a field is assigned to a variable, for example x, the variable x will lose the characteristic of representing a group of values. Therefore, the following formula will be treated as incorrect.

Number x = @dbfield;
Number y = PopulationStdDev(x); // system will prompt you that there is no such kind of function.

A Number value.
  • Number x = PopulationStdDev([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
  • Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
    Number y = PopulationStdDev( x)
  • Number x = PopulationStdDev(@dbfield)
  • Number x = PopulationStdDev(@formula)
  • Number x = PopulationStdDev(@dbfield, @dbfield1)
  • Number x = PopulationStdDev(@dbfield, @"group_field")
  • Number x = PopulationStdDev(@dbfield, @parameter)
PopulationVariance()
  • PopulationVariance(a[ ])
  • PopulationVariance(DBField field)
  • PopulationVariance(DBField field, String groupby)
This function computes the population variance of the values referred to by the argument.
  • a - A Number array.
  • field - Values of a DBField or formula field.
  • groupby - A constant string to indicate the DBField, formula or parameter grouped by.

Note: If a field is assigned to a variable, for example x, the variable x will lose the characteristic of representing a group of values. Therefore, the following formula will be treated as incorrect.

Number x = @dbfield;
Number y = PopulationVariance(x); // system will prompt you that there is no such kind of function.

A Number value.
  • PopulationVariance([ 2.5, 4.75, 2.36, 1.25 ])
  • Number x = PopulationVariance([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
  • Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
    Number y = PopulationVariance(x)
  • Number x = PopulationVariance(@dbfield)
  • Number x = PopulationVariance(@formula)
  • Number x = PopulationVariance(@dbfield, @dbfield_groupby)
  • Number x = PopulationVariance(@dbfield, @formula_groupby)
  • Number x = PopulationVariance(@dbfield, @parameter)
pow(numbera, numberb) The function returns the value of numbera ^ numberb.
  • numbera - A Number value.
  • numberb - A Number value.
 
  • pow(2, 4) - Returns 16.
  • pow(6.2, 5.1) - Returns 10995.060323919315.
random(), random(seed)

Overloads:

  • random()
  • random(seed)
The function returns a random number greater than or equal to 0 and less than 1. If seed is equal to 0, Random returns the random number that was returned from the previous call to random. If seed is not supplied or is greater than 0, then Random will return the next random number in the internally generated sequence of random numbers. If seed is less than 0, then Random will use this value of seed to start a new random number sequence and returns the first value in the sequence. This function is typically used when your formula requires a randomly generated number, for example, statistical calculations or selecting records at random to limit the data in a report.
  • seed - An optional Number value parameter.

Note: You can call Random without ever starting a new random number sequence by specifying a negative seed parameter. If you do so, an internal seed will be generated using the system clock. The reason to start a new random number sequence by calling Random with a negative seed parameter, and then making subsequent calls to Random without a parameter (or with a positive parameter) is so that the report will look exactly the same every time it is previewed. In other words, it allows you to make use of random numbers, but to return a reproducible result.

A Number value.
  • random() - Returns: 0.673411041443785
  • random(123) - Returns: 0.06300625845328678
  • random(0) - Returns: 0.06300625845328678
  • random(-122223) - Returns: 0.3291484275937213
Remainder(Integer a, Integer b) This function is used to evaluate the remainder after numerator has been divided by a denominator.
  • a - An Integer value.
  • b - An Integer value.
An Integer value.
  • Remainder(7, 9) - Returns 7.
  • if(Remainder(@"CustomerID",2)==0) then
    return "0xffcc99"
    else
    return "0xccffff"

    This formula can distinguish odd numbers and even their backgrounds.

Round(Number a) This function returns the integer portion after a specified number has been rounded.
  • a - A Double value.
An Integer value. The return value of the following statement is 3.00.

Round(2.754)

Round(Number a, Integer b) This function is used to round to a specified scale determined by argument b for Double value a.
  • a - A Double value.
  • b - A Scale value.
A Number value. The return value of the following statement is 2.46.

Round(2.4576, 2)

Round(Number a, Integer b, Integer c) This function is used to round to a specified scale determined by argument b and a specified rounding mode determined by argument c for Double value a.
  • a - A Number value which will be rounded.
  • b - The rounded scale.
  • c - The rounding mode.

A Number value. The return value of the following statement is 2.03.

Round(2.021, 2, 0)

Round(Number a, Integer b, String c) This function is use to round to a specified scale determined by argument b using the rounding mode c.
  • a - A Number value which will be rounded.
  • b - The rounded scale.
  • c - The rounding mode. It can be Up (0), DOWN (1), CEILING (2), FLOOR (3), HALF_UP (4), HALF_DOWN (5), and HALF_EVEN (6). See the previous section about the detailed description of each rounding mode.
Note: The digits of the decimal fraction of the rounded number must be bigger than the scale specified in the formula.
A Number value.

The return value of the following statement is 123.457.

Round(123.4567, 3, 'UP')

RunningAvg()
  • RunningAvg(field_variable);
  • RunningAvg(field_variable, groupby);
This function takes the average of all the numerical values in a field on running records. For details about the arguments, return type, and examples, refer to Average().      
RunningCount()
  • RunningCount(field_variable);
  • RunningCount(field_variable, groupby);
This function takes a total count on running records of the values in a field. For details about the arguments, return type, and examples, refer to Count().      
RunningDistinctCount()
  • RunningDistinctCount(field_variable);
  • RunningDistinctCount(field_variable, groupby);
This function takes a total count of all the distinct fields in a report on running records. For details about the arguments, return type, and examples, refer to DistinctCount().      
RunningMaximum()
  • RunningMaximum(field_variable);
  • RunningMaximum(field_variable, groupby);
This function finds the largest numerical value in a field on running records. For details about the arguments, return type, and examples, refer to Maximum().      
RunningMinimum()
  • RunningMinimum(field_variable);
  • RunningMinimum(field_variable, groupby);
This function finds the smallest numerical value in a field on running records. For details about the arguments, return type, and examples, refer to Minimum().      
RunningSum()
  • RunningSum(field_variable);
  • RunningSum(field_variable, groupby);
This function sums up all the numerical values in a field on running records. For details about the arguments, return type, and examples, refer to Sum().      
sgn(data) The function returns the sign value of the given number (whose value is data). If data is greater than 0, it will return 1. If data is less than 0, it will return -1. If data is equal to 0, it will return 0.
  • data - The value you want to know the sign of.
1, 0 or -1.
  • sgn(2) - Returns 1.
  • sgn(-23) - Returns -1.
  • sgn(0) - Returns 0.
sin(number) The function returns a number specifying the sine of an angle given in radians. It takes a right-angle triangle, and returns the length of the side opposite to the specified angle divided by the length of the hypotenuse.
  • number - An angle in number of radians.
A Number value between -1 and 1.
  • sin(1) - Returns 0.84 (rounded to 2 decimals). This is the sine of 1 radian.
  • sin(60*pi/180) - Returns 0.87 (rounded to 2 decimals). This is the sine of 60 degrees. Before taking the sine, the angle is converted to radians by multiplying by pi/180.
sqr(number) The function returns the square root of a given number. If the value of the number is less than 0, this function returns NaN. It is designed to work like the Java Math function of sqrt().
  • number - A Number value greater than or equal to 0.
A Number value. sqr(100) - Returns 10.
StdDev()
  • StdDev(a[ ])
  • StdDev(field)
  • StdDev(field, groupby)
This function computes the standard deviation of the values referred to by the argument.
  • a - A Number array.
  • field - Values of DBField or formula field.
  • groupby - A constant string which indicates the DBField, formula or parameter grouped by.

Note: If a field is assigned to a variable, for example x, the variable x will lose the characteristic of representing a group of values. The following formula will then be treated as incorrect.

Number x = @dbfield;
Number y = StdDev( x); // system will prompt you that there is no such kind of function.

A Number value.
  • Number x = StdDev([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
  • Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
    Number y = StdDev( x)
  • Number x = StdDev(@dbfield)
  • Number x = StdDev(@formula)
  • Number x = StdDev(@dbfield, @dbfield1)
  • Number x = StdDev(@dbfield, @formula_groupby)
  • Number x = StdDev(@dbfield, @parameter)
Sum()
  • Sum(Number a[ ])
  • Sum(Integer a[ ])
  • Sum(Currency a[ ])
  • Sum(DBField field)
  • Sum(DBField, String groupby)
This function computes the sum of all the values referred to by the argument.
  • a - A Number, Integer or Currency array.
  • field - Values of DBField or formula field.
  • groupby - A constant string which indicates the DBField, formula or parameter grouped by.

Note: If a field is assigned to a variable, for example x, the variable x will lose the characteristic of representing a group of values. So, the following formula will be treated as incorrect.

Number x = @dbfield;
Number y = Sum( x); // system will prompt you that there is no such kind of function.

The returned value type is dependent on the parameter. It can be Number, Integer or Currency.
  • Number x = Sum([1, 2, 3.0, 4.5, 45, 67])
  • Number x[6] = [1, 2, 3.0, 4.5, 45, 67];
    Number y = Sum(x)
  • Currency x = Sum([$1, $2, $3.0, $4.5, $45, $67])
  • Currency x[] = [$1, $2, $3.0, $4.5, $45, $67];
    Currency y = Sum(x)
  • Number x = Sum(@dbfield)
  • Number x = Sum(@formula)
  • Number x = Sum(@dbfield, @dbfield_groupby)
  • Number x = Sum(@dbfield, @formula_groupby)
  • Number x = Sum(@dbfield, @parameter)
  • Currency x = Sum(@dbfield)
  • Currency x = Sum(@formula)
  • Currency x = Sum(@formula, @dbfield_groupby)
  • Currency x = Sum(@formula, @formula_groupby)
  • Currency x = Sum(@formula, @parameter)
tan(number) The function returns a number specifying the tangent of an angle given in radians. It takes a right-angle triangle, and returns the length of the side opposite the specified angle divided by the length of the side adjacent to the angle.
  • number - An angle in the form of number of radians.
A Number value.
  • tan(1) - Returns 1.5574 (rounded to 4 decimals). This is the tangent of 1 radian.
  • tan(45 * pi / 180) - Returns 1.0. This is the tangent of 45 degrees. Before taking the tangent, the angle is first converted to radians by multiplying it with pi/180.
ToBinary(Integer a) This function changes a specified integer to its binary form.
  • a - An Integervalue.
A String value. The return value of the following statement is 1011.

ToBinary(11)

ToHex(Integer a) This function returns the HEX form of a specified value.
  • a - An Integer value.
A String value. The return value of the following statement is 20.

ToHex(32)

ToInt(number a) This function changes a specified number to an Integer.
  • a - A Number value.
An Integer value. The return value of the following statement is 100.

ToInt(100.1)

ToOctal(Integer a) This function returns the octal form of a specified value.
  • a - An Integer value.
A String value. The return value of the following statement is 64.

ToOctal(52)

Truncate(Number a) This function returns a number by truncating the decimal portion.
  • a - A Double value to be truncated.
A Number value. The return value of the following statement is 25.00.

Truncate(25.4321)

Truncate(Number a, Integer b) This function returns a number by truncating the number at the decimal point. The number is truncated to the decimal place indicated by argument b, and the function returns a fractional number.
  • a - A Double value to be truncated.
  • b - A Double value indicating the number of the decimal places.
A Number value. The return value of the following statement is 25.430.

Truncate(25.432, 2)

Variance()
  • Variance(Number a[ ])
  • Variance(Currency a[ ])
  • Variance(DBField field)
  • Variance(DBField field, String groupby)
This function computes the variance of all the values referred to by the argument.
  • a - A Number array.
  • field - Values of DBField or formula field.
  • groupby - A constant string which indicates the DBField, formula or parameter grouped by.

Note: If a field_variable is assigned to a variable, for example x, the variable x will lose the characteristic of representing a group of values. So, the following formula will be treated as incorrect.

Number x = @dbfield;
Number y = Variance(x); // system will prompt you that there is no such kind of function.

A Number value.
  • Number x = Variance([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
  • Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
    Number y = Variance(x)
  • Number x = Variance(@dbfield)
  • Number x = Variance(@formula)
  • Number x = Variance(@dbfield, @dbfield_groupby)
  • Number x = Variance(@dbfield, @formula_groupby)
  • Number x = Variance(@dbfield, @parameter)

Rounding Mode

The parameter c in the function Round(Number a, integer b, integer c) is used to represent the following different seven rounding modes, which can be specified as an integer between 0 and 6.

  • ROUND_UP = 0
    Rounding mode to round away from zero. Always increments the digit prior to a non-zero discarded fraction. Note that this rounding mode never decreases the magnitude of the calculated value.
  • ROUND_DOWN = 1
    Rounding mode to round towards zero. Never increments the digit prior to a discarded fraction (that is, truncates). Note that this rounding mode never increases the magnitude of the calculated value.
  • ROUND_CEILING = 2
    Rounding mode to round towards positive infinity. If the value is positive, behaves as for ROUND_UP; if negative, behaves as for ROUND_DOWN. Note that this rounding mode never decreases the calculated value.
  • ROUND_FLOOR = 3
    Rounding mode to round towards negative infinity. If the BigDecimal is positive, behave as for ROUND_DOWN; if negative, behave as for ROUND_UP. Note that this rounding mode never increases the calculated value.
  • ROUND_HALF_UP = 4
    Rounding mode to round towards "nearest neighbor" unless both neighbors are equidistant, in which case round up. Behaves as for ROUND_UP if the discarded fraction is >= .5; otherwise, behaves as for ROUND_DOWN. Note that this is the rounding mode that most of us were taught in grade school.
  • ROUND_HALF_DOWN = 5
    Rounding mode to round towards "nearest neighbor" unless both neighbors are equidistant, in which case round down. Behaves as for ROUND_UP if the discarded fraction is > .5; otherwise, behaves as for ROUND_DOWN.
  • ROUND_HALF_EVEN = 6
    Rounding mode to round towards the "nearest neighbor" unless both neighbors are equidistant, in which case, round towards the even neighbor. Behaves as for ROUND_HALF_UP if the digit to the left of the discarded fraction is odd; behaves as for ROUND_HALF_DOWN if it's even. Note that this is the rounding mode that minimizes cumulative error when applied repeatedly over a sequence of calculations.

Back to top

String

Function Description Parameter Return Value Example
Asc(Char a) This function evaluates the ASCII value of the first character of the argument.
  • a - A Char value.
An Integer value. The return value of the following statement is 97.

Asc("abc")

ByteToText(byte) This function returns the size of a message in the "appropriate" unit.
  • If the parameter is less than 1024, the result will be in bytes.
  • If the parameter is between 1024 and 1048576, then the result will be in kilobytes.
  • Otherwise, the result will be in megabytes.
  • byte - the size of a message in bytes.
A String value.
  • ByteToText(1000) - Returns 1000 bytes.
  • ByteToText(251561) - Returns 245 KB.
  • ByteToText(10000000) - Returns 9 MB.
  • ByteToText(25454464) - Returns 24 MB.
Chr(Integer a) This function returns a single character string associated with the ASCII value passed as the argument.
  • a - An Integer value.
A String value.
  • The return value of the following statement is character c.

    Chr(99)

  • The return value of the following statement is character {.

    Chr(123)

ExchGetId (address) This function begins by determining whether the address is in X500 or X400 format. Once this has been ascertained, the function will then determine the ID.
  • If the field is in X500 format, the function will extract the last instance of the "CN=" code (not case sensitive)
  • If the field is in the X400 format, it will extract the SMTP or MS Ids.

Note: The fields must follow the address type standards for the functions to operate them.

  • address - The address of sender/recipient (String data type).
A String value.
  • ExchGetID("/o=Jinfonet/ou=LOGIREPORT /cn=Configuration/cn=Servers/cn=ESPRESSO/cn=Fredt") - Returns "Fredt".
  • ExchGetID("c=US ;a= ;p=Jinfonet; o=apps-wga; dda:smtp=Jenny@jinfonet.com") - Returns "Jenny@jinfonet.com".
  • ExchGetID("c=US; a= ;p=Jinfonet;o=apps-wga;dda:ms=com/logireport/Jenny") - Returns "com/logireport/Jenny".
ExchGetOrganization(address) This function begins by determining whether the address is in X500 or X400 format. Once this has been ascertained, the function will then determine the Organization Name.
  • If the field is in X500 format, the function will extract the last instance of the "/O=" code (not case sensitive).
  • If the field is in the X400 format, it will extract the instance of the "P=" code (not case sensitive).

Note: The fields must follow the address type standards for the functions to operate on them.

  • address - The address of the sender/recipient (String data type).
A String value.
  • ExchGetOrganization("c=US ;a= ;p=Jinfonet; o=apps-wga; dda:ms=com/logireport/Jenny") - Returns "Jinfonet".
  • ExchGetOrganization("c=US ;a= ;p=Jinfonet; o=apps-wga; dda:smtp=Jenny@jinfonet.com") - Returns "Jinfonet".
ExchGetPath(path) This function returns the container information in an address field.
  • If the address type is X500, the function will return all of the information from the first instance of the "CN=" code until the last instance of the "CN=" code. If there is only one instance of the "CN=" code, the function will return NULL.
  • If the address type is X400, the function will return all "OU*=" codes (residing between the "P=" and "O=" codes).
  • If the address starts with "DDA:", the function will return all information after the "DDA:" code.
  • If the field is blank, "UNKNOWN ADDRESS" will be returned.
  • path - The address of sender/receiver (String data type).
A String value.
  • ExchGetPath("/o=Jinfonet/ou=LOGIREPORT /cn=Configuration/cn=Servers/cn=ESPRESSO/cn=Fredt") - Returns "cn=Configuration/cn=Servers/cn=ESPRESSO".
  • ExchGetPath("c=US; a= ; p=Jinfonet; ou1=Jenny; ou2=LOGIREPORT; o=apps-wga;") - Returns "ou1=Jenny; ou2=LOGIREPORT".
  • ExchGetpath("DDA:MS=LOGIREPORT/JENNY/logireport") - Returns "MS=LOGIREPORT/JENNY/logireport".
ExchGetSite(address) The function begins by determining whether the address is in X500 or X400 format. Once this has been ascertained, the function will then determine the Site Name.
  • If the field is in X500 format, the function will extract the last instance of the "/OU=" code (not case sensitive).
  • If the field is in the X400 format, it will extract the instance of the "O=" code (not case sensitive).

Note: The fields must follow the address type standards for the functions to operate on them.

  • address - The address of sender/recipient (String data type).
A String value.
  • ExchGetSite("/o=Jinfonet/ou=LOGIREPORT/cn=Configuration/cn=Servers/cn=ESPRESSO/cn=Fredt") - Returns "LOGIREPORT".
  • ExchGetSite("c=US; a= ;p=Jinfonet;o=apps-wga; dda:smtp=Jenny@jinfonet.com") - Returns "apps-wga".
ExtractString(String origin, String start, String end) This function is used to evaluate an origin string and returns the first occurrence of a String that starts with the Start String and ends with the End String (the End String is excluded). If the End String is not found, the string that starts with the Start String till its end will be returned.
  • origin - A String value.
  • start - A String value.
  • end - A String value.
A String value.
  • The return value of the following statement is a String day.

    ExtractString("one day after that day", "d", " ")

  • The return value of the following statement is day after that day.

    ExtractString("one day after that day", "d", "c")

  • ExtractString("The rain in Spain falls on the plain","rain", "plain") - Returns rain in Spain falls on the.
filter(inString, searchString)

Overloads:

  • filter(inString, searchString)
  • filter(inString, searchString, include)
  • filter(inString, searchString, include, compare)
This function is designed to search the string in the specified strings. It searches an array of strings for a specified string, and returns the strings in an array.
  • inString - An array of string which you want to find the search string from.
  • searchString - A String which you want to search for.
  • include - An optional Boolean value indicating whether to return sub strings that include or exclude searchString. If include is True, Filter returns the subset of the array that contains searchString as a sub string. If include is False, Filter returns the subset of the array that does not contain searchString as a sub string. If omitted, the value True will be used.
  • compare - An optional Number value indicating the kind of string comparison to use,
    • 0 performs a comparison that is case sensitive.
    • 1 performs a comparison that is case insensitive.
    • If omitted, a case sensitive comparison is performed.
Array of String values. Suppose that inString = [ "abc", "Abc", "abcdfg", "asdfabc", "sdfdfd"], searchString = "abc"
  • filter(inStrings, searchString) - Returns ["abc", "abcdfg", "asdfabc"].
  • filter(inStrings, searchString, true) - Returns ["abc", "abcdfg", "asdfabc"].
  • filter(inStrings, searchString, false) - Returns ["abc"].
  • filter(inStrings, searchString, true, 0)]"asdfabc", "abcdfg", "abc"- Returns [
  • filter(inStrings, searchString, true, 1) - Returns ["abc", "Abc", "abcdfg", "asdfabc"].
  • filter(inStrings, searchString, false, 1) - Returns ["abc", "Abc"].
  • string inString[]=["John","Paul","George","Ringo"];
    join(filter(inString,"o"),",");

    This formula can search strings which include the word "o" and use comma to compart them.

InStr(String a, String b) This function returns the position of the first occurrence of one string within another. If String a is not found in String b, the InStr function will return -1.
  • a - A String value to be sought.
  • b - A String value to be searched.
An Integer value.
  • The return value of the following statement is 6.

    InStr("my", "he is my brother")

  • The return value of the following statement is -1.

    InStr("our", "she is my mother")

  • String value = toText(@Price, "####.##", 2, "", ".");
    integer dot = InStr(".", value);

    if (dot >= 0)
    {
    return ToWords(ToNumber(left(value, dot)), 0) + " dollars "
    +ToWords(ToNumber(mid(value, dot + 1)), 0) + " cents";
    }
    else
    {
    return ToWords(ToNumber(value), 0) + " dollars";
    }

    This formula can convert number format to dollars and cents pattern.

InStr(Number a, String b, String c) This function returns the position of the first occurrence of one string within another, starting at the position specified by argument a. If String c is not found in String b, the InStr function will return -1.
  • a - An Integer value that is the character position in String b where the search is to begin.
  • b - A String value to be searched.
  • c - A String value to be sought.
An Integer value.
  • The return value of the following statement is 6.

    InStr(3, "he is my brother", "my")

  • The return value of the following statement is -1.

    InStr(3, "he is my brother", "our")

join(stringArray)

Overloads:

  • join(stringArray)
  • join(stringArray, delimiterString)
The function returns a string created by joining a number of sub strings contained in an array.
  • stringArray - A String array containing substrings to be joined.
  • dimiterString - An optional String used to separate the substrings in the returned string. If omitted, the space character (" ") is used. If the delimiter is a zero-length string(" "), then all items in the list will be concatenated with no delimiters.
A String value. Assume that the stringArray list in the examples consists of the following three elements: Welcome, Use and Logi Report.
  • join(list) - Returns the string Welcome Use Logi Report.
  • join(list, ", ") - Returns the string Welcome, Use, Logi Report.
LastIndexOf(String a, String b) This function returns the position of the last occurrence of one string within another, if String b is not found in String a, the InStr function will return -1.
  • a - A String value to be searched.
  • b - A String value to be sought.
An Integer value.
  • The return value of the following statement is 9.

    LastIndexOf("avcievmgbvi","v")

  • The return value of the following statement is -1.

    LastIndexOf("avcievmgbvi","n")

LastIndexOf(Integer a, String b, String c) This function returns the position within this string of the first occurrence of the specified sub string, searching backward starting at the position specified by argument a. If String c is not found in String b, the InStr function will return -1.
  • a - An Integer value that is the character position in String b where the search is to end.
  • b - A String value to be searched.
  • c - A String value to be sought.
An Integer value.  
Left(String a, Number b) This function returns a sub string that contains the specified number of characters from the left side of a string.
  • a - A String value.
  • b - A Number value indicating the number of characters to be extracted from String a.
A String value. The return value of the following statement is he is.

Left("he is my father", 5)

Length(String a) This function returns the number of characters in a String.
  • a - A String value.
An Integer value. The return value of the following statement is 16.

Length("she is my mother")

LooksLike(String a, String b) This function enables you to locate field values using standard DOS wildcards ( ? is a wildcard for a single character, * is a wildcard for any number of characters). It does this by comparing a String to a mask which contains one or more wildcards. The function returns True if the string matches the mask, and False if the string does not match the mask.
  • a - A String value to be compared to the mask.
  • b - A String that provides a mask for comparing the value in String a.
A Boolean value.
  • The return value of the following statement is False.

    LooksLike("he is my brother", "he is my brother and friend")

  • The return value of the following statement is True.

    LooksLike("he is my brother", "he is my brother")

  • LooksLike("George Peck", "G?orge*") - Returns True.
LowerCase(String a) This function returns a String that contains all lowercase letters converted from String a.
  • a - A String value.
A String value. The return value of the following statement is this is her book.

LowerCase("This is HER book")

Mid(String a, Integer b) This function returns a number of characters from a specified position of a String.
  • a - A String value to be extracted.
  • b - An Integer value indicating the position of the first character to extract.
A String value. The return value of the following statement is my father.

Mid("he is my father", 6)

Mid(String a, Integer b, Integer c) This function returns a specified number of characters from a specified position of a String.
  • a - A String value to be extracted.
  • b - An Integer value indicating the position of the first character to extract.
  • c - An Integer value indicating the number of characters to be extracted from String a.
A String value.
  • Mid("he is my father", 9, 6) - Returns "father".
  • Integer ln,i;
    String str1;
    str1="";
    ln=length(@col);
    for (i=1;i<ln ; i=i+1)
    str1=str1 + mid(@col, i, 1) + "\n";
    return str1;

    This formula can convert a DBField string from a normal horizontal pattern to a vertical pattern.

NumericText(String a) This function is used to test if the content of the string is a Number.
  • a - A String value to be tested.
A Boolean value.
  • The return value of the following statement is False.

    NumericText("she is my mother")

  • The return value of the following statement is True.

    NumericText("123456")

Picture(String a, String b) This function prints a string or values in a string in a predetermined format.
  • a - A String value to be formatted according to the picture format.
  • b - A String value representing the way you want the characters in the string to be printed.
A String value.
  • The return value of the following statement is Brother Tom.

    Picture("Brother", "XxXXxxx Tom")

  • The return value of the following statement is He is Tom.

    Picture("He Tom", "xx is")

  • Picture("8007733472", "Phone: (xxx) xxx-xxxx") - Returns Phone: (800) 773-3472.
ProperCase()

Overloads:

ProperCase(String)

The function capitalizes the first letter in a text string and any other letter that follows a character other than a letter, and converts all other letters to lowercase letters.
  • String - A text string.
A String value.
  • ProperCase('LINDA FONG') - Return "Linda Fong".
  • ProperCase("123Michael's") - Return "123Michael'S".
  • ProperCase("2-cent's worth") - Return "2-Cent'S Worth".
  • ProperCase('76BudGet') - Return "76Gudget".
ReplaceString(inString, searchString, replaceString, startPosition, count, compare)

Overloads:

  • ReplaceString(inString, searchString, replaceString)
  • ReplaceString(inString, searchString, replaceString, startPosition)
  • ReplaceString(inString, searchString, replaceString, startPosition, count)
  • ReplaceString(inString, searchString, replaceString, startPosition, count, compare)
The function returns a String in which a specified sub string has been replaced with another sub string a specified number of times. As an option, you can also specify a location in the string where the replacing process will begin from (returns a string starting from that position). This function is typically used in a string to systematically replace one sub string with another.
  • inString - A String containing sub string to replace.
  • searchString - A sub string being searched for.
  • replaceString - The replacement sub string.
  • startPosition - An optional Number indicating the position within inString where the sub string search is to begin from. If omitted, 1 will be used.
  • count - An optional Number of sub string substitutions to be performed. If omitted, the default value is -1, which means make all possible substitutions.
  • compare - An optional Number indicating the kind of comparison to use when evaluating sub strings:
    • 0 performs a comparison that is case-sensitive
    • 1 performs a comparison that is case-insensitive
    • If omitted, a case-sensitive comparison will be performed

Note: The return value of the replace function is a String, with the specified sub string replacements made, beginning at the position specified by startPosition, and endings at the end of the inString string. It is not a start to finish copy of the original string.

A String value. Assume that inStrings = "abc Abc abcdfg asdfabc sdfdfd", searchString = "abc", replaceString = "ABC".
  • ReplaceString(inStrings, searchString, replaceString) - Returns "ABC Abc ABCdfg asdfABC sdfdfd".
  • ReplaceString(inStrings, searchString, replaceString, 5) - Returns "abc Abc ABC dfg asdfABC sdfdfd".
  • ReplaceString(inStrings, searchString, replaceString, 0) - Returns "ABC Abc ABCdfg asdfABC sdfdfd".
  • ReplaceString(inStrings, searchString, replaceString, 0, 0) - Returns "abc Abc abcdfg asdfabc sdfdfd".
  • ReplaceString(inStrings, searchString, replaceString, 0, 1) - Returns "ABC Abc abcdfg asdfabc sdfdfd".
  • ReplaceString(inStrings, searchString, replaceString, 0, -1,1) - Returns "ABC ABC ABCdfg asdfABC sdfdfd".
  • ReplaceString(inStrings, searchString, replaceString, 0, -1, 0) - Returns "ABC Abc ABCdfg asdfABC sdfdfd".
ReplicateString(String a, Integer b) This function replicates the string in String a the number of times specified by b.
  • a - A String value to be replicated.
  • b - An Integer value indicating the number of times that String a is to be replicated.
A String value. The return value of the following statement is Stop! Stop! Stop!.

ReplicateString("Stop!", 3)

Right(String a, Integer b) This function is used to extract the specified number of characters in String a from the right side.
  • a - A String value to be extracted.
  • b - An Integer value indicating the number of characters to be extracted from String a.
A String value.

The return value of the following statement is mother.

Right("She is my mother", 6)

Soundex(string) The function evaluates a text string and returns a four-character value that symbolizes the way the string sounds. Use this function whenever you want to locate records based on two or more field values that are spelled differently yet sound alike. This function can also be used to find customer names that have been misspelled.
  • string - One of two or more strings that sound alike.
Text string.
  • Soundex("Jinfonet") - Returns J515.
  • if (Soundex("George") == Soundex("gorge")) then
    "Sound the same"
    else
    "Different sound"

    - Returns Sound the same.

Space(Integer a) This function returns a String value that contains a specified number of spaces.
  • a - An Integer value indicating the number of spaces.
A String value.

The return value of the following statement is   .

Space(3)

StrCmp(String a, String b) This function is used to compare two strings. It returns positive if String a is greater than String b, returns 0 if String a is equal to String b, and returns negative if String a is less than String b.
  • a- A String value.
  • b- A String value.
An Integer value.
  • The return value of the following statement is -1.

    StrCmp("He is Tom", "I am student")

  • The return value of the following statement is 0.

    StrCmp("He is Tom", "He is Tom")

  • The return value of the following statement is 1.

    StrCmp("I am student", "He is Tom")

StringSplit(inString, delimiterString, count, compare)

Overloads:

  • stringSplit(inString)
  • stringSplit(inString, delimiterString)
  • stringSplit(inString, delimiterString, count)
  • stringSplit(inString, delimiterString, count, compare)
This function takes a String that contains a number of sub strings, breaks it up into a specified number of sub strings and returns an array containing the sub strings.
  • inString - A String expression containing sub strings and delimiters.
  • delimiterString - An optional String character used to identify sub string limits. If omitted, the space character (" ") is assumed to be the delimiter. If the delimiter is a zero-length string, a single-element array containing the entire inString string will be returned.
  • count - An optional Number value of substrings to be returned. The value -1 indicates that all substrings will be returned. If omitted, -1 will be used.
  • compare - An optional Number indicating the kind of comparison to use when evaluating the delimiter string:
    • 0 performs a comparison that is case-sensitive.
    • 1 performs a comparison that is case-insensitive.
    • If omitted, a case-sensitive comparison is performed.

Note: If count, c, is less than the total number of substrings in inString, then at most c substrings will be returned as elements in the resultant array. The last element in the array is a concatenation of the sub string and all the remaining sub strings.

Array of String values. Assume that inString = "Welcome use Logi Report"
  • StringSplit(inString) - Returns [ "Welcome", "use", "Logi Report"]
  • StringSplit(inString, "use") - Returns [ "Welcome", "Logi Report"]
  • StringSplit(inString, " ", 2) - Returns [ "Welcome", "use Logi Report"]
strReverse(inString) This function is designed to reverse the string. It returns a String in which the character order of inString is reversed. If inString is a zero-length string (" "), a zero-length string will be returned.
  • inString - A String whose characters are to be reversed.
A String value.
  • strReverse("abc") - Returns cba.
  • strReverse("false") - Returns eslaf.
toLongString(String a) This function converts a string to a long string.
  • a - A String value.
A LongString value. The return value of the following statement is aa.

LongString a=toLongString("aa");
string b="bb";
if (b > toString(a)) then
return a
else
return toLongString(b);

ToNumber(Currency a) This function converts a Currency to a Number.
  • a - A Currency value.
A Number value.
  • The return value of the following statement is 4.32.

    ToNumber($4.32)

  • The result value of the following statement is 4.68.

    ToNumber($4.68)

ToNumber(String a) This function converts a specified string to a Number.
  • a - A Char value.

Note: In this function, the format of the string should be [#] or [#].[#]. If you specify a character string as the argument, the return value will be NULL.

A Number value.

The return value of the following statement is 123.00.

ToNumber("123")

toString(LongString a) This function converts a long string to a string.
  • a - A LongString value.
A String value.

The return value of the following statement is aa.

LongString a=toLongString("aa");
string b="bb";
if (b > toString(a)) then
return a
else
return toLongString(b);

ToText(Bit a) This function converts a Bit value to a String, either true or false.
  • a - A Bit value.
A String value. The return value of the following statement is false.

ToText(3<2)

ToText(Currency a) This function converts a Currency value to a String.
  • a - A Currency value to be converted.
A String value. he return value of the following statement is $123.45.

ToText($123.45)

ToText(Currency a, String b) This function converts a Currency value to a String.
  • a - A Currency value to be converted.
  • b - A String indicating the format for displaying the value in a.
A String value. The return value of the following statement is $123.45.

ToText($123.45, "$##0.00")

ToText(Currency a, String b, Integer c) This function converts a Currency value to a String.
  • a - A Currency value to be converted.
  • b - A String indicating the format for displaying the value in a.
  • c - The number of decimal places to be converted.
A String value. The return value of the following statement is $123.5.

ToText($123.456, "$##0.00", 1)

ToText(Currency a, String&nbsp; b, Integer c, String d) This function converts a Currency value to a String.
  • a - A Currency value to be converted.
  • b - A String indicating the format for displaying the value in a.
  • c - The number of decimal places to be converted.
  • d - A single character string indicating the character to be used to separate thousands in a.
A String value. The return value of the following statement is $1*234.57.

ToText($1234.57, "$#,###.000", 2, "*")

ToText(Currency a, String b, Integer c, String d, String e) This function converts a Currency value to a String.
  • a - A Currency value to be converted.
  • b - A String indicating the format for displaying the value in a.
  • c - The number of decimal places to be converted.
  • d - A single character string indicating the character to be used to separate thousands in a.
  • e - A single character string indicating to be used as a decimal separator.
A String value.
  • The return value of the following statement is $4,567.123.

    ToText($4567.123, "$#,##0.00", 3, "," , ".")

  • The return value of the following statement is $4&567*1.

    ToText($4567.123, "$#,##0.00", 1, "&", "*")

ToText(Currency a, Integer c) This function converts a Currency value to a String.
  • a - A Currency value to be converted.
  • c - The number of decimal places to be converted.
A String value. The return value of the following statement is $123.46.

ToText($123.4567, 2)

ToText(Currency a, Integer c, String d) This function converts a Currency value to a String.
  • a - A Currency value to be converted.
  • c - The number of decimal places to be converted.
  • d - A single character string indicating the character to be used to separate thousands in a.
A String value. The return value of the following statement is $1,234.57.

ToText($1234.567, 2, ",")

ToText(Currency a, Integer c, String d, String e) This function converts a Currency value to a String.
  • a - A currency value to be converted.
  • c - The number of decimal places to be converted.
  • d - A single character string indicating the character to be used to separate thousands in a.
  • e - A single character string indicating to be used as a decimal separator.
A String value. The return value of the following statement is $1,234.57.

ToText($1234.567, 2, ",", ".")

ToText(Date a) This function converts a Date value to a String.
  • a - A Date value.
A String value. Suppose the date is July 15,1999, the return value of the following statement is 15-Jul-99.

ToText(ToDate(1999,7,15))

ToText(Date a, String b) This function converts a Date value to a String.
  • a - A Date value.
  • b - A String that defines how the Date value in a to be formatted.
A String value. Suppose the date is July 15,1999, the return value of the following statement is 15-Jul-99.

ToText(ToDate(1999,7,15), "dd-MMM-yy")

ToText(DateTime a) This function converts a DateTime value to a String.
  • a - A DateTime value to be converted.
A String value. Suppose the date is July 15,1999 and time is 7:42:51, the return value of the following statement is 15-Jul-99 7:42:51 AM.

ToText(ToDateTime(1999,7,15,7,42,51))

ToText(DateTime a, String b) This function converts a DateTime value to a String.
  • a - A DateTime value to be converted.
  • b - A String that defines how the DateTime value in a to be formatted.
A String value. Suppose the date is July 15,1999 and time is 7:42:51, the return value of the following statement is 07-15-99 7:42:51.

ToText(ToDateTime(1999,7,15,7,42,51), "MM-dd-yy h:mm:ss")

ToText(DateTime a, String b, String c) This function converts a DateTime value to a String.
  • a - A DateTime value to be converted.
  • b - A String that defines how the DateTime value in a to be formatted.
  • c - A String to be used as a label for A.M. (morning) hours.
A String value. Suppose the date is July 15,1999 and time is 7:42:51, the return value of the following statement is 15-Jul-99 7:42:51 AM.

ToText(ToDateTime(1999,7,15,7,42,51), "dd-MMM-yy h:mm:ss a", "AM")

ToText(DateTime a, String b, String c, String d) This function converts a DateTime value to a String.
  • a - A DateTime value to be converted.
  • b - A String that defines how the DateTime value in a to be formatted.
  • c - A String to be used as a label for A.M. (morning) hours.
  • d - A String to be used as a label for P.M. (evening) hours.
A String value.
  • Suppose the date is Oct. 7,1999 and time is 7:42:51, the return value of the following statement is 07-Oct-99 7:42:51 AM.

    ToText(ToDateTime(1999,10,7,7,42,51), "dd-MMM-yy h:mm:ss a", "AM", "PM")

  • Suppose the date is Oct. 7,1999 and time is 18:42:51, the return value of the following statement is 07-Oct-99 6:42:51 PM.

    ToText(ToDateTime(1999,10,7,18,42,51), "dd-MMM-yy h:mm:ss a","AM", "PM")

ToText(Integer a) This function converts an Integer value to a String.
  • a - An Integer value to be converted.
A String value. The return value of the following statement is 456.

ToText(456)

ToText(Integer a, String b) This function converts an Integer value to a String.
  • a - An Integer value to be converted.
  • b - A String value indicating the format for displaying the value in argument a.
A String value. The return value of the following statement is 456.

ToText(456, "###")

ToText(Integer a, String b, Integer c) This function converts an Integer value to a String.
  • a - An Integer value to be converted.
  • b - A String indicating the format for displaying the value in a.
  • c - The number of decimal places to be converted.
A String value. The return value of the following statement is 456.00.

ToText(456, "###", 2)

ToText(Integer a, String b, Integer c, String d) This function converts an Integer value to a String.
  • a - An Integer value to be converted.
  • b - A String indicating the format for displaying the value in a.
  • c - The number of decimal places to be converted.
  • d - A single character string indicating the character to be used to separate thousands in a.
A String value. The return value of the following statement is 1,234.00.

ToText(1234, "#,###", 2, ",", ".")

ToText(Integer a, String b, Integer c, String d, String e) This function converts an Integer value to a String.
  • a - An Integer value to be converted.
  • b - A String indicating the format for displaying the value in a.
  • c - The number of decimal places to be converted.
  • d - A single character string indicating the character to be used to separate thousands in a.
  • e - A single character string indicating to be used as a decimal separator.
A String value. The return value of the following statement is 1,234.00.

ToText(1234, "#,###", 2, ",", ".")

ToText(Integer a, Integer c) This function converts an Integer value to a String.
  • a - An Integer value to be converted.
  • c - The number of decimal places to be converted.
A String value. The return value of the following statement is 123.00.

ToText(123, 2)

ToText(Integer a, Integer c, String d) This function converts an Integer value to a String.
  • a - An Integer value to be converted.
  • c - The number of decimal places to be converted.
  • d - A single character string indicating the character to be used to separate thousands in a.
A String value. The return value of the following statement is 1,234.00.

ToText(1234, 2, ",")

ToText(Integer a, Integer c, String d, String e) This function converts an Integer value to a String.
  • a - An Integer value to be converted.
  • c - The number of decimal places to be converted.
  • d - A single character string indicating the character to be used to separate thousands in a.
  • e - A single character string indicating to be used as a decimal separator.
A String value. The return value of the following statement is 12,345.00.

ToText(12345, 2, ",", ".")

ToText(Number a) This function converts a Double value to a String.
  • a - A Double value to be converted.
A String value. The return value of the following statement is 123.456.

ToText(123.456)

ToText(Number a, String b) This function converts a Double value to a String.
  • a - A Double value to be converted.
  • b - A String indicating the format for displaying the value in a.
A String value. The return value of the following statement is 1236.46.

ToText(1236.456, "###0.00")

ToText(Number a, String b, Integer c) This function converts a Double value to a String.
  • a - A Double value to be converted.
  • b - A String indicating the format for displaying the value in a.
  • c - The number of decimal places to be converted.
A String value. The return value of the following statement is 123.46.

ToText(123.456, "###.00", 2)

ToText(Number a, String b, Integer c, String d) This function converts a Double value to a String.
  • a - A Double value to be converted.
  • b - A String indicating the format for displaying the value in a.
  • c - The number of decimal places to be converted.
  • d - A single character string indicating the character to be used to separate thousands in a.
A String value. The return value of the following statement is 1,234.57.

ToText(1234.567, "#,###.000", 2, ",")

ToText(Number a, String b, Integer c, String d, String e) This function converts a Double value to a String.
  • a - A Double value to be converted.
  • b - A String indicating the format for displaying the value in a.
  • c - The number of decimal places to be converted.
  • d - A single character string indicating the character to be used to separate thousands in a.
  • e - A single character string indicating to be used as a decimal separator.
A String value. The return value of the following statement is 1,234.57.

ToText(1234.567, "#,###.000", 2, ",", ".")

ToText(Number a, Integer c) This function converts a Double value to a String.
  • a - A Double value to be converted.
  • c - The number of decimal places to be converted.
A String value. The return value of the following statement is 123.46.

ToText(123.456, 2)

ToText(Number a, Integer c, String d) This function converts a Double value to a string.
  • a - A Double value to be converted.
  • c - The number of decimal places to be converted.
  • d - A single character string indicating the character to be used to separate thousands in a.
A String value. The return value of the following statement is 1,234.6.

ToText(1234.567, 1, ",")

ToText(Number a, Integer c, String d, String e) This function converts a Double value to a String.
  • a - A Double value to be converted.
  • c - The number of decimal places to be converted.
  • d - A single character string indicating the character to be used to separate thousands in a.
  • e - A single character string indicating to be used as a decimal separator.
A String value. The return value of the following statement is 1,234,57.

ToText(1234.567, 2, "," , ",")

ToText(String timezone, Date a) This function converts a Date value to a String.
  • timezone - A String value indicating the time zone. For the detailed usage, refer to java.util.TimeZone.
  • a - A Date value.
A String value. Suppose that today is 2018.09.27, the return value of the following statement is 26-Sep-18.

Date d = today();

ToText("GMT", d);

ToText(String timezone, Date a, String pattern) This function converts a Date value to a String.
  • timezone - A String value indicating the time zone. For the detailed usage, refer to java.util.TimeZone.
  • a - A Date value.
  • pattern - A String value that defines how the Date value in a to be formatted.
A String value. Suppose that today is 2018.09.27, the return value of the following statement is 2018/09/26.

ToText("GMT", today(), "yyyy/MM/dd")

ToText(String timezone, Datetime a) This function converts a DateTime value to a String.
  • timezone - A String value indicating the time zone. For the detailed usage, refer to java.util.TimeZone.
  • a - A DateTime value.
A String value. Suppose that the current date time is 2018.09.27 5:07:37 PM, the return value of the following statement is 27-Sep-18 9:07:37 AM.

ToText("GMT", CurrentDateTime())

ToText(String timezone, Datetime a, String pattern) This function converts a DateTime value to a String.
  • timezone - A String value indicating the time zone. For the detailed usage, refer to java.util.TimeZone.
  • a - A DateTime value.
  • pattern - A String value that defines how the DateTime value in a to be formatted.
A String value. Suppose that the current date time is 2018.09.27 5:07:37 PM, the return value of the following statement is 2018/09/27 09:07:37.

ToText("GMT", CurrentDateTime(), "yyyy/MM/dd hh:mm:ss")

ToText(String timezone, Datetime a, String pattern, String am) This function converts a DateTime value to a String.
  • timezone - A String value indicating the time zone. For the detailed usage, refer to java.util.TimeZone.
  • a - A DateTime value.
  • pattern - A String value that defines how the DateTime value in a to be formatted.
  • am - A String to be used as a label for A.M. (morning) hours.
A String value. Suppose that the current date time is 2018.09.27 5:07:37 PM, the return value of the following statement is 2018/09/27 09:07:37 AM.

ToText("GMT", CurrentDateTime(), "yyyy/MM/dd hh:mm:ss a","AM")

ToText(String timezone, Datetime a, String pattern, String am, String pm) This function converts a DateTime value to a String.
  • timezone - A String value indicating the time zone. For the detailed usage, refer to java.util.TimeZone.
  • a - A DateTime value.
  • pattern - A String value that defines how the DateTime value in a to be formatted.
  • am - A String to be used as a label for A.M. (morning) hours.
  • pm - A String to be used as a label for P.M. (evening) hours.
A String value. Suppose that the current date time is 2018.09.27 5:07:37 PM, the return value of the following statement is 2018/09/27 09:07:37 AM.

ToText("GMT", CurrentDateTime(), "yyyy/MM/dd hh:mm:ss a","AM", "PM")

ToText(String timezone, Time a) This function converts a Time value to a String.
  • timezone - A String value indicating the time zone. For the detailed usage, refer to java.util.TimeZone.
  • a - A Time value.
A String value. Suppose that the current time is 5:07:37 PM, the return value of the following statement is 9:07:37 AM.

ToText("GMT", CurrentTime())

ToText(String timezone, Time a, String pattern) This function converts a Time value to a String.
  • timezone - A String value indicating the time zone. For the detailed usage, refer to java.util.TimeZone.
  • a - A Time value.
  • pattern - A String value that defines how the Time value in a to be formatted.
A String value. Suppose that the current time is 5:07:37 PM, the return value of the following statement is 09:07:37.

ToText("GMT", CurrentTime(), "HH:mm:ss")

ToText(String timezone, Time a, String pattern, String am) This function converts a Time value to a String.
  • timezone - A String value indicating the time zone. For the detailed usage, refer to java.util.TimeZone.
  • a - A Time value.
  • pattern - A String value that defines how the Time value in a to be formatted.
  • am - A String to be used as a label for A.M. (morning) hours.
A String value. Suppose that the current time is 5:07:37 PM, the return value of the following statement is 09:07:37 am.

ToText("GMT", CurrentTime(), "hh:mm:ss a", "am")

ToText(String timezone, Time a, String pattern, String am, String pm) This function converts a Time value to a String.
  • timezone - A String value indicating the time zone. For the detailed usage, refer to java.util.TimeZone.
  • a - A Time value.
  • pattern - A String value that defines how the Time value in a to be formatted.
  • am - A String to be used as a label for A.M. (morning) hours.
  • pm - A String to be used as a label for P.M. (evening) hours.
A String value. Suppose that the current time is 5:07:37 PM, the return value of the following statement is 09:07:37 am.

ToText("GMT", CurrentTime(), "hh:mm:ss a", "am", "pm")

ToText(Time a) This function converts a Time value to a String.
  • a - A Time value.
A String value. Suppose the time is 12:40:30, the return value of the following statement is 12:40:30 PM.

ToText(ToTime(12,40,30))

ToText(Time a, String b) This function converts a Time value to a String.
  • a - A Time value to be converted.
  • b - A String that defines how the value in a to be formatted.
A String value. Suppose the time is 08:48:58, the return value of the following statement is 8:48:58.

ToText(ToTime(8,48,58), "h:mm:ss")

ToText(Time a, String b, String c) This function converts a Time value to a String.
  • a - A Time value to be converted.
  • b - A String that defines how the value in a to be formatted.
  • c - A String to be used as a label for A.M. (morning) hours.
A String value. Suppose the time is 8:48:58 in the morning, the return value of the following statement is 8:48:58 AM.

ToText(ToTime(8,48,58), "h:mm:ss a", "AM")

ToText(Time a, String b, String c, String d) This function converts a Time value to a String.
  • a - A Time value to be converted.
  • b - A String that defines how the value in a to be formatted.
  • c - A String to be used as a label for A.M. (morning) hours.
  • d - A String to be used as a label for P.M. (evening) hours.
A String value.
  • Suppose the time is 08:48:58, the return value of the following statement is 8:48:58 AM.

    ToText(ToTime(8,48,58), "h:mm:ss a", "AM", "PM")

  • Suppose the time is 21:49:59, the return value of the following statement is 9:49:59 PM.

    ToText(ToTime(21,49,59), "h:mm:ss a", "AM", "PM")

ToWords(Currency a) This function converts a Number field value or the result of a numeric calculation to words.
  • a - A Currency value to be converted into words.
A String value. The return value of the following statement is three and 15/100.

ToWords($3.15)

ToWords(Currency a, Integer b) This function converts a Number field value or the result of a numeric calculation to words.
  • a - A Currency value to be converted into words.
  • b - An Integer value indicating the number of decimal places to be converted. This argument is optional.
A String value. The return value of the following statement is three and 5/10.

ToWords($3.45, 1)

ToWords(Integer a) This function converts a Number field value or the result of a numeric calculation to words. a - An Integer value to be converted into words. A String value. The return value of the following statement is twenty and xx/100.

ToWords(20)

ToWords(Integer a, Integer b) This function converts a Number field value or the result of a numeric calculation to words.
  • a - An Integer value to be converted into words.
  • b - An Integer value indicating the number of decimal places to be converted. This argument is optional.
A String value. The return value of the following statement is three and x/10.

ToWords(3, 1)

ToWords(Number a) This function converts a Number field value or the result of a numeric calculation to words.
  • a - A fractional number to be converted into words.
A String value. The return value of the following statement is ten and 12/100.

ToWords(10.12)

ToWords(Number a, Integer b) This function converts a Number field value or the result of a numeric calculation to words.
  • a - A Number value to be converted into words.
  • b - An Integer value indicating the number of decimal places to be converted. This argument is optional.
A String value. The return value of the following statement is three and 1/10.

ToWords(3.14, 1)

Translate(String a) This function searches for the NLS translation of current field value in the bound data mapping file. For an illustrate example, see Built-in Functions for NLS.
  • a - A String to which a corresponding data mapping file can be bound.
A String value. It is the NLS translation of the field value if a data mapping file is bound to it and the NLS translation can be found, or null if the string is null, or the string itself when the corresponding NLS translation cannot be found in the data mapping file or the file cannot be found. The return value of the following statement is "美国" and the locale is China if the language of the data mapping file bound to it is Chinese and it contains the mapping: ["USA", "美国"].

Translate(@country), where @country has the value "USA".

Translate(String a, String b) This function accesses the corresponding data mapping file dynamically according to the language selected from the language drop-down list on the NLS language toolbar and searches for the NLS translation of String b in the data mapping file. For an illustrate example, see Built-in Functions for NLS. a - A String to which a corresponding data mapping file can be bound. A String value. It is the NLS translation of the field value if a data mapping file is bound to it and the NLS translation can be found, or null if the string is null, or the string itself when the corresponding NLS translation cannot be found in the data mapping file or the file cannot be found. The return value of the following statement is "美国" and the locale is China if the language of the data mapping file bound to it is Chinese and it contains the mapping: ["USA", "美国"].
  • Translate("mapping_file_country", "USA")
  • Translate("mapping_file_country", @country), where @country has the value "USA".
Trim(String a) This function is used to remove the leading and the trailing spaces from string arguments.
  • a - A String value.
A String value. The return value of the following statement is he is a boy.

Trim("he is a boy")

TrimLeft(String a) This function is used to remove the spaces on the left side of the string.
  • a - A String value to be trimmed.
A String value. The return value of the following statement is he is a boy.

TrimLeft("he is a boy")

TrimRight(String a) This function is used to remove the spaces on the right side of the string.
  • a - A String value to be trimmed.
A String value. The return value of the following statement is he is a boy.

TrimRight("he is a boy")

UpperCase(String a) This function converts all letters in string a to uppercase letters.
  • a - A String value.
A String value. The return value of the following statement is HE IS A BOY.

UpperCase("he is a boy")

Val(String a) This function reads a string containing numbers (for example, address, phone or social security number), and converts them to a decimal value. Val stops reading when it finds the first character in the string. a - A String value. A Number value. The return value of the following statement is 63,550,513.00.

Val("63550513Mr.Tom")

Soundex(string)

You need to pay attention to the following when using this function:

  • The value string must be in quotes.
  • Soundex works only with values that begin with the same letter. For example, Soundex will return the same value for Chris and Cris C620 but not for Kris K620. Soundex creates a code based on the first character in the string plus three characters based on the following:
    • Non-alpha characters like numbers and punctuation become -1.
    • The characters a, e, i, o, u, y, h, and w are ignored (except when they are the first character in the original string).
    • The characters b, f, p, and v become 1.
    • The characters c, g, j, k, q, s, x, and z become 2.
    • The characters d and t become 3.
    • The character l becomes 4.
    • The characters m and n become 5.
    • The character r becomes 6.
  • If the resulting code is only 2 or 3 characters long, Soundex uses zeros to fill it out to four characters. For example, in the name Lauren, only the L, r, and n are translated (Lrn), so the resulting Soundex code becomes L650.
  • If the resulting code is more than four characters long, all characters after the fourth character will be ignored. For example, in the name Patrick, the P, t, r, c, and k can be translated (Ptrck), but the resulting Soundex code will only be four characters P362.

ToText(Time a, String b, String c, String d)

You need to pay attention to the following when using this function:

  • When converting a Double value, Currency value or an Integer value to a String, special characters used as format in argument b are following:
    SymbolMeaningNotes
    0a digit 
    *a digit, zero shows as a starCan't mix 0, *, and _ in same format
    _a digit, zero shows as a spaceCan't mix 0, *, and _ in same format
    #a digit, zero shows as absent 
    .placeholder for decimal separator 
    ,placeholder for grouping delimiterShows the interval to be used
    ;separates formatspositive and negative.
    -if there is no explicit negative sign, - is prefixed"0.00" -> "0.00;-0.00"
    %divide by 100 and show as percentage 
    Xany other characters can be used in the prefix or suffix 
  • Time format syntax: The time format is specified by means of a String time pattern. In this pattern, all ASCII letters are reserved as pattern letters, which are defined as the following:
    SymbolMeaningPresentationExample
    Gera designatorTextAD
    yyearNumber1996
    Mmonth in yearText & NumberJuly & 07
    dday in monthNumber10
    hhour in am/pm (1~12) Number12
    Hhour in day (0~23)Number0
    mminute in hour Number30
    ssecond in minuteNumber55
    SmillisecondNumber978
    Eday in weekTextTuesday
    Dday in yearNumber189
    Fday of week in monthNumber2 (2nd Wed in July)
    wweek in yearNumber27
    Wweek in monthNumber2
    aam/pm markerTextPM
    khour in day (1~24)Number24
    Khour in am/pm (0~11)Number0
    ztime zoneTextPacific Standard Time
    ' escape for text  
    ''single quote  

Back to top

Others

Function Description Parameter Return Value Example
Choose(Integer, Array) This function returns a value from the array based on the value of Integer. For example, if integer is 0, it returns the first element in the array, and if index is 1 it returns the second element in the array.
  • Integer - A Number or numeric expression that specifies the index of the element. The minimum value is 0, and the maximum is the number of available elements minus 1. If it is out of bounds, no value will be returned.
  • Array - An array that contains all the available elements to choose from. All elements must be of the same type. A choice can be any simple type (Number, Currency, String, Boolean, Date, Time or DateTime), or range type (Number Range, Currency Range, String Range, Date Range, Time Range or DateTime Range), but it may not be an array.
A value from the element in the given array. The type of the returned value is the same as the type of the element.
  • Choose(1,["Poor","Fair","Good","Excellent"]) - Returns Fair.
  • Choose(2,["1 to 10","11 to 20", "21 to 30"]) - Returns 21 to 30.
  • Choose(2,[1,2,3,4,5,6]) - Returns 3.
  • Choose(2,[Todate('1998/5/4'),ToDate('1999/5/5'),ToDate('1996/5/6')]) - Returns 5/6/96.
currentBurstingSchema() This function returns the names of the applied bursting schemas in the current report. When isRunBursting() returns false, the returned value of currentBurstingSchema() is NULL.   A String value. If the current report has been applied two bursting schemas: VP and Manager, the return value of the following statement is VP,Manager:

currentBurstingSchema()

eqv(booleanx, booleany)

This function returns a Boolean value of booleanx eqv booleany,

  • if x = true and y = true, returns the value of true.
  • if x = true and y = false, returns the value of false.
  • if x = false and y = true, returns the value of false.
  • if x = false and y = false, returns the value of true.
  • booleanx - A Boolean value.
  • booleany - A Boolean value.
A Boolean value.
  • eqv(true, true) - Returns true.
  • eqv(true, false) - Returns false.
GetInfo(String) This function is used to get information of a given key in the global level information container of the Information Bus.
  • String - A String indicating the information in the container.
A String value. If you want to get information containing a key-value pair, TestKey and TestValue, use the following statement:

GetInfo("TestKey") - Returns TestValue.

getLanguage() This function returns the language name of the locale that the current running task is based on.   The return value will either be the empty string or a lowercase ISO 639 code. When the locale is set to en_US, the return value of the following statement is en:

getLanguage()

getLocale() This function returns the language and locale name that the current running task is based on.   A string value containing language and locale information, for example, country. When the locale is set to en_US, the return value of the following statement is en_US:

getLocale()

GetOrgInfo(String) This function is used to get information of a given key in the organization level information container of the Information Bus that the current user can access.
  • String - A String indicating the information in the container.
A String value. If you want to get information containing a key-value pair, TestKey and TestValue, use the following statement:

GetOrgInfo("TestKey") - Returns TestValue.

getSecurityContext() This function returns a Security Context object, which provides the method get() to get the Security Context instance from Logi Report Server or Logi Report Designer.   A DbSecurityContext object. Import userClass from "UserFunction";
userClass.getData(getSecurityContext(), @country, …);
GetUserInfo(String) This function is used to get information of a given key in the user level information container of the Information Bus that the current user can access.
  • String - A String indicating the information in the container.
A String value. If you want to get information containing a key-value pair, TestKey and TestValue, use the following statement:

GetUserInfo("TestKey") - Returns TestValue.

isAll(Array) This function checks whether the value of the specified parameter is "ALL".   A Boolean value. When the parameter PEndDate supports multiple values and its value is set to "All", the following statement returns true.

isAll(@PEndDate)

isCountry(String) This function compares the specified parameter with that of the country setting of JVM based on default locale. The specified string must be an uppercase 2-letter ISO 3166 code.   A Boolean value.
  • If getLanguage() return zh, isCountry("CN") will return true.
  • If getLanguage() return en, isCountry("CN") will return false.
IsNoRecord() This function tells whether a report has returned a record or not. If the report has no value returned, the function will return True, otherwise False.

A formula that calls this function cannot be applied to queries, and this formula only takes effect when laying out this report which contains it.

  A Boolean value. When using this function, you should refer to a DBField to identify whether this formula is a record level formula so that it can be calculated when fetching the record. For example,

@"Customers_Customer ID";
if(IsNoRecord())
    return "There is no data"
else
    return "Total number of records="+@"Count_Customer Name5"

In this example, if no data returned, a tip "There is no data" will be displayed. If data is returned, the Total number of records will be displayed.

IsNull(DBfield a) This function tells whether a specified value (especially the value of a DBField) is null or not. If the value is Null, the function will return True, otherwise False.
  • a - A specified value especially a value of a DBField.
A Boolean value.
  • The return value of the following statement is false.

    IsNull(2.5)

  • If you build a report about customer order, suppose one of the values of the field "Shipped" is Null, the return value of the following statement is true.

    IsNull(@shipped)

  • Integer Total=0;
    if (!isnull(@grandTotal))
    Total= Total+@grandTotal;
    else
    Total=Total+0;

    This formula can summarize the Grand Total while ignoring the null value or no-record column.

isNumeric(inString) This function returns a Boolean value if the inString is a math number string.
  • inString - A String type value.
A Boolean value.
  • isNumeric("true") - Returns false.
  • isNumeric("false") - Returns false.
  • isNumeric("1234") - Returns true.
  • isNumeric("122 322323") - Returns false.
isRunBursting() This function returns true if the current report is running based on a bursting schema, else returns false.   A Boolean value.
  • If a bursting report is scheduled to run based on a bursting schema, the return value of the following statement is true:

    isRunBursting()

  • If a bursting report is scheduled to run a normal result, the return value of the following statement is false:

    isRunBursting()

Next(DBfield a) This function returns the next value of the current DBField.
  • a - A DBField value.
A DBField value. Suppose you build a report about customer orders. If you use this function on "Ship Date" and insert it into the Detail Section, then when you run the report, you will see after each record, the next records will be displayed according to the following statement.

Next(@"Ship Date")

Next(DBfield a, Integer b) This function returns the next Nth record decided by the argument b.
  • a - A DBField value.
  • b - An Integer value.

Note: Due to some implementation limitation, the argument b cannot be equal to or larger than 2.

A DBField value. Suppose you build a report about customer orders. If you use this function on "Ship Date" and set the argument b as 1, then when you run the report, you will see after each record, the next first record will be displayed according to the following statement.

Next(@"Ship Date", 1)

nextMember() This function is used in the custom aggregation expression to locate the next member of a group or detail object.

 

Suppose that a group or detail object contains x members (confined to all parent groups' current members) and the custom aggregation is executed on the ith member, if i = x, this function cannot locate any member. A member of a group or detail object. When a detail object contains these members: 10, 20, 30, 40, 50, and the custom aggregation is executed on 20, the following expression returns 30.

nextMember()

nextMember(n) This function is used in the custom aggregation expression to locate the next nth member of a group or detail object.

 

  • n - An integer starting from 1.

Suppose that a group or detail object contains x members (confined to all parent groups' current members) and the custom aggregation is executed on the ith member, if i + n > x , this function cannot locate any member.

A member of a group or detail object. When a detail object contains these members: 10, 20, 30, 40, 50, and the custom aggregation is executed on 20, the following expression returns 50.

nextMember(3)

nextMember(n, start) This function is used in the custom aggregation expression to locate the next nth member after the member specified in the start parameter.

 

  • n - An integer starting from 1.
  • start - A constant member of a group or detail object such as 'USA', FIRSTMEMBER, and LASTMEMBER.
  • FIRSTMEMBER - Always locates the first member of a group or detail object.
  • LASTMEMBER - Always locates the last member of a group or detail object.

Suppose that a group or detail object contains x members (confined to all parent groups' current members), when start is the jth member, if j + n > x , this function cannot locate any member.

A member of a group or detail object. When a detail object contains these members: 10, 20, 30, 40, 50, the following expression returns 50.

nextMember(2,30)

openBinFile(string a) This function is used to open an image file which is saved in your file system according to the specified path. It allows user access to the computer file system and possibly passwords which may cause security issue, so if you have security concerns you can disable the function by setting the propertyenable_openBinFile to false in the FormulaConfig.properties file in <install_root>\bin. After being disabled, the function will return null but is still available in the Formula Editor for reference.
  • a - A String value which indicates the full path of the image file.
The return value is a binary image file. Suppose you have an image file photo1.gif which is saved in the following directory c:\images. The following example will open this image file.

openBinFile("c:\\images\\photo1.gif")

openBinURL(string a) This function is used to open an image file according to the specified URL. It allows user access to the computer file system and possibly passwords which may cause security issue, so if you have security concerns you can disable the function by setting the property enable_openBinURL to false in the FormulaConfig.properties file in <install_root>\bin. After being disabled, the function will return null but is still available in the Formula Editor for reference.
  • a - A String value which indicates the URL of the image file.
A binary image file. Suppose you have an image file in the following URL http://www.jinfonet.com/../../asset/images/Pic1.gif. The following example will open this image file Pic1.gif.

openBinURL("http://www.jinfonet.com/../../asset/images/Pic1.gif")

openTxtFile(string a) This function is used to open a text file which is saved in your file system according to the specified path. It allows user access to the computer file system and possibly passwords which may cause security issue, so if you have security concerns you can disable the function by setting the property enable_openTxtFile to false in the FormulaConfig.properties file in <install_root>\bin. After being disabled, the function will return null but is still available in the Formula Editor for reference.
  • a - A String value which indicates the full path of the text file.
A long varchar value. Suppose you have a text file report.int in the following directory C:\LogiReport\Designer. The following example will open this text file report.ini.

openTxtFile("C:\\LogiReport\\Designer\\report.ini")

openTxtURL(string a) This function is used to open a text file according to the specified URL. It allows user access to the computer file system and possibly passwords which may cause security issue, so if you have security concerns you can disable the function by setting the property enable_openTxtURL to false in the FormulaConfig.properties file in <install_root>\bin. After being disabled, the function will return null but is still available in the Formula Editor for reference.
  • a - A String value which indicates the URL of the text file.
A long varchar value. Suppose you have a text file at the following URL http://www.logianalytics.com/logireport/report.ini. The following example will open the text file report.ini.

openTxtURL("http://www.logianalytics.com/logireport/report.ini")

Prev(DBfield a) This function returns the previous value of the current DBField.
  • a - A DBField value.
A DBField value. Suppose you build a report about customer orders. If you use this function on "Ship Date" and insert it into the Detail Section, after the report has been run, you will see the previous records displayed after each record, according to the following statement.

Prev(@"Ship Date")

Prev(DBfield a, Integer b) This function returns the previous Nth record decided by the argument b.
  • a - A DBField value.
  • b - An Integer value.

Note: Due to some implementation limitation, the argument b cannot be equal to or less than -2.

A DBField value. Suppose you build a report about customer orders. If you use this function on "Ship Date" and set the argument b as 4, then when you run the report, you will see before each record, the previous 4th record displayed according to the following statement.

Prev(@"Ship Date", 4)

prevMember() This function is used in the custom aggregation expression to locate the previous member of a group or detail object.

 

Suppose that a group or detail object contains x members (confined to all parent groups' current members) and the custom aggregation is executed on the ith member, if i = 1, this function cannot locate any member. A member of a group or detail object. When a detail object contains these members: 10, 20, 30, 40, 50, and the custom aggregation is executed on 20, the following expression returns 10.

prevMember()

prevMember(n) This function is used in the custom aggregation expression to locate the previous nth member of a group or detail object.

 

  • n - An integer starting from 1.

Suppose that a group or detail object contains x members (confined to all parent groups' current members) and the custom aggregation is executed on the ith member, if i - n < 1 , this function cannot locate any member.

A member of a group or detail object. When a detail object contains these members: 10, 20, 30, 40, 50, and the custom aggregation is executed on 40, the following expression returns 10.

prevMember(3)

prevMember(n, start) This function is used in the custom aggregation expression to locate the previous nth member before the member specified in the start parameter.
  • n - An integer starting from 1.
  • start - A constant member of a group or detail object such as 'USA', FIRSTMEMBER, and LASTMEMBER.
  • FIRSTMEMBER - Always locates the first member of a group or detail object.
  • LASTMEMBER - Always locates the last member of a group or detail object.

Suppose that a group or detail object contains x members (confined to all parent groups' current members), when start is the jth member, if j - n < 1 , this function cannot locate any member.

A member of a group or detail object. When a detail object contains these members: 10, 20, 30, 40, 50, the following expression returns 30.

prevMember(2,50)

PutInfo(String a, String b) This function is used to put or replace information of a given key in the global level information container of the Information Bus.
  • a - A String indicating the information in the container.
  • b - A String value.
No return value. If you want to put information containing a key-value pair, TestKey and TestValue, use the following statement:

PutInfo("TestKey", "TestValue")

PutOrgInfo(String a, String b) This function is used to put or replace information of a given key in the organization level information container of the Information Bus that the current user can access.   No return value. If you want to put information containing a key-value pair, TestKey and TestValue, use the following statement:

PutOrgInfo("TestKey", "TestValue")

PutUserInfo(String a, String b) This function is used to put or replace information of a given key in the user level information container of the Information Bus that the current user can access.
  • a - A String indicating the information in the container.
  • b - A String value.
No return value. If you want to put information containing a key-value pair, TestKey and TestValue, use the following statement:

PutUserInfo("TestKey", "TestValue")

RemoveInfo(String) This function is used to remove information of a given key in the global level information container of the Information Bus.
  • String - A String indicating the information in the container.
No return value. If you want to remove information containing a key named TestKey, use the following statement:

RemoveInfo("TestKey")

RemoveOrgInfo(String) This function is used to remove information of a given key in the organization level information container of the Information Bus that the current user can access.
  • String - A String indicating the information in the container.
No return value. If you want to remove information containing a key named TestKey, use the following statement:

RemoveOrgInfo("TestKey")

RemoveUserInfo(String) This function is used to remove information of a given key in the user level information container of the Information Bus that the current user can access.
  • String - A String indicating the information in the container.
No return value. If you want to remove information containing a key named TestKey, use the following statement:

RemoveUserInfo("TestKey")

reportName() This function returns the current report name.   A String value. If the current report name is Employee Information List, the return value of the following statement is Employee Information List.

reportName()

Switch(Boolean[], Array) The elements in the two parameters corresponding with each other. This function evaluates the elements in the first parameter from left to right, and returns element associated with the first element to evaluate to True. For example,

Switch([1, 2, 3], [a, b, c])

if 1 is true, Switch returns a. If 2 is true, Switch returns b. If 3 is true, Switch returns c.

  • Boolean[] - A Boolean type array, which contains the elements that are to be evaluated.
  • Array - An array, which contains all the available values that may be returned.
One of the elements of the parameter array. The type of the returned value is the same as the element in the array. Insert the following function into the Detail section of a report,

Switch([@"Customers_Customer ID"< 5, @"Customers_Customer ID" > 50,true],["small", "large", "medium"])

  • if Customer ID < 5 is true, returns small.
  • if Customer ID >50 is true, returns large.
  • if 5 < Customer ID < 50 (true), returns medium.
toBool(number or currency) This function returns True if the parameter is positive or negative but not 0, and returns False if the parameter is 0. Can be either a Number, Currency value, or expression. A Boolean value. toBool(@Discount) - Returns false if the discount is 0. Otherwise, returns true.
toNumber(Boolean) This function returns 1 if the parameter is True, and returns 0 if the parameter is False.   1 or 0.
  • toNumber(True) - Returns 1.
  • toNumber(False) - Returns 0.
xor(booleanx, booleany) This function returns a Boolean value of booleanx Exclusive OR booleany,
  • if x = true and y = true, returns the value of false.
  • if x = true and y = false, returns the value of true.
  • if x = false and y = true, returns the value of true.
  • if x = false and y = false, returns the value of false.
  • booleanx - A Boolean value.
  • booleany - A Boolean value.
A Boolean value.
  • xor(true, true) - Returns false.
  • xor(true, false) - Returns true.
  • xor((@"Customer ID"<=20),(Remainder(@"Customer ID", 2) == 0));

Back to top

BackPrevious Topic Next TopicNext