Previous Topic
Appendix 1: Formula Functions
Logi Report provides different types of the built-in functions to help you write your formulas. This topic describes the usage of each function with examples.
Select the following links to view the formula functions of different types:
- Array Functions
- Date Range Functions
- Date/Time Functions
- Financial Functions
- Math Functions
- String Functions
- Other Functions
Array Functions
You can use the Array functions to summarize field data in several different ways.
When using the Array functions, if you assign a field_variable to a variable, for example "X", the variable "X" loses the characteristic of representing a group of values. The following formula are then incorrect.
Number x = @dbfield;
Number y = Average(x); // system will prompt you that there is no such kind of function.Number x = @dbfield;
Number y = Variance(x); // system will prompt you that there is no such kind of function.
Back to topDate Range Functions
You cannot use any of the Date Range functions to return a value singly.
| Function | Description | Example |
|---|---|---|
| Aged0To30Days() | This function returns a range of Date values that is within the previous 30 days from today. For example, if today is 10/14/99, the return range is from 09/15/99 to 10/14/99. | The return value of the following statement is "Not Expired".
|
| Aged31To60Days() | This function returns a range of Date values that is within the previous 31 to 60 days from today date. For example, if today is 10/13/99, the return range is from 08/12/99 to 09/13/99. | If today is 10/13/99, the return value of the following statement is "false".
|
| Aged61To90Days() | This function returns a range of Date values that is within the previous 61 to 90 days from today date. For example, if today is 10/13/99, the return range is from 07/13/99 to 08/11/99. | If today is 10/13/99, the return value of the following statement is "false".
|
| AllDatesFromToday() | This function returns a range of Date values that include any date from the present day to a date about 10000 years in the future Date value. For example, if today is 04/15/98, the return range is from 04/15/98 to the future. | If today is 10/10/99, the return value of the following statement is "false".
|
| AllDatesFromTomorrow() | This function returns a range of Date values that include any date from tomorrow to any future Date value. For example, if today is 04/15/98, the return range is from 04/16/98 to the future. | If today is 10/10/99, the return of the following statement is "false".
|
| AllDatesToToday() | This function returns a range of Date values that include all dates up through the present day. For example, if today is 4/15/98, the return range is from the past to 04/15/1998. | If today is 10/10/99, the return value of the following statement is "true".
|
| AllDatesToYesterday() | This function returns a range of Date values that include all dates up through the previous day. For example, if today is 4/15/98, the return range is from the past to 04/14/1998. | If today is 10/10/99, the return value of the following statement is "true".
|
| Calendar1stHalf() | This function returns a range of Date values that include all dates from January 1st through June 30th. | If today is 10/10/99, the return value of the following statement is "false".
|
| Calendar1stQtr() | This function returns a range of Date values that include all dates from January 1st through to March 31st. | If today is 10/10/99, the return value of the following statement is "false".
|
| Calendar2ndHalf() | This function returns a range of Date values that include all dates from July 1st through to December 31st. | If today is 10/10/99, the return value of the following statement is "true".
|
| Calendar2ndQtr() | This function returns a range of Date values that include all dates from April 1st through to June 30th. | If today is 10/10/99, the return value of the following statement is "false".
|
| Calendar3rdQtr() | This function returns a range of Date values that include all dates from July 1st through to September 30th. | If today is 10/10/99, the return value of the following statement is "false".
|
| Calendar4thQtr() | This function returns a range of Date values that include all dates from October 1st through December 31st. | If today is 10/10/99, the return value of the following statement is "true".
|
| Last4WeeksToSun() | This function returns a range of Date values that include the four weeks previous to last Sunday. For example, if today is 4/15/95, the return range is from 3/13/95 to 4/9/95. | If today is 10/10/99, the return value of the following statement is "false".
|
| Last7Days() | This function returns a range of Date values that include all dates from seven days ago to today (including today). For example, if today is 04/15/98, the return range is from (include) 04/09/98 to 04/15/98. | If today is 10/10/99, the return value of the following statement is "false".
|
| LastFullMonth() | This function returns a range of Date values that include all dates from the first to the last day of the previous month. For example, if today is 04/15/98, the return range is from 03/01/98 to 03/31/98. | If today is 10/10/99, the return value of the following statement is "false".
|
| LastFullWeek() | This function returns a range of Date values that include all dates from the Sunday to the Saturday of the previous week. For example, if today is 04/10/95, the return range is from 04/02/95 to 04/08/95. | If today is 10/10/99, the return value of the following statement is "false".
|
| LastYearMTD() | This function returns a range of Date values that fall on any date in the current month last year, up to the current date last year. For example, if today is 04/15/98, the return range is from 04/01/97 to 04/15/97. | If today is 10/10/99, the return value of the following statement is "false".
|
| LastYearYTD() | This function returns a range of Date values that include all dates in the last year, up to the current date last year. For example, if today is 04/15/98, the return range is from 01/01/97 to 04/15/97. | If today is 10/10/99, the return value of the following statement is "true".
|
| MonthToDate() | This function returns a range of Date values that include all dates from the first day of the month to today. For example, if today is 04/15/98, the return range is from 04/01/98 to 04/15/98. | If today is 10/10/99, the return value of the following statement is "true".
|
| Next30Days() | This function returns a range of Date values that include all days in the next 30 days starting from today (including today). For example, if today is 04/15/98, the return range is from 04/15/98 to 05/15/98. | If today is 10/10/99, the return value of the following statement is "false".
|
| Next31To60Days() | This function returns a range of Date values that include all days in the next 31 to 60 days starting from today. For example, if today is 04/15/98, the return range is from 05/16/98 to 06/14/98. | If today is 10/10/99, the return value of the following statement is "false".
|
| Next61To90Days() | This function returns a range of Date values that include all days in the next 61 to 90 days starting from today. For example, if today is 04/15/98, the return range is from 06/15/98 to 07/14/98. | If today is 10/10/99, the return value of the following statement is "false".
|
| Next91To365Days() | This function Returns a range of Date values that include all days in the next 91 to 365 days starting from today. For example, if today is 04/15/98, the return range is from 07/15/98 to 04/15/99. | If today is 10/10/99, the return value of the following statement is "false".
|
| Over90Days() | This function returns a range of Date values that include all days that are more than 90 days older than the current date. For example, if today is 04/15/98, the return range is from the past to 01/14/98. | If today is 10/10/99, the return value of the following statement is "true".
|
| WeekToDateFromSun() | This function returns a range of Date values that include all days from last Sunday to today (including today). For example, if today is 04/13/95, the return range is from 04/09/95 to 04/13/95. | If today is 10/10/99, the return value of the following statement is "false".
|
| YearToDate() | This function returns a range of Date values that include all days from the first day of the calendar year to today (including today). For example, if today is 04/15/98, the return range is from 01/01/98 to 04/15/98. | If today is 10/10/99, the return value of the following statement is "true".
|
Date/Time Functions
Date/Time functions enable you to convert numbers to dates and dates to numbers.
| Function | Description | Parameter | Return Value | Example | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| CurrentDate() | This function returns the current date on a report. | A Date value. | If today is Oct. 6,1999, the return value of the following statement is "10/06/99".
|
||||||||||||||||
| CurrentDateTime() | This function returns the current date and time on a report. | A DateTime value. | If today is Oct.6,1999, the current time is 5:10:27 p.m., the return value of the following statement is "1999-10-06 05:10:27 PM".
|
||||||||||||||||
| CurrentTime() | This function returns the current time on a report. | A Time value. | If the current time is 5:10:27 p.m., the return value of the following statement is "05:10:27 PM".
|
||||||||||||||||
| DateAdd(intervalType, nIntervals, startDateTime) |
This function returns a DateTime value to which a specified number of time intervals have been added. You can use this function to add interval of time to a DateTime. Its main feature is that the DateTime returned is always 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. |
Notes:
|
A DateTime value. |
|
|||||||||||||||
| DateDiff(intervalType, startDateTime, endDateTime, firstDayOfWeek)
Overloads:
|
This function returns a number of time intervals between two specified dates. |
|
A Number value. |
The following examples 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.
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. The following examples use DateDiff with the "Q" parameter to find the number of quarters (3 month periods) difference between two dates.
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.
The following examples 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 it is on a Tuesday.
|
|||||||||||||||
| DatePart(intervalType, inputDateTime, firstDayOfWeek, firstWeekOfYear)
Overloads:
|
This function returns a number that specifies a given part of a given date. |
|
A Number value. |
|
|||||||||||||||
| DateTimeTo2000(Datetime, Number) |
|
|
A Date field with a four digit year. |
These are examples of dates that are not affected by this function:
Some databases maintain the year of a Date as a two-digit field. These samples simulate that Date field:
|
|||||||||||||||
| DateTimeToDate(Datetime a) | This function evaluates the specified DateTime value and returns only the date. |
|
A Date value. | If the current date is Oct. 17, 1999 and the current time is 12:27:15, the return value of the following statement is "10/17/99".
|
|||||||||||||||
| DateTimeToSeconds(Datetime a) | This function evaluates the specified value and converts the time to the number of seconds from 00:00:00 to the specified time. |
|
An Integer value. | If the current date is Oct. 17, 1999 and the current time is 12:27:15, the return value of the following statement is "44835".
|
|||||||||||||||
| DateTimeToTime(Datetime a) | This function evaluates the specified DateTime value and returns only the time. |
|
A Time value. | If the current date is Oct. 17, 1999 and the current time is 12:27:15, the return value of the following statement is "12:27:15".
|
|||||||||||||||
| DateTo2000(date, number) |
|
|
A Date field with a four digit year. |
Some databases maintain the year of a date as a two-digit field. The following simulates this type of date field:
|
|||||||||||||||
| Day() | This function returns the day of the month. | An Integer value. | If today is Oct. 17, 1999, the return value of the following statement is "17".
|
||||||||||||||||
| Day(Date a) | This function extracts the day portion of a specified Date value. |
|
An Integer value. | If the current date is Oct. 17, 1999, the return value of the following statement is "17".
|
|||||||||||||||
| Day(Datetime a) | This function extracts the day portion of a specified Date value. |
|
An Integer value. | If the current date is Oct. 17, 1999 and the current time is 12:27:15, the return value of the following statement is "17".
|
|||||||||||||||
| DayOfWeek() | This function returns the day of the week. | An Integer value. | If today is Thursday, the return value of the following statement is "5".
|
||||||||||||||||
| DayOfWeek(Date a) | This function extracts the day portion of a specified Date value, and indicates which day of the week. |
|
An Integer value. | If today is Oct. 17, 1999 and today is Sunday, the return value of the following statement is "1".
|
|||||||||||||||
| DayOfWeek(Datetime a) | This function extracts the day portion of a specified Date value, and indicates which day of the week. |
|
An Integer value. | If the current date is Oct. 17, 1999, current time is 12:27:15 and today is Sunday, the return value of the following statement is "1" (Sunday is the first day of the week).
|
|||||||||||||||
| DayOfYear() | This function returns the day of the year. | An Integer value. | If today is Oct. 17, 1999, the return value of the following statement is "290".
|
||||||||||||||||
| DTSTo2000(DateString, Number) |
|
|
A DateTime string with a four digit year. |
These are examples of a date that are not affected by the function:
Some databases maintain the year of a date as a two-digit field and so these samples simulate that date field:
|
|||||||||||||||
| DTSToSeconds(string) | This function evaluates the specified string, and converts the Time value to the number of seconds from 00:00:00 (12:00 midnight) to the specified time. |
|
A Whole number value. | DTSToSeconds("2004/06/11 13:42:15") - Returns "49335". |
|||||||||||||||
| FirstDayOfMonth(Date a) | This function returns the first day of the month. |
|
A Date value. | The return value of the following statement is "10/01/2008".
|
|||||||||||||||
| FirstDayOfQuarter(Date a) | This function returns the first day of the quarter. |
|
A Date value. | The return value of the following statement is "10/01/2008".
|
|||||||||||||||
| FirstDayOfWeek(Date a) | This function returns the first day of the week. |
|
A Date value. | The return value of the following statement is "11/23/2008".
|
|||||||||||||||
| FirstDayOfYear(Date a) | This function returns the first day of the year. |
|
A Date value. |
The return value of the following statement is "01/01/2008".
|
|||||||||||||||
| FirstSundayOfMonth(Date a) | This function returns the first Sunday of the month. |
|
A Date value. | The return value of the following statement is "11/02/2008".
|
|||||||||||||||
| FirstSundayOfYear(Date a) | This function returns the first Sunday of the year. |
|
A Date value. | The return value of the following statement is "01/06/2008".
|
|||||||||||||||
| ForEachDay(Datetime a) | This function returns the Date portion of a specified DateTime value. |
|
A Date value. | If the DateTime is Dec.25,1999 20:12:50, the return value of the following statement is "12/25/99".
|
|||||||||||||||
| ForEachHalfMonth(DateTime a) | If the day in the DateTime is less than 15, this function returns the first day of the month. If the day is more than 15, it returns the 16th day of the month. |
|
A Date value. |
If the datetime is Sep.13,1999 12:27:15, the return value of the following statement is "09/01/99".
If the datetime is Oct.18,1999 5:21:22, the return value of the following statement is "10/16/99".
|
|||||||||||||||
| ForEachHalfYear(DateTime a) | If the month of a specified DateTime is before July, this function returns Jan.1 of the year; otherwise, it returns July 1 of the year. |
|
A Date value. |
If the datetime is May. 28,1999 10:10:25, the return value of the following statement is "01/01/99".
If the datetime is Nov. 15,1998 11:40:23, the return value of the following statement is "07/01/99".
|
|||||||||||||||
| ForEachMonth(DateTime a) | This function returns the first day of the month in a specified DateTime value. |
|
A Date value. | If the datetime is Sep.24,1999 12:05:56, the return value of the following statement is "09/01/99".
|
|||||||||||||||
| ForEachQuarter(DateTime a) | If the month of a specified DateTime is before April, the function returns Jan. 1 of the year. If the month is after April but before July, the function returns April. 1 of the year. If the month is after July but before October, the function returns July. 1 of the year. If the month is after October, the function returns Oct.1 of the year. |
|
A Date value. |
If the datetime is Feb.14,1997 2:10:23, the return value of the following statement is "01/01/97".
If the datetime is May. 28,1999 10:10:25, the return value of the following statement is "04/01/99".
If the datetime is Aug. 28,1999 10:10:25, the return value of the following statement is "07/01/99".
If the datetime is Nov. 11,1998 11:40:23, the return value of the following statement is "10/01/98".
|
|||||||||||||||
| ForEachWeek(DateTime a) | This function returns the Date of the first day of the week. |
|
A Date value. | If the DateTime is Oct. 15,1999 5:15:20 and it is Friday, the return value of the following statement is "10/10/99".
|
|||||||||||||||
| ForEachYear(DateTime a) | This function returns Jan.1 of the year. |
|
A Date value. | If the datetime is Aug. 28,1999 10:10:25, the return value of the following statement is "01/01/99".
|
|||||||||||||||
| Hour() | This function extracts the hour portion of the current time. | An integer value. | If the current time is 8:15:30, the return value of the following statement is "8".
|
||||||||||||||||
| Hour(DateTime a) | This function extracts the hour portion of a specified DateTime value. |
|
An integer value. | If the datetime is Oct. 15,1999 9:35:22, the return value of the following statement is "9".
|
|||||||||||||||
| Hour(Time a) | This function extracts the hour portion of a specified Time value. |
|
An integer value. | If the time is 10:05:26, the return value of the following statement is "10".
|
|||||||||||||||
| IsDate(number or string) | This function returns "true" if it can convert the given String or Number value to a valid Date and returns "false" otherwise. A valid Date is any Date between 100-1-1 and 9999-12-31. |
|
A Boolean value. |
|
|||||||||||||||
| IsDateTime(number or string) | This function returns "true" if it can convert the given Number or String value to a valid DateTime, and returns "false" otherwise. |
|
A Boolean value. |
|
|||||||||||||||
| IsTime(number or string) | This function returns "true" if it can convert the given Number or String value to a valid Time, and returns "false" if otherwise. |
|
A Boolean value. |
|
|||||||||||||||
| Minute() | This function returns the minute portion of the current time. | An Integer value. | If the current time is 12:41:27, the return value of the following statement is "41".
|
||||||||||||||||
| Minute(DateTime a) | This function returns the minute portion of a specified DateTime. |
|
An Integer value. | If the datetime is Sep. 21,1997 12:20:25, the return value of the following statement is "20".
|
|||||||||||||||
| Minute(Time a) | This function returns the minute portion of a specified time. |
|
An Integer value. | If the time is 8:30:27, the return value of the following statement is "30".
|
|||||||||||||||
| Month() | This function adding one returns the current month of the year. | An Integer value, which ranges from 0 to 11. | If the current month is September, the return value of the following statement is "8".
|
||||||||||||||||
| Month(Date a) | This function extracts the month portion from a specified Date value. |
|
An Integer value. | If the date is June 15,1999, the return value of the following statement is "6".
|
|||||||||||||||
| Month(DateTime a) | This function extracts the month portion from a specified DateTime value. |
|
An Integer value. | If the date time is July 15, 1999 10:10:10, the return value of the following statement is "7".
|
|||||||||||||||
| MonthName(month, abbreviate) | This function returns a String name for the specified month. |
|
A String value. |
|
|||||||||||||||
| Now() | This function prints the current time on a report. | A Time value. | If the current time is 1:24:35 in the afternoon, the return value of the following statement is "1:24:35 PM".
|
||||||||||||||||
| Quarter()
Overloads:
|
This function returns the quarter for the given date (1 for the first quarter). |
|
An integer value (1, 2, 3, 4). |
|
|||||||||||||||
| Second() | This function extracts the second portion of the current time. | An Integer value. | If the current time is 4:55:03, the return value of the following statement is "3".
|
||||||||||||||||
| Second(Datetime a) | This function extracts the second portion of a specified DateTime. |
|
An Integer value. | Suppose the datetime is Oct. 15,1999 10:15:17, the return value of the following statement is "17".
|
|||||||||||||||
| Second(Time a) | This function extracts the second portion of a specified time. |
|
An Integer value. | If the time is 7:51:59, the return value of the following statement is "59".
|
|||||||||||||||
| SelectedDate() |
This function returns the date selected in the calendar or returns today() when no calendar is available for selecting a date. The built-in expression Selected Date in Calendar calls this function. You can find the expression in the Template drop-down list in the Calendar dialog box. |
A Date value. | If the calendar date is March 12, 2012, the function returns "03/12/2012". | ||||||||||||||||
| Timer() | This function returns the number of seconds elapsed since midnight. | A Number value. | If the current time is 9:28:30am, the function returns "34110". | ||||||||||||||||
| ToDate(number or string) | This function uses IsDate(number or string) to check whether the number/string is right or not. If the number/string is right, the function returns a Date value between 100-1-1 and 9999-12-31. If the number/string is wrong, it returns null. |
|
A Date value or null. |
|
|||||||||||||||
| ToDate(Date a) | This function returns the specified date. |
|
A Date value. | If the date is July 15, 1999, the return value of the following statement is "07/15/99".
|
|||||||||||||||
| ToDate(Datetime a) | This function extracts the date portion of a specified DateTime. |
|
A Date value. | If the DateTime is July 15,1999 12:20:30, the return value of the following statement is "07/15/99".
|
|||||||||||||||
| ToDate(integer d) | This function converts the argument d (a millisecond value represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT) to a date value. |
|
A Date value. | The return value of the following statement is "01/02/70".
|
|||||||||||||||
| ToDate(integer y, Integer m, Integer d) | This function creates a Date value from arguments y, m and d. |
|
A Date value. | The return value of the following statement is "07/15/99".
|
|||||||||||||||
| ToDate(integer y, Integer m, Integer d, String timezone) | This function creates a Date value from arguments y, m, d, and timezone. |
|
A Date value. | The return value of the following statement is "9/27/2018".
|
|||||||||||||||
| ToDateTime(number or string) | This function use IsDateTime(number or string) to check whether the number/string is right or not. If it is right, the function returns a DateTime value between 100-1-1 and 9999-12-31; otherwise, the function returns null. |
|
A DateTime value or null. |
|
|||||||||||||||
| ToDateTime(date a) |
|
A DateTime value. | ToDateTime(ToDate("2004-2-23")) - Returns "2004-02-23 00:00:00". |
||||||||||||||||
| ToDateTime(Date a, Time b) | This function creates a DateTime value from arguments a and b. |
|
A DateTime value. | Supposing the date is July 15,1999 and the time is 12:20:30, the return value of the following statement is "1999-07-15 12:20:30".
|
|||||||||||||||
| ToDateTime(Integer d) | This function converts the argument d (a millisecond value represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT) to a DateTime value. The function calculating is based on the time zone. You must adjust argument d to reflect your desired time zone. |
|
A DateTime value. |
The time zone is UTC+08:00. The return value of the following statement is "11/9/2016 3:10:17 AM".
The time zone is UTC+08:00. The return value of the following statement is "1/2/1970 04:00:00 AM".
|
|||||||||||||||
| ToDateTime(Integer y, Integer m, Integer d) | This function creates a DateTime value from the arguments y, m, and d. |
|
A DateTime value. | If the date is July 15,1999, the return value of the following statement is "1999-07-15 12:00:00".
|
|||||||||||||||
| ToDateTime(Integer y, Integer m, Integer d, Integer h, Integer i, Integer s) | This function creates a DateTime value from the arguments y, m, d, h, i, and s. |
|
A DateTime value. | If the date is July 15,1999 and time is 8:05:30, the return value of the following statement is "1999-07-15 08:05:30".
|
|||||||||||||||
| ToDateTime(Integer y, Integer m, Integer d, Integer h, Integer i, Integer s, String timezone) | This function creates a DateTime value while converting the time zone from the arguments y, m, d, h, i, s, and timezone. |
|
A DateTime value. | The return value of the following statement is "10/19/2018 6:23:54 PM".
|
|||||||||||||||
| Today() | This function returns the current date in a report. | A Date value. | If the current date is Oct. 11, 1999, the return value of the following statement is "10/11/99".
|
||||||||||||||||
| ToTime(number or string) | This function uses IsTime(number or string) to check whether the number/string is right or not. If the number/string is right, the function returns a Time value between 00:00:00 and 23:59:59; otherwise, it returns null. |
|
A Time value or null. |
|
|||||||||||||||
| ToTime(DateTime a) | This function extracts the time portion from a specified DateTime value. |
|
A Time value. | Supposing the date is July 15,1999 and time is 10:55:45, the return value of the following statement is "10:55:45".
|
|||||||||||||||
| ToTime(Integer t) | This function converts the argument t (a millisecond value represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT) to a Time value. |
|
A Time value. | The return value of the following statement is "08:00:01".
|
|||||||||||||||
| ToTime(Integer a, Integer b, Integer c) | This function creates a Time value from the arguments a, b, and c. |
|
A Time value. | The return value of the following statement is "10:10:10".
|
|||||||||||||||
| ToTime(Integer a, Integer b, Integer c, String timezone) | This function creates a Time value from the arguments a, b, c, and timezone. |
|
A Time value. | The return value of the following statement is "4:20:15 PM".
|
|||||||||||||||
| WeekdayName(weekday, abbreviate, firstDayOfWeek)
Overloads:
|
This function returns a string indicating the name of the specified day of the week. |
|
A String value. |
|
|||||||||||||||
| WeekFrom1970() | This function computes the total number of weeks from 1970 to the current date. | An Integer value. | If the current date is Oct. 13,1999, the return value of the following statement is "1554".
|
||||||||||||||||
| WeekFrom1970(Date, a) | This function computes the total number of weeks from 1970 to a specified date. |
|
An Integer value. | Supposing the date is July 8,1995, the return value of the following statement is "1331".
|
|||||||||||||||
| WeekFrom1970(DateTime, a) | This function computes the total number of weeks from 1970 to a specified DateTime. |
|
An Integer value. | Supposing the DateTime is Jan. 2,1988 10:40:50, the return value of the following statement is "940".
|
|||||||||||||||
| WeekOfMonth() | This function returns the week of the current month. | An Integer value. | If the current date is Oct. 13,1999, the return value of the following statement is "3".
|
||||||||||||||||
| WeekOfMonth(Date a) | This function returns the week of the month of a specified date. |
|
An Integer value. | If the current date is Oct. 13,1999, the return value of the following statement is "3".
|
|||||||||||||||
| WeekOfMonth(DateTime a) | This function evaluates the week of the month of a specified DateTime. |
|
An Integer value. | Supposing the date time is Jan. 20,1999 10:40:50, the return value of the following statement is "4".
|
|||||||||||||||
| WeekOfYear() | This function returns the week of the current year. | An Integer value. | If the current date is Oct. 13,1999, the return value of the following statement is "42".
|
||||||||||||||||
| WeekOfYear(Date a) | This function returns the week of the year in a specified date. |
|
An Integer value. | Supposing the date is July 8,1999, the return value of the following statement is "28".
|
|||||||||||||||
| WeekOfYear(DateTime a) | This function returns the week of the year in a specified DateTime. |
|
An Integer value. | Supposing the date time is Feb. 4,1999 10:40:50, tThe return value of the following statement is "6".
|
|||||||||||||||
| Year() | This function returns the current year. | An Integer value. | If the current year is 1999, the return value of the following statement is "1999".
|
||||||||||||||||
| Year(Date a) | This function extracts the year portion of a specified date. |
|
An Integer value. | Supposing the date is Jan.15,1995, the return value of the following statement is "1995".
|
|||||||||||||||
| Year(DateTime a) | This function extracts the year portion of a specified DateTime value. |
|
An Integer value. | Supposing the date time is July 15,1988 10:40:50, the return value of the following statement is "1988".
|
Financial Functions
| Function | Description | Parameter | Return Value | Example |
|---|---|---|---|---|
| DDB(cost, salvage, life, period, factor)
Overloads:
|
This function returns a number specifying the depreciation of an asset for a specific time period, using the double-declining balance method or another method as specified by the factor argument. |
|
A Number value. | Supposing a company purchases a fleet of cars for $560,000, the cars have a lifetime of 12 years and a salvage value of $30,000, you can depreciate them using the double-declining method.
|
| FRAccRecTurnover(Number accountReceivable, Number sales, DbDouble numOfDays) | This function computes the turnover of the account receivable. |
|
A Number value. |
If the account receivable is 220000.00, the sales value is 450000.00, the number of days is 100, the return value of the following statement is "48.89":
|
| FRCashFlowVsTotalDebt(Number cashFlow,Number totalDebt) | This function returns the result of cash flow vs total debt. |
|
A Number value. | If the cash flow is 250000.00, the total debt is 280000.00, the return value of the following statement is "0.89".
|
| FRCurrentRatio(Number curAssets, Number curLiabilities) | This function computes the current ratio. |
|
A Number value. | If the current assets are 1800000.00, the current liabilities are 150000.00, the return value of the following statement is "12.00".
|
| FRDebtEquityRatio(Number totalLiabilities, Number totalEquity) | This function computes the debt equity ratio. |
|
A Number value. | If the total liabilities are 175000.00, the total equity is 215000.00, the return value of the following statement is "0.81".
|
| FRDividendYield(Number dividEnd, Number marketPrice) | This function computes the dividend yield. |
|
A Number value. | If the dividend is 9.85, the market price is 10.87, the return value of the following statement is "0.91".
|
| FREarningsPerCommonShare(Number netProfit, Number preferredDividend, DbDouble numOfCommonShare) | This function computes the earnings of per common share. |
|
A Number value. | If the net profit is 300000.00, the preferred dividend is 180000.00, the number of common shares is 10000, the return value of the following statement is "12".
|
| FREquityVsTotalAssets(Number totalEquity, Number totalAssets) | This function returns the result of equity vs. total assets. |
|
A Number value. | If the total equity is 215000.00, the total assets are 2200000.00, the return value of the following statement is "0.10".
|
| FRGrossProfitMargin(Number grossProfit, Number sales) | This function is used to compute the gross profit margin. |
|
Number value. | If the gross profit is 350000.00, the sales value is 450000.00, the return value of the following statement is "0.78".
|
| FRInterestCoverage(number cashFlow, number interestExpenses) | This function computes the interest coverage. |
|
A Number value. | If the cash flow is 250000.00, the interest expenses are 350000.00, the return value of the following statement is "0.71".
|
| FRInventoryTurnover(Number inventory, Number sales, Number numOfDays) | This function computes the turnover of the inventory. |
|
A Number value. | If the inventory is 165000.00, the sales value is 450000.00, the number of days is 100, the return value of the following statement is "36.67".
|
| FRNetProfitMargin(Number netProfit, Number sales) | This function is used to compute the net profit margin. |
|
A Number value. | If the net profit is 300000.00, the sales value is 450000.00, the return value of the following statement is "0.67".
|
| FROperatingProfitMargin(Number operatingProfit, Number sales) | This function computes the operating profit margin. |
|
A Number value. | If the operating profit is 380000.00, the sales value is 450000.00, the return value of the following statement is "0.84".
|
| FRPriceEarningsRatio(Number marketPrice, Number earningsPerShare) | This function computes the price earning ratio. |
|
A Number value. | If the market price is 10.87, the earning per share is 12, the return value of the following statement is "0.91".
|
| FRQuickRatio(Number curAssets, Number inventories, Number curLiabilities) | This function computes the quick ratio. |
|
A Number value. | If the current assets are 1800000.00, the inventory is 165000.00, the current liabilities are 150000.00, the return value of the following statement is "10.90".
|
| FRReturnOnCommonEquity(Number netProfit, Number preferredDividend, Number commonEquity) | This function returns the result of return on common equity. |
|
A Number value. | If the net profit is 300000.00, the preferred dividend is 180000.00, the common equity is 208000.00, the return value of the following statement is "0.58".
|
| FRReturnOnEquity(Number netProfit, Number totalEquity) | This function returns the results of return on equity. |
|
A Number value. | If the net profit is 300000.00, the total equity is 215000.00, the return value of the following statement is "1.40".
|
| FRReturnOnInvestedCapital(Number netProfit, Number totalBankDebts, Number totalEquity) | This function returns the result of return on invested capital. |
|
A Number value. | If the net profit is 300000.00, the total bank debts are 100000.00, the total equity is 215000.00, the return value of the following statement is "0.95".
|
| FRReturnOnNetFixedAssets(Number netProfit, Number netFixedAssets) | This function returns the result of return on net fixed assets. |
|
A Number value. | If the net profit is 300000.00, the net fixed assets are 400000.00, the return value of the following statement is "0.75".
|
| FRReturnOnTotalAssets(Number netProfit, Number totalAssets) | This function returns the results of return on total assets. |
|
A Number value. | If the net profit is 300000.00, the total assets are 2200000.00, the return value of the following statement is "0.14".
|
| FV(rate, periods, payment, presentMoney, type)
Overloads:
|
This function returns a number specifying the future value of an annuity based on periodic, fixed payments and a fixed interest rate. |
|
A Number value. |
Suppose that you put $1100 per month into a retirement savings plan that pays 6 percent annual interest, compounded monthly. How much will the account be worth after 20 years? The following 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:
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:
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:
|
| IPmt(rate, period, periods, presentMoney, futureMoney, type)
Overloads:
|
This function returns a number specifying the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate. |
|
A Number value. |
Suppose that you want to take out a $250,000 loan payable monthly over 15 years at an annual interest rate of 6.5 percent. The following formula returns the amount of interest that you pay in your first loan payment. Note that the monthly interest rate is 0.065 / 12 and the number of months of the loan is 15 * 12.
The following formula returns the amount of interest that you pay in your 97th payment (after 10 years of payments):
|
| IRR(values, guess)
Overloads:
|
This function returns a number specifying the internal rate of return for a series of periodic cash flows (payments and receipts). |
|
A Number value. | Suppose that you can choose one of two offers: $20,000 now or guaranteed payments of $5,000 after one year, $10,000 after two years and $15,000 after three years. Which is the better offer? One way to quantify this is to calculate the internal rate of return. If you take the second offer, you cannot take the first, which is like experiencing an initial payment of $20,000 followed by the receipts:
Returns "0.201" (rounded to 3 decimals) or "20.1 percent" interest. With all other things being equal, if you think that 20.1 percent is a good rate of return, you would prefer the second offer. |
| MIRR(valueArray, financeRate, reinvestRate) | This function returns a number specifying the modified internal rate of return for a series of periodic cash flows (payments and receipts). |
|
A Number value. | Suppose that you run a business that makes equipment investments, which results in a loss in the first and fourth years. Your expected annual returns are: -$60,000, $60,000, $45,000, -$50,000, $65,000, $40,000. Your losses are financed at 10 percent while you reinvest your earnings in an account at 6 percent. The modified internal rate of return is:
|
| NPer(rate, payment, presentMoney, futureMoney, type)
Overloads:
|
This function returns a number specifying the number of periods for an annuity based on periodic, fixed payments and a fixed interest rate. |
|
A Number value. |
Suppose that you want a $250,000 loan to buy a house. The interest rate is 6.5 percent and you can afford to pay $3,000 per month. How long a mortgage would you need?
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?
|
| NPV(rate, values) | This function returns a number specifying the net present value of an investment based on a series of periodic cash flows (payments and receipts) and a discount rate. |
|
A Number value. | Suppose that someone offers to pay you $15000 after one year, $20000 after two years, $25000 after three years, and $12000 after four years. If the discount rate (the Time value of money) is 6.5 percent, the value of this offer to you today is:
The formula returns "61741.8". So this scheme is worth $61,741.8 to you today. This is less than the sum of the payments, which is $72000, since you have to wait for this money. |
| Pmt(rate, periods, presentMoney, futureMoney, type)
Overloads:
|
This function returns a number specifying the payment for an annuity based on periodic, fixed payments, and a fixed interest rate. To find the total amount paid out over the whole loan, multiply the payment per period (the value returned by Pmt) by the total number of periods. |
|
A Number value. |
Suppose that you want to take out a $250,000 loan payable monthly over 15 years at an annual interest rate of 6.5 percent. The following formula returns your monthly loan payment. Note that the monthly interest rate is 0.065 / 12, and the number of months of the loan is 15 * 12.
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:
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:
|
| PPmt(rate, period, periods, presentMoney, futureMoney, type)
Overloads:
|
|
A Number value. |
|
|
| PV(rate, periods, payment, futureMoney, type)
Overloads:
|
This function returns a number specifying the present value of an annuity based on periodic, fixed payments to be paid in the future and at a fixed interest rate. |
|
A Number value. | Suppose that you want to buy a condo and can make payments of $1100 twice a month (24 annual payments). If the mortgage rates are 6.5 percent, and you want to pay off the condo in 10 years, what is the maximum loan that you can take out?
You can therefore afford a loan of about $194,000. Notice that the payment argument is negative since you are paying out the money each month. |
| Rate(periods, payment, presentMoney, futureMoney, type, guess)
Overloads:
|
This function returns a number specifying the interest rate per period for an annuity. The units of the return value are consistent with the units of periods. For example, if periods is in months, the rate returned is a monthly interest rate. |
|
A Number value. |
An electronics store offers to finance a $12500 television for $560 per month, over 2 years, with no money down. Is this a good deal? The first step in determining this is to figure out what interest rate the store is charging.
The next expression calculates the interest rate, expressed as a yearly interest rate and as a percent.
|
| SLN(cost, salvage, life) | This function returns a number specifying the straight-line depreciation of an asset for a single period. |
|
A Number value. | Suppose a company purchases a fleet of cars for $560,000. The cars have a lifetime of 12 years and a salvage value of $30,000. The depreciation per year is:
|
| SYD(cost, salvage, life, period) | This function returns a number specifying the sum-of-years' digits depreciation of an asset for a single period. |
|
A Number value. | Suppose a company purchases a fleet of cars for $560,000. The cars have a lifetime of 12 years and a salvage value of $30,000. They are depreciated as follows:
|
Math
You can use mathematical functions for a variety of mathematics-oriented calculations and operations.
| Function | Description | Parameter | Return Value | Example |
|---|---|---|---|---|
| Abs(Integer, a) | The function returns the absolute value of an Integer value. If the argument is not negative, it returns the argument; if the argument is negative, it returns the negation of the argument. |
|
An Integer value. | The return value of the following statements is "3534".
|
| Abs(Currency, a) | The function returns the absolute value of a Currency value. If the argument is not negative, it returns the argument; if the argument is negative, it returns the negation of the argument will. |
|
A Currency value. | The return value of the following statement is "$3.67".
|
| Abs(Number, a) | The function returns the absolute value of a Number value. If the argument is not negative, it returns the argumen; if the argument is negative, it returns the negation of the argument. |
|
A Number value. | The return value of the following statements is "2.4785".
|
| atan(Number) | The function returns a number specifying the arctangent of the given number parameter. In other words, it returns the angle whose tangent is the given number parameter. This function works in a similar way to the Java Math function with the same name. The range of values returned by atan is between -pi/2 and pi/2 radians. |
|
A Number value, which is an angle specified in number of radians. | atan(1) - Returns an angle of 0.7854 radians (rounded to 4 radians). To convert this angle to degrees, multiply by 180 / pi. For instance, atan (1) * 180 / pi is 45 degrees. |
| Average(DBField a) | The function gets the average value of the values referred to by the argument. |
|
A Number or Currency value. |
|
| Average(DBField a, String groupby) | This function gets the average value of values referred to by the argument. |
|
A Number or Currency value, which depends on the data type of the first argument respectively. |
|
| Average(Number a[]) | This function computes the average of all the values in the array. |
|
A Number or a Currency value. |
|
| cos(number) | The function returns a number specifying the cosine of an angle given in radians. It takes a right-angle triangle, and returns the length of the side adjacent to the specified angle divided by the length of the hypotenuse. |
|
A Number value between 1 and -1. |
|
| Count(Array a[ ]) | This function computes the number of the values in an array. |
|
An Integer value. |
|
| Count(DBField a) | The function counts the number of values referred to by the argument. |
|
A Number value. |
|
| Count(DBField a, String groupby) | The function is used to count the number of values referred to by the first argument. |
|
A Number value. |
|
| DistinctCount(DBField a) | This function computes the number of distinct values referred to by a DBField. |
|
An Integer value. | If you build a report about customer order, the return value of the following statement is "19".
|
| DistinctCount(DBField a, String groupby) | This function counts the number of distinct values referred to by the first argument. |
|
A Number value. |
|
| DistinctCount(Number a[ ]) | This function computes the number of distinct values in an array. |
|
An Integer value. | The return value of the following statement is "4".
|
| e() | The Mathematical value of e, which is 2.7182818 (if rounded to 7 decimal places). | |||
| exp(Number) | The function returns a number specifying "e" (the base of natural logarithms) raised to a power. The value of "e" is approximately 2.7182818284590452354. This function works like the Java Math function with the same name. Numeric overflow occurs if the given number parameter is larger than approximately 705. |
|
A number which is the value of e ^ Number. |
|
| integDiv(numbera, numberb) | The function returns the integer portion of the value numbera/numberb. |
|
A Whole Number value. | integDiv(33, 2) - Returns "16". |
| log(number) | The function returns a number specifying the natural logarithm of a given number. The natural logarithm is the logarithm to the base "e", where "e" is approximately 2.7182818284590452354. If the value of number is less than 0, the function returns "NaN". |
|
The number which is the value of ln(Number). |
|
| Maximum(a[ ]) | This function returns the highest value in an array. |
|
A Number or Integer value, which depends on the data type of the parameter "a". |
|
| Maximum(DBField a) | This function picks up the maximum value from the values referred to by the argument. |
|
The return value type is dependent on the data type of the parameter. It may be Number, Currency, String, Date, Time, DateTime, or Boolean. |
|
| Maximum(DBField a, String groupby) | This function picks up the maximum value from the values referred to by the first argument. |
|
The return value type is dependent on the data type of the first argument. It may be Number, Currency, String, Date, Time, DateTime, or Boolean. |
|
| Minimum(a[ ]) | This function returns the lowest value in an array. |
|
A Number or Integer value, which depends on the data type of the parameter "a". |
|
| Minimum(DBField a) | This function picks up the minimum value from the values referred to by the argument. |
|
The return value type is dependent on the data type of the parameter. It can be Number, Currency, String, Date, Time, DateTime, or Boolean. |
|
| Minimum(DBfield a, String groupby) | This function picks up the minimum value from the values referred to by the first argument. |
|
The return value type is dependent on the first argument. It may be Number, Currency, String, Date, Time, DateTime, or Boolean. |
|
| MRound()
Overloads:
|
This function rounds up (away from zero) if the remainder of the division is greater than or equal to half of the value of the second parameter.
Expression (take MRound(Currency,Number) for example):
|
|
A number value that is a multiple of the second parameter and at the same time is nearest to the first parameter. |
|
| pi() | The Mathematical value of pi, which is 3.1415926 (if rounded to 7 decimal places). | |||
PopulationStdDev()
|
This function computes the population standard deviation of the values referred to by the argument. |
|
A Number value. |
|
PopulationVariance()
|
This function computes the population variance of the values referred to by the argument. |
|
A Number value. |
|
| pow(numbera, numberb) | The function returns the value of numbera ^ numberb. |
|
|
|
| random(), random(seed)
Overloads:
|
The function returns a random number greater than or equal to 0 and less than 1. If seed is equal to 0, Random returns the random number that was returned from the previous call to random. If seed is not supplied or is greater than 0, Random returns the next random number in the internally generated sequence of random numbers. If seed is less than 0, Random uses this value of seed to start a new random number sequence and returns the first value in the sequence. This function is typically used when your formula requires a randomly generated number, for example, statistical calculations or selecting records at random to limit the data in a report. |
|
A Number value. |
|
| Remainder(Integer a, Integer b) | This function evaluates the remainder after numerator has been divided by a denominator. |
|
An Integer value. |
The following formula can distinguish odd numbers and even their backgrounds:
|
| Round(Number a) | This function returns the integer portion after a specified number has been rounded. |
|
An Integer value. | The return value of the following statement is "3.00".
|
| Round(Number a, Integer b) | This function rounds to a specified scale determined by argument b for Double value a. |
|
A Number value. | The return value of the following statement is "2.46".
|
| Round(Number a, Integer b, Integer c) | This function rounds to a specified scale determined by argument b and a specified rounding mode determined by argument c for Double value a. |
|
A Number value. | The return value of the following statement is "2.03".
|
| Round(Number a, Integer b, String c) | This function rounds to a specified scale determined by argument b using the rounding mode c. |
|
A Number value. |
The return value of the following statement is "123.457".
|
RunningAvg()
|
This function takes the average of all the numerical values in a field on running records. For more information about the arguments, return type, and examples, see Average(). | |||
RunningCount()
|
This function takes a total count on running records of the values in a field. For more information about the arguments, return type, and examples, see Count(). | |||
RunningDistinctCount()
|
This function takes a total count of all the distinct fields in a report on running records. For more information about the arguments, return type, and examples, see DistinctCount(). | |||
RunningMaximum()
|
This function finds the largest numerical value in a field on running records. For more information about the arguments, return type, and examples, see Maximum(). | |||
RunningMinimum()
|
This function finds the smallest numerical value in a field on running records. For more information about the arguments, return type, and examples, see Minimum(). | |||
RunningSum()
|
This function sums up all the numerical values in a field on running records. For more information about the arguments, return type, and examples, see Sum(). | |||
| sgn(data) | This function returns the sign value of the given number (whose value is data). If data is greater than 0, it returns "1"; if data is less than 0, it returns "-1"; if data is equal to 0, it returns "0". |
|
1, 0 or -1. |
|
| sin(number) | This function returns a number specifying the sine of an angle given in radians. It takes a right-angle triangle, and returns the length of the side opposite to the specified angle divided by the length of the hypotenuse. |
|
A Number value between -1 and 1. |
|
| sqr(number) | This function returns the square root of a given number. If the value of the number is less than "0", it returns "NaN". It is designed to work like the Java Math function of sqrt(). |
|
A Number value. |
The return value of the following statement is "10". |
StdDev()
|
This function computes the standard deviation of the values referred to by the argument. |
|
A Number value. |
|
Sum()
|
This function computes the sum of all the values referred to by the argument. |
|
The return value type is dependent on the parameter. It can be Number, Integer, or Currency. |
|
| tan(number) | This function returns a number specifying the tangent of an angle given in radians. It takes a right-angle triangle, and returns the length of the side opposite the specified angle divided by the length of the side adjacent to the angle. |
|
A Number value. |
|
| ToBinary(Integer a) | This function changes a specified integer to its binary form. |
|
A String value. | The return value of the following statement is "1011".
|
| ToHex(Integer a) | This function returns the HEX form of a specified value. |
|
A String value. | The return value of the following statement is "20".
|
| ToInt(number a) | This function changes a specified number to an Integer. |
|
An Integer value. | The return value of the following statement is "100".
|
| ToOctal(Integer a) | This function returns the octal form of a specified value. |
|
A String value. | The return value of the following statement is "64".
|
| Truncate(Number a) | This function returns a number by truncating the decimal portion. |
|
A Number value. | The return value of the following statement is "25.00".
|
| Truncate(Number a, Integer b) | This function returns a number by truncating the number at the decimal point. The number is truncates to the decimal place indicated by argument b, and the function returns a fractional number. |
|
A Number value. | The return value of the following statement is "25.430".
|
Variance()
|
This function computes the variance of all the values referred to by the argument. |
|
A Number value. |
|
When using the Math functions, if you assign a field to a variable, for example "x", the variable "x" loses the characteristic of representing a group of values. The following formulas are then incorrect.
Number x = @dbfield;
Number y = Average(x); // system will prompt you that there is no such kind of function.Number x = @dbfield;
Number y = PopulationStdDev(x); // system will prompt you that there is no such kind of function.
Rounding Mode
The parameter c in the function Round(Number a, integer b, integer c) is used to represent the following different seven rounding modes, which you can specify as an integer between 0 and 6.
- ROUND_UP = 0
Rounding mode to round away from zero. Always increments the digit prior to a non-zero discarded fraction. Note that this rounding mode never decreases the magnitude of the calculated value. - ROUND_DOWN = 1
Rounding mode to round towards zero. Never increments the digit prior to a discarded fraction (that is, truncates). Note that this rounding mode never increases the magnitude of the calculated value. - ROUND_CEILING = 2
Rounding mode to round towards positive infinity. If the value is positive, behaves as for ROUND_UP; if negative, behaves as for ROUND_DOWN. Note that this rounding mode never decreases the calculated value. - ROUND_FLOOR = 3
Rounding mode to round towards negative infinity. If the BigDecimal is positive, behave as for ROUND_DOWN; if negative, behave as for ROUND_UP. Note that this rounding mode never increases the calculated value. - ROUND_HALF_UP = 4
Rounding mode to round towards "nearest neighbor" unless both neighbors are equidistant, in which case round up. Behaves as for ROUND_UP if the discarded fraction is >= .5; otherwise, behaves as for ROUND_DOWN. Note that this is the rounding mode that most of us were taught in grade school. - ROUND_HALF_DOWN = 5
Rounding mode to round towards "nearest neighbor" unless both neighbors are equidistant, in which case round down. Behaves as for ROUND_UP if the discarded fraction is > .5; otherwise, behaves as for ROUND_DOWN. - ROUND_HALF_EVEN = 6
Rounding mode to round towards the "nearest neighbor" unless both neighbors are equidistant, in which case, round towards the even neighbor. Behaves as for ROUND_HALF_UP if the digit to the left of the discarded fraction is odd; behaves as for ROUND_HALF_DOWN if it's even. Note that this is the rounding mode that minimizes cumulative error when applied repeatedly over a sequence of calculations.
String Functions
| Function | Description | Parameter | Return Value | Example |
|---|---|---|---|---|
| Asc(Char a) | This function evaluates the ASCII value of the first character of the argument. |
|
An Integer value. | The return value of the following statement is "97".
|
| ByteToText(byte) |
This function returns the size of a message in the appropriate unit. If the parameter is less than 1024, the result is in bytes; if the parameter is between 1024 and 1048576, the result is in kilobytes; otherwise, the result is in megabytes. |
|
A String value. |
|
| Chr(Integer a) | This function returns a single character string associated with the ASCII value passed as the argument. |
|
A String value. |
|
| ExchGetId (address) | This function begins by determining whether the address is in X500 or X400 format. Once this has been ascertained, the function then determines the ID.
If the field is in X500 format, the function extracts the last instance of the "CN=" code (not case sensitive); if the field is in the X400 format, the function extracts the SMTP or MS Ids.
|
|
A String value. |
|
| ExchGetOrganization(address) | This function begins by determining whether the address is in X500 or X400 format. Once this has been ascertained, the function then determines the Organization Name.
If the field is in X500 format, the function extracts the last instance of the "/O=" code (not case sensitive); if the field is in the X400 format, the function extracts the instance of the "P=" code (not case sensitive).
|
|
A String value. |
|
| ExchGetPath(path) | This function returns the container information in an address field.
If the address type is X500, the function returns 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 returns NULL. If the address type is X400, the function returns all "OU*=" codes (residing between the "P=" and "O=" codes). If the address starts with "DDA:", the function returns all information after the "DDA:" code. If the field is blank, the function returns "UNKNOWN ADDRESS". |
|
A String value. |
|
| ExchGetSite(address) | The function begins by determining whether the address is in X500 or X400 format. Once this has been ascertained, the function then determines the Site Name.
If the field is in X500 format, the function extracts the last instance of the "/OU=" code (not case sensitive); if the field is in the X400 format, the function extracts the instance of the "O=" code (not case sensitive).
|
|
A String value. |
|
| ExtractString(String origin, String start, String end) | This function evaluates an origin string and returns the first occurrence of a String that starts with the Start String and ends with the End String (the End String is excluded). If the function cannot find the End String, it returns the string that starts with the Start String till its end. |
|
A String value. |
|
| filter(inString, searchString)
Overloads:
|
This function is designed to search the string in the specified strings. It searches an array of strings for a specified string, and returns the strings in an array. |
|
Array of String values. | Suppose that inString = [ "abc", "Abc", "abcdfg", "asdfabc", "sdfdfd"], searchString = "abc":
|
| InStr(String a, String b) | This function returns the position of the first occurrence of one string within another. If the function cannot find String a in String b, it returns "-1". |
|
An Integer value. |
The following formula can convert number format to dollars and cents pattern:
|
| InStr(Number a, String b, String c) | This function returns the position of the first occurrence of one string within another, starting at the position specified by argument a. If the function cannot find String c in String b, it returns "-1". |
|
An Integer value. |
|
| join(stringArray)
Overloads:
|
The function returns a string created by joining a number of substrings contained in an array. |
|
A String value. | Assume that the stringArray list in the examples consists of the following three elements, Welcome, Use, and Logi Report:
|
| LastIndexOf(String a, String b) | This function returns the position of the last occurrence of one string within another, if the function cannot find String b in String a, it returns "-1". |
|
An Integer value. |
|
| LastIndexOf(Integer a, String b, String c) | This function returns the position within this string of the first occurrence of the specified substring, searching backward starting at the position specified by argument a. If the function cannot find String c in String b, it returns "-1". |
|
An Integer value. | |
| Left(String a, Number b) | This function returns a substring that contains the specified number of characters from the left side of a string. |
|
A String value. | The return value of the following statement is "he is".
|
| Length(String a) | This function returns the number of characters in a String. |
|
An Integer value. | The return value of the following statement is "16".
|
| LooksLike(String a, String b) | This function enables you to locate field values using standard DOS wildcards ( "?" is a wildcard for a single character and "*" is a wildcard for any number of characters). It does this by comparing a String to a mask which contains one or more wildcards. The function returns "True" if the string matches the mask, and "False" if the string does not match the mask. |
|
A Boolean value. |
|
| LowerCase(String a) | This function returns a String that contains all lowercase letters converted from String a. |
|
A String value. | The return value of the following statement is this is "her book".
|
| Mid(String a, Integer b) | This function returns a number of characters from a specified position of a String. |
|
A String value. | The return value of the following statement is "my father".
|
| Mid(String a, Integer b, Integer c) | This function returns a specified number of characters from a specified position of a String. |
|
A String value. |
The following formula can convert a DBField string from a normal horizontal pattern to a vertical pattern:
|
| NumericText(String a) | This function is used to test if the content of the string is a Number. |
|
A Boolean value. |
|
| Picture(String a, String b) | This function prints a string or values in a string in a predetermined format. |
|
A String value. |
|
| ProperCase()
Overloads: ProperCase(String) |
The function capitalizes the first letter in a text string and any other letter that follows a character other than a letter, and converts all other letters to lowercase letters. |
|
A String value. |
|
| ReplaceString(inString, searchString, replaceString, startPosition, count, compare)
Overloads:
|
The function returns a String in which a specified substring has been replaced with another substring a specified number of times. As an option, you can also specify a location in the string where the replacing process begins from (returns a string starting from that position). This function is typically used in a string to systematically replace one substring with another. |
|
A String value. | Assume that inStrings = "abc Abc abcdfg asdfabc sdfdfd", searchString = "abc", replaceString = "ABC":
|
| ReplicateString(String a, Integer b) | This function replicates the string in String a the number of times specified by b. |
|
A String value. | The return value of the following statement is "Stop! Stop! Stop!".
|
| Right(String a, Integer b) | This function extracts the specified number of characters in String a from the right side. |
|
A String value. |
The return value of the following statement is "mother".
|
| Soundex(string) | The function evaluates a text string and returns a four-character value that symbolizes the way the string sounds. Use this function whenever you want to locate records based on two or more field values that are spelled differently yet sound alike. You can also use this function to find customer names that have been misspelled. |
|
Text string. |
|
| Space(Integer a) | This function returns a String value that contains a specified number of spaces. |
|
A String value. |
|
| StrCmp(String a, String b) | This function is used to compare two strings. It returns positive if String a is greater than String b, returns "0" if String a is equal to String b, and returns negative if String a is less than String b. |
|
An Integer value. |
|
| StringSplit(inString, delimiterString, count, compare)
Overloads:
|
This function takes a String that contains a number of substrings, breaks it up into a specified number of substrings and returns an array containing the substrings. |
|
Array of String values. | Assume that inString = "Welcome use Logi Report":
|
| strReverse(inString) | This function reverses the string. It returns a String in which the character order of inString is reversed. If inString is a zero-length string (" "), the function returns a zero-length string. |
|
A String value. |
|
| toLongString(String a) | This function converts a string to a long string. |
|
A LongString value. | The return value of the following statement is "aa".
|
| ToNumber(Currency a) | This function converts a Currency to a Number. |
|
A Number value. |
|
| ToNumber(String a) | This function converts a specified string to a Number. |
|
A Number value. |
The return value of the following statement is "123.00".
|
| toString(LongString a) | This function converts a long string to a string. |
|
A String value. |
The return value of the following statement is "aa".
|
| ToText(Bit a) | This function converts a Bit value to a String, either "true" or "false". |
|
A String value. | The return value of the following statement is "false".
|
| ToText(Currency a) | This function converts a Currency value to a String. |
|
A String value. | he return value of the following statement is "$123.45".
|
| ToText(Currency a, String b) | This function converts a Currency value to a String. |
|
A String value. | The return value of the following statement is "$123.45".
|
| ToText(Currency a, String b, Integer c) | This function converts a Currency value to a String. |
|
A String value. | The return value of the following statement is "$123.5".
|
| ToText(Currency a, String b, Integer c, String d) | This function converts a Currency value to a String. |
|
A String value. | The return value of the following statement is "$1*234.57".
|
| ToText(Currency a, String b, Integer c, String d, String e) | This function converts a Currency value to a String. |
|
A String value. |
|
| ToText(Currency a, Integer c) | This function converts a Currency value to a String. |
|
A String value. | The return value of the following statement is "$123.46".
|
| ToText(Currency a, Integer c, String d) | This function converts a Currency value to a String. |
|
A String value. | The return value of the following statement is "$1,234.57".
|
| ToText(Currency a, Integer c, String d, String e) | This function converts a Currency value to a String. |
|
A String value. | The return value of the following statement is "$1,234.57".
|
| ToText(Date a) | This function converts a Date value to a String. |
|
A String value. | Suppose the date is July 15,1999, the return value of the following statement is "15-Jul-99".
|
| ToText(Date a, String b) | This function converts a Date value to a String. |
|
A String value. | Suppose the date is July 15,1999, the return value of the following statement is "15-Jul-99".
|
| ToText(DateTime a) | This function converts a DateTime value to a String. |
|
A String value. | Suppose the date is July 15,1999 and time is 7:42:51, the return value of the following statement is "15-Jul-99 7:42:51 AM".
|
| ToText(DateTime a, String b) | This function converts a DateTime value to a String. |
|
A String value. | Suppose the date is July 15,1999 and time is 7:42:51, the return value of the following statement is "07-15-99 7:42:51".
|
| ToText(DateTime a, String b, String c) | This function converts a DateTime value to a String. |
|
A String value. | Suppose the date is July 15,1999 and time is 7:42:51, the return value of the following statement is "15-Jul-99 7:42:51 AM".
|
| ToText(DateTime a, String b, String c, String d) | This function converts a DateTime value to a String. |
|
A String value. |
Suppose the date is Oct. 7,1999 and time is 7:42:51, the return value of the following statement is "07-Oct-99 7:42:51 AM".
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(Integer a) | This function converts an Integer value to a String. |
|
A String value. | The return value of the following statement is "456".
|
| ToText(Integer a, String b) | This function converts an Integer value to a String. |
|
A String value. | The return value of the following statement is "456".
|
| ToText(Integer a, String b, Integer c) | This function converts an Integer value to a String. |
|
A String value. | The return value of the following statement is "456.00".
|
| ToText(Integer a, String b, Integer c, String d) | This function converts an Integer value to a String. |
|
A String value. | The return value of the following statement is "1,234.00".
|
| ToText(Integer a, String b, Integer c, String d, String e) | This function converts an Integer value to a String. |
|
A String value. | The return value of the following statement is "1,234.00".
|
| ToText(Integer a, Integer c) | This function converts an Integer value to a String. |
|
A String value. | The return value of the following statement is "123.00".
|
| ToText(Integer a, Integer c, String d) | This function converts an Integer value to a String. |
|
A String value. | The return value of the following statement is "1,234.00".
|
| ToText(Integer a, Integer c, String d, String e) | This function converts an Integer value to a String. |
|
A String value. | The return value of the following statement is "12,345.00".
|
| ToText(Number a) | This function converts a Double value to a String. |
|
A String value. | The return value of the following statement is "123.456".
|
| ToText(Number a, String b) | This function converts a Double value to a String. |
|
A String value. | The return value of the following statement is "1236.46".
|
| ToText(Number a, String b, Integer c) | This function converts a Double value to a String. |
|
A String value. | The return value of the following statement is "123.46".
|
| ToText(Number a, String b, Integer c, String d) | This function converts a Double value to a String. |
|
A String value. | The return value of the following statement is "1,234.57".
|
| ToText(Number a, String b, Integer c, String d, String e) | This function converts a Double value to a String. |
|
A String value. | The return value of the following statement is "1,234.57".
|
| ToText(Number a, Integer c) | This function converts a Double value to a String. |
|
A String value. | The return value of the following statement is "123.46".
|
| ToText(Number a, Integer c, String d) | This function converts a Double value to a string. |
|
A String value. | The return value of the following statement is "1,234.6".
|
| ToText(Number a, Integer c, String d, String e) | This function converts a Double value to a String. |
|
A String value. | The return value of the following statement is "1,234,57".
|
| ToText(String timezone, Date a) | This function converts a Date value to a String. |
|
A String value. | Suppose that today is 2018.09.27, the return value of the following statement is "26-Sep-18".
|
| ToText(String timezone, Date a, String pattern) | This function converts a Date value to a String. |
|
A String value. | Suppose that today is 2018.09.27, the return value of the following statement is "2018/09/26".
|
| ToText(String timezone, Datetime a) | This function converts a DateTime value to a String. |
|
A String value. | Suppose that the current date time is 2018.09.27 5:07:37 PM, the return value of the following statement is "27-Sep-18 9:07:37 AM".
|
| ToText(String timezone, Datetime a, String pattern) | This function converts a DateTime value to a String. |
|
A String value. | Suppose that the current date time is 2018.09.27 5:07:37 PM, the return value of the following statement is "2018/09/27 09:07:37".
|
| ToText(String timezone, Datetime a, String pattern, String am) | This function converts a DateTime value to a String. |
|
A String value. | Suppose that the current date time is 2018.09.27 5:07:37 PM, the return value of the following statement is "2018/09/27 09:07:37 AM".
|
| ToText(String timezone, Datetime a, String pattern, String am, String pm) | This function converts a DateTime value to a String. |
|
A String value. | Suppose that the current date time is 2018.09.27 5:07:37 PM, the return value of the following statement is "2018/09/27 09:07:37 AM".
|
| ToText(String timezone, Time a) | This function converts a Time value to a String. |
|
A String value. | Suppose that the current time is 5:07:37 PM, the return value of the following statement is "9:07:37 AM".
|
| ToText(String timezone, Time a, String pattern) | This function converts a Time value to a String. |
|
A String value. | Suppose that the current time is 5:07:37 PM, the return value of the following statement is "09:07:37".
|
| ToText(String timezone, Time a, String pattern, String am) | This function converts a Time value to a String. |
|
A String value. | Suppose that the current time is 5:07:37 PM, the return value of the following statement is "09:07:37 am".
|
| ToText(String timezone, Time a, String pattern, String am, String pm) | This function converts a Time value to a String. |
|
A String value. | Suppose that the current time is 5:07:37 PM, the return value of the following statement is "09:07:37 am".
|
| ToText(Time a) | This function converts a Time value to a String. |
|
A String value. | Suppose the time is 12:40:30, the return value of the following statement is "12:40:30 PM".
|
| ToText(Time a, String b) | This function converts a Time value to a String. |
|
A String value. | Suppose the time is 08:48:58, the return value of the following statement is "8:48:58".
|
| ToText(Time a, String b, String c) | This function converts a Time value to a String. |
|
A String value. | Suppose the time is 8:48:58 in the morning, the return value of the following statement is "8:48:58 AM".
|
| ToText(Time a, String b, String c, String d) | This function converts a Time value to a String. |
|
A String value. |
Suppose the time is 08:48:58, the return value of the following statement is "8:48:58 AM".
Suppose the time is 21:49:59, the return value of the following statement is "9:49:59 PM".
|
| ToWords(Currency a) | This function converts a Number field value or the result of a numeric calculation to words. |
|
A String value. | The return value of the following statement is three and "15/100".
|
| ToWords(Currency a, Integer b) | This function converts a Number field value or the result of a numeric calculation to words. |
|
A String value. | The return value of the following statement is three and "5/10".
|
| ToWords(Integer a) | This function converts a Number field value or the result of a numeric calculation to words. |
|
A String value. | The return value of the following statement is "twenty and xx/100".
|
| ToWords(Integer a, Integer b) | This function converts a Number field value or the result of a numeric calculation to words. |
|
A String value. | The return value of the following statement is "three and x/10".
|
| ToWords(Number a) | This function converts a Number field value or the result of a numeric calculation to words. |
|
A String value. | The return value of the following statement is "ten and 12/100".
|
| ToWords(Number a, Integer b) | This function converts a Number field value or the result of a numeric calculation to words. |
|
A String value. | The return value of the following statement is "three and 1/10".
|
| Translate(String a) | This function searches for the NLS translation of current field value in the bound data mapping file. For an illustrate example, see Built-in Functions for NLS. |
|
A String value. It is the NLS translation of the field value if a data mapping file is bound to it and the NLS translation can be found, or null if the string is null, or the string itself when the corresponding NLS translation cannot be found in the data mapping file or the file cannot be found. | The return value of the following statement is "美国" and the locale is China if the language of the data mapping file bound to it is Chinese and it contains the mapping: ["USA", "美国"].
|
| Translate(String a, String b) | This function accesses the corresponding data mapping file dynamically according to the language selected from the language drop-down list on the NLS language toolbar and searches for the NLS translation of String b in the data mapping file. For an illustrate example, see Built-in Functions for NLS. |
|
A String value. It is the NLS translation of the field value if a data mapping file is bound to it and the NLS translation can be found, or null if the string is null, or the string itself when the corresponding NLS translation cannot be found in the data mapping file or the file cannot be found. | The return value of the following statement is "美国" and the locale is China if the language of the data mapping file bound to it is Chinese and it contains the mapping: ["USA", "美国"].
|
| Trim(String a) | This function removes the leading and the trailing spaces from string arguments. |
|
A String value. | The return value of the following statement is "he is a boy".
|
| TrimLeft(String a) | This function removes the spaces on the left side of the string. |
|
A String value. | The return value of the following statement is" he is a boy".
|
| TrimRight(String a) | This function removes the spaces on the right side of the string. |
|
A String value. | The return value of the following statement is "he is a boy".
|
| UpperCase(String a) | This function converts all letters in string a to uppercase letters. |
|
A String value. | The return value of the following statement is "HE IS A BOY".
|
| Val(String a) | This function reads a string containing numbers (for example, address, phone, or social security number), and converts them to a decimal value. The function stops reading when it finds the first character in the string. |
|
A Number value. | The return value of the following statement is "63,550,513.00".
|
Soundex(string)
You need to pay attention to the following when using this function:
- The value string must be in quotes.
- Soundex works only with values that begin with the same letter. For example, Soundex returns the same value for Chris and Cris C620 but not for Kris K620. Soundex creates a code based on the first character in the string plus three characters based on the following:
- Non-alpha characters like numbers and punctuation become -1.
- The characters "a", "e", "i", "o", "u", "y", "h", and "w" are ignored (except when they are the first character in the original string).
- The characters "b", "f", "p", and "v" become 1.
- The characters "c", "g", "j", "k", "q", "s", "x", and "z" become 2.
- The characters "d" and "t" become 3.
- The character "l" becomes 4.
- The characters "m" and "n" become 5.
- The character "r" becomes 6.
- If the resulting code is only two or three characters long, Soundex uses zeros to fill it out to four characters. For example, in the name Lauren, only "L", "r", and "n" are translated (Lrn), so the resulting Soundex code becomes L650.
- If the resulting code is more than four characters long, Soundex ignores all characters after the fourth character. For example, in the name Patrick, "P", "t", "r", "c", and "k" can be translated (Ptrck), but the resulting Soundex code is only four characters P362.
ToText(Time a, String b, String c, String d)
You need to pay attention to the following when using this function:
- When converting a Double value, Currency value or an Integer value to a String, you can use the following special characters as format in argument b:
Symbol Meaning Notes 0 A digit * A digit, zero shows as a star. Cannot mix "0", "*", and "_" in same format. _ A digit, zero shows as a space. Cannot mix "0", "*", and "_" in same format. # A digit, zero shows as absent. . Placeholder for decimal separator. , Placeholder for grouping delimiter. Shows the interval to be used. ; Separates formats. Positive and negative. - If there is no explicit negative sign, "-" is prefixed. "0.00" -> "0.00;-0.00" % Divided by 100 and shows as percentage. X Any other characters can be used in the prefix or suffix. - Time format syntax: You can specify the time format by means of a String time pattern. In this pattern, all ASCII letters are reserved as pattern letters, which are defined as the following:
Symbol Meaning Presentation Example G era designator Text AD y year Number 1996 M month in year Text & Number July & 07 d day in month Number 10 h hour in am/pm (1~12) Number 12 H hour in day (0~23) Number 0 m minute in hour Number 30 s second in minute Number 55 S millisecond Number 978 E day in week Text Tuesday D day in year Number 189 F day of week in month Number 2 (2nd Wed in July) w week in year Number 27 W week in month Number 2 a am/pm marker Text PM k hour in day (1~24) Number 24 K hour in am/pm (0~11) Number 0 z time zone Text Pacific Standard Time ' escape for text '' single quote
Other Functions
| Function | Description | Parameter | Return Value | Example |
|---|---|---|---|---|
| Choose(Integer, Array) | This function returns a value from the array based on the value of Integer. For example, if integer is 0, it returns the first element in the array; if index is 1, it returns the second element in the array. |
|
A value from the element in the given array. The type of the return value is the same as the type of the element. |
|
| currentBurstingSchema() | This function returns the names of the applied bursting schemas in the current report. When isRunBursting() returns "false", the return value of currentBurstingSchema() is "NULL". | A String value. | If you apply two bursting schemas to the current report: VP and Manager, the return value of the following statement is "VP,Manager".
|
|
| eqv(booleanx, booleany) |
This function returns a Boolean value of booleanx eqv booleany,
|
|
A Boolean value. |
|
| GetInfo(String) | This function gets information of a given key in the global level information container of the Information Bus. |
|
A String value. | If you want to get information containing a key-value pair, TestKey, and TestValue, use the following statement:
|
| getLanguage() | This function returns the language name of the locale that the current running task is based on. | The return value is either the empty string or a lowercase ISO 639 code. | When the locale is set to en_US, the return value of the following statement is "en".
|
|
| getLocale() | This function returns the language and locale name that the current running task is based on. | A string value containing language and locale information, for example, country. | When the locale is set to en_US, the return value of the following statement is "en_US".
|
|
| GetOrgInfo(String) | This function gets information of a given key in the organization level information container of the Information Bus that the current user can access. |
|
A String value. | If you want to get information containing a key-value pair, TestKey, and TestValue, use the following statement:
|
| getSecurityContext() | This function returns a Security Context object, which provides the method get() to get the Security Context instance from Designer or Server. | A DbSecurityContext object. | Import userClass from "UserFunction";
|
|
| getSystemTimezone() | This function returns the time zone ID of the runtime environment. | A String value. |
When the runtime time zone is GMT-0500, the following statement returns GMT-0500:
|
|
| GetUserInfo(String) | This function gets information of a given key in the user level information container of the Information Bus that the current user can access. |
|
A String value. | If you want to get information containing a key-value pair, TestKey, and TestValue, use the following statement:
|
| getValueTimezone(Date) | This function returns the value-level time zone ID of a Date value. |
|
A String value. |
The following statement returns GMT-0500:
|
| getValueTimezone(DateTime) | This function returns the value-level time zone ID of a DateTime value. |
|
A String value. |
The following statement returns GMT-0500:
|
| getValueTimezone(Time) | This function returns the value-level time zone ID of a Time value. |
|
A String value. |
The following statement returns GMT-0500:
|
| isAll(Array) | This function checks whether the value of the specified parameter is "ALL". | A Boolean value. | When the parameter PEndDate supports multiple values and its value is set to "All", the following statement returns "true".
|
|
| isCountry(String) | This function compares the specified parameter with that of the country setting of JVM based on default locale. The specified string must be an uppercase 2-letter ISO 3166 code. | A Boolean value. |
If If |
|
| IsNoRecord() | This function tells whether a report has returned a record or not. If the report has no value returned, the function returns "True"; otherwise "False".
You cannot apply a formula that calls this function to queries, and this formula only takes effect when laying out the report which contains it. |
A Boolean value. | When using this function, you should refer to a DBField to identify whether this formula is a record level formula so that Logi Report Engine can calculate it when fetching the record. For example,
In this example, if no data is returned, Logi Report displays the tip "There is no data" is displayed; If data is returned, Logi Report display the total number of records. |
|
| IsNull(DBfield a) | This function tells whether a specified value (especially the value of a DBField) is null or not. If the value is Null, the function returns "True"; otherwise "False". |
|
A Boolean value. |
The return value of the following statement is "False": 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": The following formula can summarize the Grand Total while ignoring the null value or no-record column:
|
| isNumeric(inString) | This function returns a Boolean value if inString is a math number string. |
|
A Boolean value. |
|
| isRunBursting() | This function returns "true" if the current report is running based on a bursting schema; else returns "false". | A Boolean value. |
If you schedule a bursting report to run based on a bursting schema, the return value of the following statement is "true": If a bursting report is scheduled to run a normal result, the return value of the following statement is "false":
|
|
| Next(DBfield a) | This function returns the next value of the current DBField. |
|
A DBField value. | Suppose you build a report about customer orders. If you use this function on "Ship Date" and insert it into the detail panel, when you run the report, you can see Logi Report displays the next records after each record according to the following statement:
|
| Next(DBfield a, Integer b) | This function returns the next Nth record decided by the argument b. |
|
A DBField value. |
Suppose you build a report about customer orders. If you use this function on "Ship Date" and set the argument b as "1", when you run the report, you can see Logi Report displays the next first record after each record according to the following statement.
|
| nextMember() | This function is used in the custom aggregation expression to locate the next member of a group or detail object.
|
Suppose that a group or detail object contains x members (confined to all parent groups' current members) and the custom aggregation is executed on the ith member, if "i = x", this function cannot locate any member. | A member of a group or detail object. | When a detail object contains these members: 10, 20, 30, 40, 50, and the custom aggregation is executed on 20, the following expression returns "30".
|
| nextMember(n) | This function is used in the custom aggregation expression to locate the next nth member of a group or detail object.
|
Suppose that a group or detail object contains x members (confined to all parent groups' current members) and the custom aggregation is executed on the ith member, if "i + n > x", this function cannot locate any member. |
A member of a group or detail object. | When a detail object contains these members: 10, 20, 30, 40, 50, and the custom aggregation is executed on 20, the following expression returns "50".
|
| nextMember(n, start) | This function is used in the custom aggregation expression to locate the next nth member after the member specified in the start parameter.
|
Suppose that a group or detail object contains x members (confined to all parent groups' current members), when start is the jth member, if "j + n > x", this function cannot locate any member. |
A member of a group or detail object. | When a detail object contains these members: 10, 20, 30, 40, 50, the following expression returns "50".
|
| openBinFile(string a) | This function opens an image file which is saved in your file system according to the specified path. It enables user access to the computer file system and possibly passwords which may cause security issue, so if you have security concerns, you can disable the function by setting propertyenable_openBinFile to "false" in the FormulaConfig.properties file in <install_root>\bin. After you disable using the function, the function returns null but is still available in the Formula Editor for reference. |
|
The return value is a binary image file. | Suppose you have an image file photo1.gif which is saved in the following directory c:\images. The following example will open this image file.
|
| openBinURL(string a) | This function opens an image file according to the specified URL. It enables user access to the computer file system and possibly passwords which may cause security issue, so if you have security concerns, you can disable the function by setting enable_openBinURL to "false" in the FormulaConfig.properties file in <install_root>\bin. After you disable using the function, the function returns null but is still available in the Formula Editor for reference. |
|
A binary image file. | Suppose you have an image file in the following URL http://www.logianalytics.com/../../asset/images/Pic1.gif. The following example will open this image file Pic1.gif.
|
| openTxtFile(string a) | This function opens a text file which is saved in your file system according to the specified path. It enables user access to the computer file system and possibly passwords which may cause security issue, so if you have security concerns, you can disable the function by setting enable_openTxtFile to "false" in the FormulaConfig.properties file in <install_root>\bin. After you disable using the function, the function returns null but is still available in the Formula Editor for reference. |
|
A long varchar value. | Suppose you have a text file report.int in the following directory C:\LogiReport\Designer. The following example opens this text file report.ini.
|
| openTxtURL(string a) | This function opens a text file according to the specified URL. It enables user access to the computer file system and possibly passwords which may cause security issue, so if you have security concerns, you can disable the function by setting enable_openTxtURL to "false" in the FormulaConfig.properties file in <install_root>\bin. After you disable using the function, the function returns null but is still available in the Formula Editor for reference. |
|
A long varchar value. | Suppose you have a text file at the following URL http://www.logianalytics.com/logireport/report.ini. The following example will open the text file report.ini.
|
| Prev(DBfield a) | This function returns the previous value of the current DBField. |
|
A DBField value. | Suppose you build a report about customer orders. If you use this function on "Ship Date" and insert it into the detail panel, when you run the report, you can see Logi Report displays the previous records after each record according to the following statement.
|
| Prev(DBfield a, Integer b) | This function returns the previous Nth record decided by the argument b. |
|
A DBField value. | Suppose you build a report about customer orders. If you use this function on "Ship Date" and set the argument b as 4, when you run the report, you can see that Logi Report displays the previous 4th record before each record according to the following statement.
|
| prevMember() | This function is used in the custom aggregation expression to locate the previous member of a group or detail object.
|
Suppose that a group or detail object contains x members (confined to all parent groups' current members) and the custom aggregation is executed on the ith member, if "i = 1", this function cannot locate any member. | A member of a group or detail object. | When a detail object contains these members: 10, 20, 30, 40, 50, and the custom aggregation is executed on 20, the following expression returns "10".
|
| prevMember(n) | This function is used in the custom aggregation expression to locate the previous nth member of a group or detail object.
|
Suppose that a group or detail object contains x members (confined to all parent groups' current members) and the custom aggregation is executed on the ith member, if "i - n < 1" , this function cannot locate any member. |
A member of a group or detail object. | When a detail object contains these members: 10, 20, 30, 40, 50, and the custom aggregation is executed on 40, the following expression returns "10".
|
| prevMember(n, start) | This function is used in the custom aggregation expression to locate the previous nth member before the member specified in the start parameter. |
Suppose that a group or detail object contains x members (confined to all parent groups' current members), when start is the jth member, if "j - n < 1", this function cannot locate any member. |
A member of a group or detail object. | When a detail object contains these members: 10, 20, 30, 40, 50, the following expression returns "30".
|
| PutInfo(String a, String b) | This function puts or replaces information of a given key in the global level information container of the Information Bus. |
|
No return value. | If you want to put information containing a key-value pair, TestKey, and TestValue, use the following statement:
|
| PutOrgInfo(String a, String b) | This function puts or replaces information of a given key in the organization level information container of the Information Bus that the current user can access. | No return value. | If you want to put information containing a key-value pair, TestKey, and TestValue, use the following statement:
|
|
| PutUserInfo(String a, String b) | This function puts or replaces information of a given key in the user level information container of the Information Bus that the current user can access. |
|
No return value. | If you want to put information containing a key-value pair, TestKey, and TestValue, use the following statement:
|
| RemoveInfo(String) | This function removes information of a given key in the global level information container of the Information Bus. |
|
No return value. | If you want to remove information containing a key named TestKey, use the following statement:
|
| RemoveOrgInfo(String) | This function removes information of a given key in the organization level information container of the Information Bus that the current user can access. |
|
No return value. | If you want to remove information containing a key named TestKey, use the following statement:
|
| RemoveUserInfo(String) | This function is used to remove information of a given key in the user level information container of the Information Bus that the current user can access. |
|
No return value. | If you want to remove information containing a key named TestKey, use the following statement:
|
| reportName() | This function returns the current report name. | A String value. | If the current report name is Employee Information List, the return value of the following statement is "Employee Information List".
|
|
| Switch(Boolean[], Array) | The elements in the two parameters corresponding with each other. This function evaluates the elements in the first parameter from left to right, and returns element associated with the first element to evaluate to True.
For example,
If 1 is true, the function returns "a"; if 2 is true, it returns "b"; if 3 is true, it returns "c". |
|
One of the elements of the parameter array. The type of the return value is the same as the element in the array. | Insert the following function into the detail panel of a report,
|
| toBool(number or currency) | This function returns "True" if the parameter is positive or negative but not 0, and returns "False" if the parameter is 0. | Can be either a Number, Currency value, or expression. | A Boolean value. | toBool(@Discount) - Returns "False" if the discount is 0; otherwise, returns "True". |
| toNumber(Boolean) | This function returns "1" if the parameter is True, and returns "0" if the parameter is False. | 1 or 0. |
|
|
| xor(booleanx, booleany) | This function returns a Boolean value of booleanx Exclusive OR booleany.
|
|
A Boolean value. |
|