Appendix 1: Formula Functions

• Any(array_variable);
• Any(field_variable);

• 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.

• 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(array_variable);
• Average(field_variable);
• Average(field_variable, groupby);

• 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 groupby 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.

• 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(array_variable);
• Count(field_variable);
• Count(field_variable, groupby);

• 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 groupby 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.

• 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(array_variable);
• DistinctCount(field_variable);
• DistinctCount(field_variable, groupby);

• 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 groupby 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.

• 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(["str1","str1","str3","str4","str4","str6"])}
• 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(array_variable);
• Every(field_variable);

• 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.

• 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(array_variable);
• Maximum(field_variable);
• Maximum(field_variable, groupby);

• 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 groupby 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.

• 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(array_variable);
• Minimum(field_variable);
• Minimum(field_variable, groupby);

• 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 groupby 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.

• 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(array_variable);
• PopulationStdDev(field_variable);
• PopulationStdDev(field_variable, groupby);

• 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 groupby 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.

• 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(array_variable);
• PopulationVariance(field_variable);
• PopulationVariance(field_variable, groupby);

• 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 groupby 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.

• 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(array_variable);
• StdDev(field_variable);
• StdDev(field_variable, groupby);

• 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 groupby 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.

• 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(array_variable);
• Sum(field_variable);
• Sum(field_variable, groupby);

• 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 groupby 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.

• 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(array_variable);

• array_variable - Refers to an Array value, expression or variable.

• 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(array_variable);
• Variance(field_variable);
• Variance(field_variable, groupby);

• 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 groupby 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.

• 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)

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

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

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

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

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

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

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

return Today() in Calendar1stHalf()

return Today() in Calendar1stQtr()

return Today() in Calendar2ndHalf()

return Today() in Calendar2ndQtr()

return Today() in Calendar3rdQtr()

return Today() in Calendar4thQtr()

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CurrentDate()

CurrentDateTime()

CurrentTime()

Returns a DateTime value to which a specified number of time intervals have been added.

This function 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.

• 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.

Overloads:

• DateDiff(intervalType, startDateTime, endDateTime)
• DateDiff(intervalType, startDateTime, endDateTime, firstDayOfWeek)

• 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

• 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.

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)

• 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).

  Constant	Value	Description
  jrUseSystem	0	Start with week in which system date occurs
  jrFirstJan1	1	Start with week in which January 1 occurs (default)
  jrFirstFourDays	2	Start with the first week that has at least four days in the new year
  jrFirstFullWeek	3	Start with first full week of the year

• 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.

• 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.

• 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.

• a - A TimeStamp value.

DateTimeToDate(CurrentDateTime())

• a - A TimeStamp value.

DateTimeToSeconds(CurrentDateTime())

• a - A TimeStamp value.

DateTimeToTime(CurrentDateTime())

• 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.

• 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()

• a - A Date value.

Day(CurrentDate())

• a - A TimeStamp value.

Day(CurrentDateTime())

DayOfWeek()

• a - A Date value.

DayOfWeek(CurrentDate())

• a - A TimeStamp value.

DayOfWeek(CurrentDateTime())

DayOfYear()

• 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.

• 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.

• string - A String which includes a Date and a Time value.

• a - A Date value.

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

• a - A Date value.

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

• a - A Date value.

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

• a - A Date value.

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

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

• a - A Date value.

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

• a - A Date value.

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

• a - A DateTime value.

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

• a - A DateTime 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))

• a - A DateTime 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))

• a - A DateTime value.

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

• a - A DateTime 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))

• a - A DateTime value.

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

• a - A DateTime value.

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

Hour()

• a - A TimeStamp value.

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

• a - A Time value.

Hour(ToTime(10, 5, 26))

• 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.

• 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.

• 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.

• 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.

• 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.

• 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()

• a - A DateTime value.

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

• a - A Time value.

Minute(ToTime(8,30,27))

Month()

• a - A Date value.

Month(ToDate(1999, 6, 15))

• a - A TimeStamp value.

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

• 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.

• MonthName(4) - Returns String value "April".
• MonthName(12, true) - Returns String value "Dec".
• MonthName(12, false) - Returns String value "December".

Now()

Overloads:

• Quarter()
• Quarter(Date,Integer)
• Quarter(Datetime,Integer)

• 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.

• 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()

• a - A TimeStamp value.

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

• a - A Time value.

Second(ToTime(7, 51, 59))

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

There is a built-in expression Selected Date in Calendar calling this function. The expression can be found in the Name drop-down list of the Calendar dialog.

• 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.

• 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.

• a - A Date value.

ToDate(ToDate(1999, 7, 15))

• a - A TimeStamp value.

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

• d - A Milliseconds value, a DBField value or a parameter. It should be a long integer.

ToDate(72000000)

• y - A BigInt value indicating year.
• m - A BigInt value indicating month.
• d - A BigInt value indicating day.

ToDate(1999, 7, 15)

• 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.

• 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.

• a - A Date value that can be converted to a DateTime type value by this function.

• a - A Date value.
• b - A Time value.

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

• d - A Milliseconds value, a DBField value or a parameter. It should be a long integer.

• 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)

• y - A BigInt value indicating year.
• m - A BigInt value indicating month.
• d - A BigInt value indicating day.

ToDateTime(1999, 7, 15)

• y - A BigInt value indicating year.
• m - A BigInt value indicating month.
• d - A BigInt value indicating day.
• h - A BigInt value indicating hour.
• i - A BigInt value indicating minute.
• s - a BigInt value indicating second.

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

Today()

• 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.

• 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.

• a - A TimeStamp value.

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

• t - A milliseconds value, a DBField value or a parameter. It should be a long integer.

ToTime(1000)

• a - A BigInt value, indicating hours.
• b - A BigInt value, indicating minutes.
• c - A BigInt value, indicating seconds.

ToTime(10, 10, 10)

Overloads:

• WeekdayName(weekday)
• WeekdayName(weekday, abbreviate)
• WeekdayName(weekday, abbreviate, firstDayOfWeek)

• 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

• 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()

• a - A Date value.

WeekFrom1970(ToDate(1995, 7, 8))

• a - A DateTime value.

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

WeekOfMonth()

• a - A Date value.

WeekOfMonth(ToDate(1999, 10, 13))

• a - A TimeStamp value.

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

WeekOfYear()

• a - A Data value.

WeekOfYear(ToDate(1999, 7, 8))

• a - A TimeStamp value.

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

Year()

• a - A Data value.

Year(ToDate(1995, 1, 15))

• a - A TimeStamp value.

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

Overloads:

• DDB(cost, salvage, life, period)
• DDB(cost, salvage, life, period, factor)

• 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.

• 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.

• accountReceivable - A Double value indicating the account receivable.
• sales - A Double value indicating the sales.
• numOfDays - A Double value indicating the number of days.

• 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.

• cashFlow - A Double value indicating the cash flow.
• totalDebt - A Double value indicating the total debt.

FRCashFlowVsTotalDebt(250000.00, 280000.00)

• curAssets - A Double value indicating the current ratio.
• curLiabilities - A Double value indicating the current liabilities.

FRCurrentRatio(1800000.00, 150000.00)

• totalLiabilities - A Double value indicating the total liabilities.
• totalEquity - A Double value indicating the total equity.

FRDebtEquityRatio(175000.00, 215000.00)

• dividend - A Double value indicating the dividend.
• marketPrice - A Double value indicating the market price.

FRDividendYield(9.85, 10.87)

• 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.

FREarningsPerCommonShare(300000.00, 180000.00, 10000)

• totalEquity - A Double value indicating the total equity.
• totalAssets - A Double value indicating the total assets.

FREquityVsTotalAssets(215000.00, 2200000.00)

• grossProfit - A Double value indicating the gross profit.
• sales - A Double value indicating the sales.

FRGrossProfitMargin(350000.00, 450000.00)

• cashFlow - A Double value indicating the cash flow.
• interestExpenses - A Double value indicating the interest expenses.

FRInterestCoverage(250000.00, 350000.00)

• inventory - A Double value indicating the inventory.
• sales - A Double value indicating the sales.
• numOfDays - A Double value indicating the number of days.

FRInventoryTurnover(165000.00, 450000.00, 100)

• netProfit - A Double value indicating the net profit.
• sales - A Double value indicating the sales.

FRNetProfitMargin(300000.00, 450000.00)

• operatingProfit - A Double value indicating the operating profit.
• sales - A Double value indicating the sales.

FROperatingProfitMargin(380000.00, 450000.00)

• marketPrice - A Double value indicating the market price.
• earningPerShare - A Double value indicating the earning per share.

FRPriceEarningsRatio(10.87, 12)

• curAssets - A Double value indicating the current assets.
• inventories - A Double value indicating the inventory.
• curLiabilities - A Double value indicating the current liabilities.

FRQuickRatio(1800000.00, 165000.00, 150000.00)

• netProfit - A Double value indicating the net profit.
• preferredDividend - A Double value indicating the preferred dividend.
• commonEquity - A Double value indicating the common equity.

FRReturnOnCommonEquity(300000.00, 180000.00, 208000.00)

• netProfit - A Double value indicating the net profit.
• totalEquity - A Double value indicating the total equity.

FRReturnOnEquity(300000.00, 215000.00)

• 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.

FRReturnOnInvestedCapital(300000.00, 100000.00, 215000.00)

• netProfit - A Double value indicating the net profit.
• netFixedAssets - A Double value indicating the net fixed assets.

FRReturnOnNetFixedAssets(300000.00, 400000.00)

• netProfit - A Double value indicating the net profit.
• totalAssets - A Double value indicating the total assets.

FRReturnOntotalAssets(300000.00, 2200000.00)

Overloads:

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

• 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.

• 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.

Overloads:

• IPmt(rate, period, periods, presentMoney)
• IPmt(rate, period, periods, presentMoney, futureMoney)
• IPmt(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.

• 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.

Overloads:

• IRR(values)
• IRR(values, guess)

• 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).

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.

• 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.

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

Overloads:

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

• 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.

• 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.

• 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.

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.

Overloads:

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

• 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.

• 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).

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.

• 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.

Overloads:

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

• 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.

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.

Overloads:

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

• 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).

• 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.

• 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.

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

• 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.

• 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.

• a - A BigInt value.

Abs(-3534) and Abs(3534)

• a - A Currency value.

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

• a - A Double value.

Abs(2.4785) and Abs(-2.4785)

• number - A Number value.

• a - Values of DBField or formula field. Can be Number or Currency data type.

• Number x = Average(@dbfield)
• Number x = Average(@formula)
• Currency x = Average(@dbfield)
• Currency x = Average(@formula)

• a - Values of DBField or formula field. Can be Number or Currency data type.
• groupby - A constant string to indicate the groupby field name or a field variable grouped by or a parameter variable grouped by.

• 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)

• a - A Number or Currency array.

• 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 ])

• number - An angle in number of radians.

• 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.

• a - A Double array.

• 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.

• a - Values of DBField or formula field.

• Number x = Count(@dbfield)
• Number x = Count(@formula)

• 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.

• Number y = Count(@"Customer Name", @Customers_Region)
• Number x = Count(@formula, @formula_groupby)
• Number x = Count(@dbfield, @parametger_groupby)

• a - Values of a DBField or a formula field.

DistinctCount(@"Order Date")

• 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.

• Number x = DistinctCount(@dbfield, @dbfield1)
• Number x = DistinctCount(@formula, @formula_groupby)
• Number x = DistinctCount(@dbfield, @parameter_groupby)

• a - A Number array.

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

• inNumber - Number value that specifies a power.

• exp(1.5) - Returns the exponentiation of 1.5, which is approximately 4.4817.
• exp(10) - Returns 22,026.47.

• numbera - A Number value.
• numberb - A Number value.

• number - The Number value that you want to calculate on.

• log(1.5) - Returns 0.406 (rounded to 3 decimals).
• log(22026.47) - Returns 10.

• a - An array, data type can be Number or Integer.

• Maximum([ 22.5, 75.81, 236.47, 56.31, 235.78 ])
• Maximum([ 4, 9, 34, 80, 200 ])

• a - Values of a DBField or a formula field.

• Number x = Maximum(@dbfield)
• Date x = Maximum(@formula)
• Currency x = Maximum(@dbfield)
• Boolean x = Maximum(@formula)

• a - Values of a DBField or of a formula field.
• groupby - A constant string to indicate the DBField name, formula or parameter grouped by.

• 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)

• a - An array, data type can be Number or Integer.

• 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)

• a - Values of a DBField or a formula field.

• Number x = Minimum(@dbfield)
• Date x = Minimum(@formula)
• Currency x = Minimum(@dbfield)
• Boolean x = Minimum(@formula)

• a - Values of a DBField or a formula field.
• groupby - A constant string to indicate the DBField name, formula or parameter grouped by.

• 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)

Overloads:

• MRound(Number,Number)
• MRound(Currency,Number)
• MRound(Currency,Currency)

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.

• MRound(11.0,4.0) - Return 12.0.
• MRound($11.0,-4.0) - Return 12.0.
• MRound($-7.0,$2.0) - Return -8.0.

• PopulationStdDev(a[ ])
• PopulationStdDev(DBField field)
• PopulationStdDev(DBField field, String groupby)

• 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.

• 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(a[ ])
• PopulationVariance(DBField field)
• PopulationVariance(DBField field, String groupby)

• 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.

• 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)

• numbera - A Number value.
• numberb - A Number value.

• pow(2, 4) - Returns 16.
• pow(6.2, 5.1) - Returns 10995.060323919315.

Overloads:

• random()
• random(seed)

• seed - An optional Number value parameter.

• random() - Returns: 0.673411041443785
• random(123) - Returns: 0.06300625845328678
• random(0) - Returns: 0.06300625845328678
• random(-122223) - Returns: 0.3291484275937213

• a - An Integer value.
• b - 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.

• a - A Double value.

Round(2.754)

• a - A Double value.
• b - A Scale value.

Round(2.4576, 2)

• a - A double value which will be rounded.
• b - The rounded scale.
• c - The rounding mode.

Round(2.021, 2, 0)

• a - A double 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.

The return value of the following statement is 123.457.

Round(123.4567, 3, 'UP')

• RunningAvg(field_variable);
• RunningAvg(field_variable, groupby);

• RunningCount(field_variable);
• RunningCount(field_variable, groupby);

• RunningDistinctCount(field_variable);
• RunningDistinctCount(field_variable, groupby);

• RunningMaximum(field_variable);
• RunningMaximum(field_variable, groupby);

• RunningMinimum(field_variable);
• RunningMinimum(field_variable, groupby);

• RunningSum(field_variable);
• RunningSum(field_variable, groupby);

• data - The value you want to know the sign of.

• sgn(2) - Returns 1.
• sgn(-23) - Returns -1.
• sgn(0) - Returns 0.

• number - An angle in number of radians.

• 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.

• number - A Number value greater than or equal to 0.

• StdDev(a[ ])
• StdDev(field)
• StdDev(field, groupby)

• a - A Number array.
• field - Values of DBField or formula field.
• groupby - A constant string which indicates the DBField, formula or parameter grouped by.

• 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(Number a[ ])
• Sum(Integer a[ ])
• Sum(Currency a[ ])
• Sum(DBField field)
• Sum(DBField, String groupby)

• 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.

• 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)

• number - An angle in the form of number of radians.

• 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.

• a - A BigInt value.

ToBinary(11)

• a - A BigInt value.

ToHex(32)

• a - A Number value.

ToInt(100.1)

• a - A BigInt value.

ToOctal(52)

• a - A Double value to be truncated.

Truncate(25.4321)

• a - A Double value to be truncated.
• b - A Double value indicating the number of the decimal places.

Truncate(25.432, 2)

• Variance(Number a[ ])
• Variance(Currency a[ ])
• Variance(DBField field)
• Variance(DBField field, String groupby)

• a - A Number array.
• field - Values of DBField or formula field.
• groupby - A constant string which indicates the DBField, formula or parameter grouped by.

• 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)

• a - A Char value.

Asc("abc")

• 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.

• ByteToText(1000) - Returns 1000 bytes.
• ByteToText(251561) - Returns 245 KB.
• ByteToText(10000000) - Returns 9 MB.
• ByteToText(25454464) - Returns 24 MB.

• a - A BigInt value.

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

  Chr(99)

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

  Chr(123)

• 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.

• address - The address of sender/recipient (String data type).

• ExchGetID("/o=Jinfonet Software/ou=JREPORT /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/jreport/Jenny") - Returns "com/jreport/Jenny".

• 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).

• address - The address of the sender/recipient (String data type).

• ExchGetOrganization("c=US ;a= ;p=Jinfonet; o=apps-wga; dda:ms=com/jreport/Jenny") - Returns "Jinfonet".
• ExchGetOrganization("c=US ;a= ;p=Jinfonet; o=apps-wga; dda:smtp=Jenny@jinfonet.com") - Returns "Jinfonet".

• 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).

• ExchGetPath("/o=Jinfonet Software/ou=JREPORT /cn=Configuration/cn=Servers/cn=ESPRESSO/cn=Fredt") - Returns "cn=Configuration/cn=Servers/cn=ESPRESSO".
• ExchGetPath("c=US; a= ; p=Jinfonet; ou1=Jenny; ou2=JReport; o=apps-wga;") - Returns "ou1=Jenny; ou2=JReport".
• ExchGetpath("DDA:MS=JREPORT/JENNY/jreport") - Returns "MS=JREPORT/JENNY/jreport".

• 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).

• address - The address of sender/recipient (String data type).

• ExchGetSite("/o=Jinfonet Software/ou=JReport/cn=Configuration/cn=Servers/cn=ESPRESSO/cn=Fredt") - Returns "JReport".
• ExchGetSite("c=US; a= ;p=Jinfonet;o=apps-wga; dda:smtp=Jenny@jinfonet.com") - Returns "apps-wga".

• origin - A String value.
• start - A String value.
• end - 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.

Overloads:

• filter(inString, searchString)
• filter(inString, searchString, include)
• filter(inString, searchString, include, compare)

• 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 substrings that include or exclude searchString. If include is True, Filter returns the subset of the array that contains searchString as a substring. If include is False, Filter returns the subset of the array that does not contain searchString as a substring. 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.

• 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) - Returns ["abc", "abcdfg", "asdfabc"]
• 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.

• a - A String value to be sought.
• b - A String value to be searched.

• 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.

• a - A BigInt 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.

• 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")

Overloads:

• join(stringArray)
• join(stringArray, delimiterString)

• 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.

• join(list) - Returns the string Welcome Use JReport.
• join(list, ", ") - Returns the string Welcome, Use, JReport.

• a - A String value to be searched.
• b - A String value to be sought.

• The return value of the following statement is 9.

  LastIndexOf("avcievmgbvi","v")

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

  LastIndexOf("avcievmgbvi","n")

• a - A BigInt 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.

• a - A String value.
• b - A BigInt value indicating the number of characters to be extracted from String a.

Left("he is my father", 5)

• a - A String value.

Length("she is my mother")

• a - A String value to be compared to the mask.
• b - A String that provides a mask for comparing the value in String a.

• 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.

• a - A String value.

LowerCase("This is HER book")

• a - A String value to be extracted.
• b - A BigInt value indicating the position of the first character to extract.

Mid("he is my father", 6)

• a - A String value to be extracted.
• b - A BigInt value indicating the position of the first character to extract.
• c - A BigInt value indicating the number of characters to be extracted from String a.

• 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.

• a - A String value to be tested.

• 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")

• 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.

• 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.

Overloads:

ProperCase(String)

• String - A text string.

• 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".

Overloads:

• ReplaceString(inString, searchString, replaceString)
• ReplaceString(inString, searchString, replaceString, startPosition)
• ReplaceString(inString, searchString, replaceString, startPosition, count)
• ReplaceString(inString, searchString, replaceString, startPosition, count, compare)

• inString - A String containing substring to replace.
• searchString - A substring being searched for.
• replaceString - The replacement substring.
• startPosition - An optional Number indicating the position within inString where the substring search is to begin from. If omitted, 1 will be used.
• count - An optional Number of substring 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 substrings:
  • 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

• 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".

• a - A String value to be replicated.
• b - A BigInt value indicating the number of times that String a is to be replicated.

ReplicateString("Stop!", 3)

• a - A String value to be extracted.
• b - A BigInt value indicating the number of characters to be extracted from String a.

The return value of the following statement is mother.

Right("She is my mother", 6)

• string - One of two or more strings that sound alike.

• Soundex("Jinfonet") - Returns J515.
• if (Soundex("George") == Soundex("gorge")) then
"Sound the same"
else
"Different sound"

  - Returns Sound the same.

• a - A BigInt value indicating the number of spaces.

The return value of the following statement is   .

Space(3)

• a- A String value.
• b- A String 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")

Overloads:

• stringSplit(inString)
• stringSplit(inString, delimiterString)
• stringSplit(inString, delimiterString, count)
• stringSplit(inString, delimiterString, count, compare)

• inString - A String expression containing substrings and delimiters.
• delimiterString - An optional String character used to identify substring 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.

• StringSplit(inString) - Returns [ "Welcome", "use", "JReport"]
• StringSplit(inString, "use") - Returns [ "Welcome", "JReport"]
• StringSplit(inString, " ", 2) - Returns [ "Welcome", "use JReport"]

• inString - A String whose characters are to be reversed.

• strReverse("abc") - Returns cba.
• strReverse("false") - Returns eslaf.

• a - A String value.

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

• a - A Currency 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)

• a - A Char value.

The return value of the following statement is 123.00.

ToNumber("123")

• a - 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);

• a - A Bit value.

ToText(3<2)

• a - A Currency value to be converted.

ToText($123.45)

• a - A Currency value to be converted.
• b - A String indicating the format for displaying the value in a.

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

• 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.

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

• 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.

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

• 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.

• 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, "&", "*")

• a - A Currency value to be converted.
• c - The number of decimal places to be converted.

ToText($123.4567, 2)

• 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.

ToText($1234.567, 2, ",")

• 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.

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

• a - A Data value.

ToText(ToDate(1999,7,15))

• a - A Date value.
• b - A String that defines how the Data value in a to be formatted.

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

• a - A TimeStamp value to be converted.

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

• a - A TimeStamp value to be converted.
• b - A String that defines how the DateTime value in a to be formatted.

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

• a - A TimeStamp 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.

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

• a - A TimeStamp 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.

• 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")

• a - A BigInt value to be converted.

ToText(456)

• a - A BigInt value to be converted.
• b - A String value indicating the format for displaying the value in argument a.

ToText(456, "###")

• a - A BigInt 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.

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

• a - A BigInt 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.

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

• a - A BigInt 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.

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

• a - A BigInt value to be converted.
• c - The number of decimal places to be converted.

ToText(123, 2)

• a - A BigInt 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.

ToText(1234, 2, ",")

• a - A BigInt 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.

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

• a - A Double value to be converted.

ToText(123.456)

• 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.

ToText(1236.456, "###0.00")

• 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.

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

• 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.

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

• 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.

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

• a - A Double value to be converted.
• c - The number of decimal places to be converted.

ToText(123.456, 2)

• 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.

ToText(1234.567, 1, ",")

• 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.

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

• a - A Time value.

ToText(ToTime(12,40,30))

• a - A Time value to be converted.
• b - A String that defines how the value in a to be formatted.

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

• 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.

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

• 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.

• 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")

• a - A Currency value to be converted into words.

ToWords($3.15)

• a - A Currency value to be converted into words.
• b - A BigInt value indicating the number of decimal places to be converted. This argument is optional.

ToWords($3.45, 1)

ToWords(20)

• a - A BigInt value to be converted into words.
• b - A BigInt value indicating the number of decimal places to be converted. This argument is optional.

ToWords(3, 1)

• a - A fractional number to be converted into words.

ToWords(10.12)

• a - A Number value to be converted into words.
• b - A BigInt value indicating the number of decimal places to be converted. This argument is optional.

ToWords(3.14, 1)

• a - A String to which a corresponding data mapping file can be bound.

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

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

• a - A String value.

Trim("he is a boy")

• a - A String value to be trimmed.

TrimLeft("he is a boy")

• a - A String value to be trimmed.

TrimRight("he is a boy")

• a - A String value.

UpperCase("he is a boy")

Val("63550513Mr.Tom")

• 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.

• 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()

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.

• eqv(true, true) - Returns true.
• eqv(true, false) - Returns false.

• String - A String indicating the information in the container.

GetInfo("TestKey") - Returns TestValue.

getLanguage()

getLocale()

• String - A String indicating the information in the container.

GetOrgInfo("TestKey") - Returns TestValue.

• String - A String indicating the information in the container.

GetUserInfo("TestKey") - Returns TestValue.

isAll(@PEndDate)

• If getLanguage() return zh, isCountry("CN") will return true.
• If getLanguage() return en, isCountry("CN") will return 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.

@"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.

• a - A specified value especially a value of a DBField.

• 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.

• inString - A String type value.

• isNumeric("true") - Returns false.
• isNumeric("false") - Returns false.
• isNumeric("1234") - Returns true.
• isNumeric("122 322323") - Returns false.

• 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()

• a - A DBField value.

Next(@"Ship Date")

• a - A DBField value.
• b - A BigInt value.

Next(@"Ship Date", 1)

nextMember()

• 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.

nextMember(3)

• 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.

nextMember(2,30)

• a - A String value which indicates the full path of the image file.

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

• a - A String value which indicates the URL of the image file.

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

• a - A String value which indicates the full path of the text file.

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

• a - A String value which indicates the URL of the text file.

openTxtURL("http://www.jinfonet.com/JReport/report.ini")

• a - A DBField value.

Prev(@"Ship Date")

• a - A DBField value.
• b - A BigInt value.

Prev(@"Ship Date", 4)

prevMember()

• 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.

prevMember(3)

• 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.

prevMember(2,50)

• a - A String indicating the information in the container.
• b - A String value.

PutInfo("TestKey", "TestValue")

PutOrgInfo("TestKey", "TestValue")

• a - A String indicating the information in the container.
• b - A String value.

PutUserInfo("TestKey", "TestValue")

• String - A String indicating the information in the container.

RemoveInfo("TestKey")

• String - A String indicating the information in the container.

RemoveOrgInfo("TestKey")

• String - A String indicating the information in the container.

RemoveUserInfo("TestKey")

reportName()

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.

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.

• toNumber(True) - Returns 1.
• toNumber(False) - Returns 0.

• 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.

• xor(true, true) - Returns false.
• xor(true, false) - Returns true.
• xor((@"Customer ID"<=20),(Remainder(@"Customer ID", 2) == 0));