Previous Topic
Appendix 1: Formula Functions
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:
- 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 directly.
| 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/2019, the return range is from 09/15/2019 to 10/14/2019. | 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. For example, if today is 10/13/2019, the return range is from 08/12/2019 to 09/13/2019. | If today is 10/13/2019, 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. For example, if today is 10/13/2019, the return range is from 07/13/2019 to 08/11/2019. | If today is 10/13/2019, 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/2019, the return range is from 04/15/2019 to the future. | If today is 10/10/2019, 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/2019, the return range is from 04/16/2019 to the future. | If today is 10/10/2019, 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/2019, the return range is from the past to 04/15/2019. | If today is 10/10/2019, 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/2019, the return range is from the past to 04/14/2019. | If today is 10/10/2019, 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/2019, 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/2019, 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/2019, 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/2019, 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/2019, 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/2019, 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/2019, the return range is from 3/18/2019 to 4/14/2019. | If today is 10/10/2019, 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/2019, the return range is from (include) 04/09/2019 to 04/15/2019. | If today is 10/10/2019, 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/2019, the return range is from 03/01/2019 to 03/31/2019. | If today is 10/10/2019, 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/15/2019, the return range is from 04/07/2019 to 04/13/2019. | If today is 10/10/2019, 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/2019, the return range is from 04/01/2018 to 04/15/2018. | If today is 10/10/2019, 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/2019, the return range is from 01/01/2018 to 04/15/2018. | If today is 10/10/2019, 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/2019, the return range is from 04/01/2019 to 04/15/2019. | If today is 10/10/2019, 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/2019, the return range is from 04/15/2019 to 05/15/2019. | If today is 10/10/2019, 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/2019, the return range is from 05/16/2019 to 06/14/2019. | If today is 10/10/2019, 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/2019, the return range is from 06/15/2019 to 07/14/2019. | If today is 10/10/2019, 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/2018, the return range is from 07/15/2018 to 04/15/2019. | If today is 10/10/2019, 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 earlier than the current date. For example, if today is 04/15/2019, the return range is from the past to 01/14/2019. | If today is 10/10/2019, 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/12/2019, the return range is from 04/07/2019 to 04/12/2019. | If today is 10/10/2019, 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/2019, the return range is from 01/01/2019 to 04/15/2019. | If today is 10/10/2019, the return value of the following statement is "true".
|
Date/Time Functions
You can use the Date/Time functions to convert numbers to dates and dates to numbers.
| Function | Description | Argument | Example |
|---|---|---|---|
| CurrentDate() | This function returns a Date value indicating the current date. | If today is Oct. 6, 2021, the return value of the following statement is "10/06/21".
|
|
| CurrentDateTime() | This function returns a DateTime value indicating the current date and time. | If today is Oct.6, 2021, the current time is 5:10:27 PM, the return value of the following statement is "2021-10-06 05:10:27 PM".
|
|
| CurrentTime() | This function returns a Time value indicating the current time. | If the current time is 5:10:27 PM, 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 value. Its main feature is that the returned DateTime value 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 can 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:
|
|
| DateDiff(intervalType, startDateTime, endDateTime, firstDayOfWeek)
Overloads:
|
This function returns a Number value indicating the number of time intervals between two dates. |
|
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 preceding 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. 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 preceding 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 value indicating the specified part extracted from a DateTime value. |
|
|
| DateTimeTo2000(Datetime, Number) | This function returns a Date field with a four digit year.
|
|
DateTimeTo2000(ToDateTime(1998,12,12,3,2,1), 99) - Returns "2098-12-12 3:02:01". Here the window value is greater than the year, and so the year is changed to "20XX".
The following are examples of dates that are not affected by this function:
Some databases maintain the year of a date as a two-digit field. The following example simulate that Date field:
|
| DateTimeToDate(Datetime a) | This function evaluates the argument and returns a Date value indicating the date in the argument. |
|
If the current date is Oct. 17, 2021 and the current time is 12:27:15, the return value of the following statement is "10/17/2021".
|
| DateTimeToSeconds(Datetime a) | This function evaluates the argument and returns an Integer value indicating the number of seconds from 00:00:00 to the time in the argument. |
|
If the current date is Oct. 17, 2021 and the current time is 12:27:15, the return value of the following statement is "44835".
|
| DateTimeToTime(Datetime a) | This function evaluates the argument and returns a Time value indicating the time in the argument. |
|
If the current date is Oct. 17, 2021 and the current time is 12:27:15, the return value of the following statement is "12:27:15".
|
| DateTo2000(date, number) | This function returns 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 an Integer value indicating the day of the month. | If today is Oct. 17, 2021, the return value of the following statement is "17".
|
|
| Day(Date a) | This function returns an Integer value indicating the day in the argument. |
|
If the current date is Oct. 17, 2021, the return value of the following statement is "17".
|
| Day(Datetime a) | This function returns an Integer value indicating the day in the argument. |
|
If the current date is Oct. 17, 2021 and the current time is 12:27:15, the return value of the following statement is "17".
|
| DayOfWeek() | This function returns an Integer value indicating the day of the current week. | If today is Thursday, the return value of the following statement is "5".
|
|
| DayOfWeek(Date a) | This function extracts the day portion from the argument and returns an Integer value indicating which day the day is of the week. |
|
If today is Oct. 17, 2021 and today is Sunday, the return value of the following statement is "1".
|
| DayOfWeek(Datetime a) | This function extracts the day portion from the argument and returns an Integer value indicating which day the day is of the week. |
|
If the current date is Oct. 17, 2021, 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 an Integer value indicating the day today is of the year. | If today is Oct. 17, 2021, the return value of the following statement is "290".
|
|
| DTSTo2000(DateString, Number) | This function returns a DateTime value 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 argument and returns an Integer value indicating the number of seconds from 00:00:00 to the time in the argument. |
|
The return value of the following statement is "49335".
|
| FirstDayOfMonth(Date a) | This function returns a Date value indicating the first day of the month for the date in the argument. |
|
The return value of the following statement is "11/01/2019".
|
| FirstDayOfQuarter(Date a) | This function returns a Date value indicating the first day of the quarter for the date in the argument. |
|
The return value of the following statement is "10/01/2019".
|
| FirstDayOfWeek(Date a) | This function returns a Date value indicating the first day of the week for the date in the argument. |
|
The return value of the following statement is "10/20/2019".
|
| FirstDayOfYear(Date a) | This function returns a Date value indicating the first day of the year for the date in the argument. |
|
The return value of the following statement is "01/01/2019".
|
| FirstSundayOfMonth(Date a) | This function returns the first Sunday of the month (a Date value) for the date in the argument. |
|
The return value of the following statement is "11/03/2019".
|
| FirstSundayOfYear(Date a) | This function returns the first Sunday of the year (a Date value) for the date in the argument. |
|
The return value of the following statement is "01/06/2019".
|
| ForEachDay(Datetime a) | This function returns a Date value indicating the date in the argument. |
|
The return value of the following statement is "12/25/19".
|
| ForEachHalfMonth(DateTime a) | This function returns a Date value according to the day in the argument. If the day in the argument is less than 15, it returns the first day of the month; if the day is more than 15, it returns the 16th day of the month. |
|
ForEachHalfMonth(ToDateTime(2019,9,13,12,27,15)) - Returns "09/01/19".
|
| ForEachHalfYear(DateTime a) | This function returns a Date value according to the month in the argument. If the month in the argument is before July, it returns Jan.1 of the year; otherwise, it returns July 1 of the year. |
|
ForEachHalfYear(ToDateTime(2019,5,28,10,10,25)) - Returns "01/01/19".
|
| ForEachMonth(DateTime a) | This function returns a Date value indicating the first day of the month in the argument. |
|
The return value of the following statement is "09/01/19".
|
| ForEachQuarter(DateTime a) | This function returns a Date value according to the month in the argument. If the month in the argument 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. |
|
ForEachQuarter(ToDateTime(2019,2,14,2,10,23)) - Returns "01/01/19".
|
| ForEachWeek(DateTime a) | This function returns a Date value indicating the first day of the week in the argument. |
|
If the date and time is Oct. 15, 2021 5:15:20 and it is Friday, the return value of the following statement is "10/10/21".
|
| ForEachYear(DateTime a) | This function returns Jan.1 of the year. |
|
The return value of the following statement is "01/01/19".
|
| Hour() | This function returns an Integer value indicating the hour of the current time. | If the current time is 8:15:30, the return value of the following statement is "8".
|
|
| Hour(DateTime a) | This function returns an Integer value indicating the hour in the argument. |
|
The return value of the following statement is "9".
|
| Hour(Time a) | This function returns an Integer value indicating the hour in the argument. |
|
The return value of the following statement is "10".
|
| IsDate(number or string) | This function returns "true" if it can convert the argument to a valid Date value and returns "false" otherwise. A valid date is any date between 100-1-1 and 9999-12-31. |
|
|
| IsDateTime(number or string) | This function returns "true" if it can convert the argument to a valid DateTime value, and returns "false" otherwise. |
|
|
| IsTime(number or string) | This function returns "true" if it can convert the argument to a valid Time value, and returns "false" otherwise. |
|
|
| Minute() | This function returns an Integer value indicating the minute of the current time. | If the current time is 12:41:27, the return value of the following statement is "41".
|
|
| Minute(DateTime a) | This function returns an Integer value indicating the minute in the argument. |
|
The return value of the following statement is "20".
|
| Minute(Time a) | This function returns an Integer value indicating the minute in the argument. |
|
The return value of the following statement is "30".
|
| Month() | This function returns an Integer value indicating the current month of the year, 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 returns an Integer value indicating the month in the argument. |
|
The return value of the following statement is "6".
|
| Month(DateTime a) | This function returns an Integer value indicating the month in the argument. |
|
The return value of the following statement is "7".
|
| MonthName(month, abbreviate) | This function returns a String value indicating the name of the month in the argument. |
|
|
| Now() | This function returns a Time value indicating the current time. | 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 an Integer value indicating the quarter in the argument. The value can be 1, 2, 3, or 4, where 1 is for the first quarter. |
|
|
| Second() | This function returns an Integer value indicating the second of the current time. | If the current time is 4:55:03, the return value of the following statement is "3".
|
|
| Second(Datetime a) | This function returns an Integer value indicating the second in the argument. |
|
The return value of the following statement is "17".
|
| Second(Time a) | This function returns an Integer value indicating the second in the argument. |
|
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. |
If the calendar date is March 12, 2012, the function returns "03/12/2012". | |
| Timer() | This function returns a Number value indicating the number of seconds elapsed since midnight. | If the current time is 9:28:30 AM, the function returns "34110". | |
| ToDate(number or string) | This function uses IsDate(number or string) to check whether the argument is correct. If it is correct, the function returns a Date value between 100-1-1 and 9999-12-31; otherwise, the function returns null. |
|
|
| ToDate(Date a) | This function returns a Date value for the argument. |
|
The return value of the following statement is "07/15/19".
|
| ToDate(Datetime a) | This function returns a Date value indicating the date in the argument. |
|
The return value of the following statement is "07/15/19".
|
| ToDate(integer d) | This function converts the argument (a millisecond value representing the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT) to a Date value. |
|
The return value of the following statement is "01/02/70".
|
| ToDate(integer y, Integer m, Integer d) | This function returns a Date value from the arguments. |
|
The return value of the following statement is "07/15/19".
|
| ToDate(integer y, Integer m, Integer d, String timezone) | This function returns a Date value from the arguments. |
|
The return value of the following statement is "9/27/2018".
|
| ToDateTime(number or string) | This function uses IsDateTime(number or string) to check whether the argument is correct. If it is correct, the function returns a DateTime value between 100-1-1 and 9999-12-31; otherwise, the function returns null. |
|
|
| ToDateTime(date a) | This function returns a DateTime value for the argument. |
|
ToDateTime(ToDate("2004-2-23")) - Returns "2004-02-23 00:00:00". |
| ToDateTime(Date a, Time b) | This function returns a DateTime value from the arguments. |
|
The return value of the following statement is "2019-07-15 12:20:30".
|
| ToDateTime(Integer d) | This function converts the argument (a millisecond value representing the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT) to a DateTime value. Calculation of the function is based on the time zone. You must adjust the argument to reflect your time zone. |
|
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 returns a DateTime value from the arguments. |
|
The return value of the following statement is "2019-07-15 12:00:00".
|
| ToDateTime(Integer y, Integer m, Integer d, Integer h, Integer i, Integer s) | This function returns a DateTime value from the arguments. |
|
The return value of the following statement is "2019-07-15 08:05:30".
|
| ToDateTime(Integer y, Integer m, Integer d, Integer h, Integer i, Integer s, String timezone) | This function returns a DateTime value while converting the time zone from the arguments. |
|
The return value of the following statement is "10/19/2018 6:23:54 PM".
|
| Today() | This function returns a Date value indicating the current date. | If the current date is Oct. 11, 2021, the return value of the following statement is "10/11/21".
|
|
| ToTime(number or string) | This function uses IsTime(number or string) to check whether the argument is correct. If it is correct, the function returns a Time value between 00:00:00 and 23:59:59; otherwise, the function returns null. |
|
|
| ToTime(DateTime a) | This function returns a Time value indicating the time in the argument. |
|
The return value of the following statement is "10:55:45".
|
| ToTime(Integer t) | This function converts the argument (a millisecond value representing the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT) to a Time value. |
|
The return value of the following statement is "08:00:01".
|
| ToTime(Integer a, Integer b, Integer c) | This function returns a Time value from the arguments. |
|
The return value of the following statement is "10:10:10".
|
| ToTime(Integer a, Integer b, Integer c, String timezone) | This function returns a Time value from the arguments. |
|
The return value of the following statement is "4:20:15 PM".
|
| WeekdayName(weekday, abbreviate, firstDayOfWeek)
Overloads:
|
This function returns a String value indicating the weekday name for the argument. |
|
|
| WeekFrom1970() | This function returns an Integer value indicating the total number of weeks from 1970 to the current date. | If the current date is Oct. 13, 1999, the return value of the following statement is "1554".
|
|
| WeekFrom1970(Date, a) | This function returns an Integer value indicating the total number of weeks from 1970 to the date in the argument. |
|
The return value of the following statement is "1331".
|
| WeekFrom1970(DateTime, a) | This function returns an Integer value indicating the total number of weeks from 1970 to the date and time in the argument. |
|
The return value of the following statement is "940".
|
| WeekOfMonth() | This function returns an Integer value indicating the week of the current month. | If the current date is Oct. 13, 1999, the return value of the following statement is "3".
|
|
| WeekOfMonth(Date a) | This function returns an Integer value indicating the week of the month for the date in the argument. |
|
The return value of the following statement is "3".
|
| WeekOfMonth(DateTime a) | This function returns an Integer value indicating the week of the month for the date and time in the argument. |
|
The return value of the following statement is "4".
|
| WeekOfYear() | This function returns an Integer value indicating the week of the current year. | If the current date is Oct. 13, 1999, the return value of the following statement is "42".
|
|
| WeekOfYear(Date a) | This function returns an Integer value indicating the week of the year for the date in the argument. |
|
The return value of the following statement is "28".
|
| WeekOfYear(DateTime a) | This function returns an Integer value indicating the week of the year for the date and time in the argument. |
|
The return value of the following statement is "6".
|
| Year() | This function returns an Integer value indicating the current year. | If the current year is 2021, the return value of the following statement is "2021".
|
|
| Year(Date a) | This function returns an Integer value indicating the year of the date in the argument. |
|
The return value of the following statement is "2021".
|
| Year(DateTime a) | This function returns an Integer value indicating the year of the date and time in the argument. |
|
The return value of the following statement is "2021".
|
Financial Functions
| Function | Description | Argument | Example |
|---|---|---|---|
| DDB(cost, salvage, life, period, factor)
Overloads:
|
This function returns a Number value indicating the depreciation of an asset for a specific time period, using the double-declining balance method or another method as specified by the argument factor . |
|
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 returns a Number value indicating the turnover of the account receivable. |
|
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 a Number value indicating the result of cash flow vs total debt. |
|
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 returns a Number value indicating the current ratio. |
|
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 returns a Number value indicating the debt equity ratio. |
|
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 returns a Number value indicating the dividend yield. |
|
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 returns a Number value indicating the earnings of per common share. |
|
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 a Number value indicating the result of equity vs total assets. |
|
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 returns a Number value indicating the gross profit margin. |
|
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 returns a Number value indicating the interest coverage. |
|
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 returns a Number value indicating the turnover of the inventory. |
|
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 returns a Number value indicating the net profit margin. |
|
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 returns a Number value indicating the operating profit margin. |
|
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 returns a Number value indicating the price earning ratio. |
|
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 returns a Number value indicating the quick ratio. |
|
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 a Number value indicating the result of return on common equity. |
|
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 a Number value indicating the results of return on equity. |
|
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 a Number value indicating the result of return on invested capital. |
|
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 a Number value indicating the result of return on net fixed assets. |
|
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 a Number value indicating the result of return on total assets. |
|
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 value indicating the future value of an annuity based on periodic, fixed payments, and a fixed interest rate. |
|
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 value indicating the interest payment for a given period of an annuity based on periodic, fixed payments, and a fixed interest rate. |
|
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 value indicating the internal rate of return for a series of periodic cash flows (payments and receipts). |
|
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 value indicating the modified internal rate of return for a series of periodic cash flows (payments and receipts). |
|
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 value indicating the number of periods for an annuity based on periodic, fixed payments, and a fixed interest rate. |
|
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 value indicating the net present value of an investment based on a series of periodic cash flows (payments and receipts) and a discount rate. |
|
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 value indicating 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. |
|
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:
|
|
|
|
| PV(rate, periods, payment, futureMoney, type)
Overloads:
|
This function returns a Number value indicating the present value of an annuity based on periodic, fixed payments to be paid in the future, and at a fixed interest rate. |
|
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 value indicating 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. |
|
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 value indicating the straight-line depreciation of an asset for a single period. |
|
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 value indicating the sum-of-years' digits depreciation of an asset for a single period. |
|
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 | Argument | Return Value | Example |
|---|---|---|---|---|
| Abs(Integer, a) | This 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) | This 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) | This 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) | This function returns a number specifying the arctangent of a given number. In other words, it returns the angle whose tangent is the given number. 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) | This 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 an array. |
|
A Number or a Currency value. |
|
| cos(number) | This 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) | This function counts the number of values referred to by the argument. |
|
A Number value. |
|
| Count(DBField a, String groupby) | This function counts 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 the argument. |
|
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) | This 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 is larger than approximately 705. |
|
A number that is the value of e ^ Number. |
|
| integDiv(numbera, numberb) | This function returns the integer portion of the value numbera/numberb. |
|
A Whole Number value. | integDiv(33, 2) - Returns "16". |
| log(number) | This 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 that 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 data type of the argument. |
|
| 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 data type of the argument. It can 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 data type of the first argument. It can 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 data type of the argument. |
|
| 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 data type of the argument. 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 can 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 argument.
Expression (take MRound(Currency,Number) for example):
|
|
A number value that is a multiple of the second argument and at the same time is nearest to the first argument. |
|
| 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) | This function returns the value of numbera ^ numberb. |
|
|
|
| random(), random(seed)
Overloads:
|
This function returns a random number greater than or equal to 0 and less than 1. If seed is equal to 0, it returns the random number returned from the previous call to random(). If seed is not supplied or is greater than 0, it returns the next random number in the internally generated sequence of random numbers. If seed is less than 0, it 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 first argument. 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 argument 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 | Argument | 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) | This 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 (String origin) and returns the first occurrence of a string that starts with the start string (String start) and ends with the end string (String end) (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:
|
This 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 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 that 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 a specified string. |
|
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 tests if the content of a string is a number. |
|
A Boolean value. |
|
| Picture(String a, String b) | This function returns a string or values in a string in a specified format. |
|
A String value. |
|
| ProperCase()
Overloads: ProperCase(String) |
This 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:
|
This 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 (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 a string a number of times. |
|
A String value. | The return value of the following statement is "Stop! Stop! Stop!".
|
| Right(String a, Integer b) | This function extracts a specified number of characters in a string from the right side. |
|
A String value. |
The return value of the following statement is "mother".
|
| Soundex(string) | This 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 that contains a specified number of spaces. |
|
A String value. |
|
| StrCmp(String a, String b) | This function compares 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 Report":
|
| strReverse(inString) | This function reverses a 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 LongString value. |
|
A LongString value. | The return value of the following statement is "aa".
|
| ToNumber(Currency a) | This function converts a Currency value to a number. |
|
A Number value. |
|
| ToNumber(String a) | This function converts a string to a Number value. |
|
A Number value. |
The return value of the following statement is "123.00".
|
| toString(LongString a) | This function converts a LongString value 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, 2019, the return value of the following statement is "15-Jul-19".
|
| ToText(Date a, String b) | This function converts a Date value to a string. |
|
A String value. | Suppose the date is July 15, 2019, the return value of the following statement is "15/07/2019".
|
| ToText(DateTime a) | This function converts a DateTime value to a string. |
|
A String value. | Suppose the date is July 15, 2019 and time is 7:42:51, the return value of the following statement is "15-Jul-19 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, 2019 and time is 7:42:51, the return value of the following statement is "07-15-19 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, 2019 and time is 7:42:51, the return value of the following statement is "15-Jul-19 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, 2019 and time is 7:42:51, the return value of the following statement is "07-Oct-19 7:42:51 AM".
Suppose the date is Oct. 7, 2019 and time is 18:42:51, the return value of the following statement is "07-Oct-19 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(Locale, Currency, …)
|
The functions in this group convert a Currency value to a string based on a specified locale. |
|
A String value. | The return value of the following statement is "CHF 123'456'789.000".
|
ToText(Locale, Number, Digit, …)
|
The functions in this group convert a Number value to a string based on a specified locale. |
|
A String value. | The return value of the following statement is "987'654'321.000".
|
ToText(Locale, Number, Format, Digit, …)
|
The functions in this group convert a Number value to a string in a designated format based on a specified locale. |
|
A String value. | The return value of the following statement is "12'3456'7890.000".
|
ToText(Locale, TimeZone, Date, …)
|
The functions in this group convert a Date value to a string based on specified locale and time zone. |
|
A String value. | The return value of the following statement is "2021-08-08".
|
ToText(Locale, TimeZone, DateTime, …)
|
The functions in this group convert a DateTime value to a string based on specified locale and time zone. |
|
A String value. | The return value of the following statement is "2021-08-08 10:33:44 PM".
|
ToText(Locale, TimeZone, Time, …)
|
The functions in this group convert a Time value to a string based on the specified locale and time zone. |
|
A String value. | The return value of the following statement is "02:03:04 AM".
|
| 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 based on a specified time zone. |
|
A String value. | Suppose today is 2018.09.27, the return value of the following statement is "26-Sep-18".
|
| ToText(String timezone, Date a, String b) | This function converts a Date value to a string based on a specified time zone. |
|
A String value. | Suppose 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 based on a specified time zone. |
|
A String value. | Suppose the current date and 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 b) | This function converts a DateTime value to a string based on a specified time zone. |
|
A String value. | Suppose the current date and 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 b, String c) | This function converts a DateTime value to a string based on a specified time zone. |
|
A String value. | Suppose the current date and 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 b, String c, String d) | This function converts a DateTime value to a string based on a specified time zone. |
|
A String value. | Suppose the current date and 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 based on a specified time zone. |
|
A String value. | Suppose 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 b) | This function converts a Time value to a string based on a specified time zone. |
|
A String value. | Suppose 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 b, String c) | This function converts a Time value to a string based on a specified time zone. |
|
A String value. | Suppose 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 b, String c, String d) | This function converts a Time value to a string based on a specified time zone. |
|
A String value. | Suppose 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 Currency 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 Currency 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 an Integer 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 convert an Integer 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 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 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 the field value in the bound data mapping file. |
|
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. | For an illustrate example, see Built-in Functions for NLS. |
| Translate(String a, String b) | This function accesses the corresponding data mapping file dynamically according to the language selected to run the report and searches for the NLS translation of String b in the data mapping file. |
|
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. | For an illustrate example, see Built-in Functions for NLS. |
| Trim(String a) | This function removes the leading and the trailing spaces from a string. |
|
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 a 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 a 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 a string 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.
Converting Double/Currency/Integer Values
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 in the following table.
| 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 | Argument | 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 data type of the return value is the same as that of the element. |
|
| currentBurstingSchema() | This function returns the names of the bursting schemas the current report applies. 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".
|
|
| currentGroup() | This function returns the name of the group-by field for the group where the formula is executing in a banded object or table. When the group-by field applies special function, the name also includes the function name. | A String value. | Suppose you place a formula calling this function in the group header panel of the Order Date group in a banded object and the group applies the "For each month" special function, the formula returns "Order Date For each month". | |
| currentGroupLevel() | This function returns an integer indicating the group level of the group where the formula is executing in a banded object or table. | An Integer value. | Suppose you place a formula calling this function in the second group header panel of a banded object, the formula returns "2". | |
| currentUsedHeight() | This function returns the height out of totalAvailableHeight() in the current page that has been used for displaying the corresponding vertical banded object or table. | An Integer value. | ||
| currentUsedWidth() | This function returns the width out of totalAvailableWidth() in the current page that has been used for displaying the corresponding horizontal banded object or table. | An Integer value. | ||
| eqv(booleanx, booleany) |
This function returns a Boolean value of booleanx eqv booleany,
|
|
A Boolean value. |
|
| getAllGroups() | This function returns the names of all group-by fields of a banded object or table in an array, such as [Region, Country, City]. | An array. | ||
| getAllSortColumns() | This function returns the names of all sort-by fields of a banded object or group table in an array, such as [Product ID, Order ID, Quantity]. | An array. | ||
| getGroupBy(Integer) | This function returns the name of the group-by field for the specified group level in a banded object or table. |
|
A String 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 code 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 locale that the current running task is based on. | A String value containing locale information. | 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 get() method to get the Security Context instance from Designer or Server. | A DbSecurityContext object. | Import userClass from "UserFunction";
|
|
| getSortBy(Integer) | This function returns the name of the sort-by field for the specified sort level in a banded object or group table. |
|
A String value. | |
| 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. 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 that 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 Report Engine can calculate it when fetching the record. For example,
In this example, if no data is returned, Report displays the tip "There is no data" is displayed; if data is returned, 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. 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 the argument 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 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 Report displays the next first record after each record according to the following statement.
|
| nextMember() | You can use this function 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) | You can use this function 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) | You can use this function in the custom aggregation expression to locate the next nth member after the member specified by start.
|
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 that you save 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. If you have security concerns, you can disable the function by setting propertyenable_openBinFile to "false" in the file FormulaConfig.properties that is saved 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 in the following directory c:\images. The following example opens 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. If you have security concerns, you can disable the function by setting enable_openBinURL to "false" in the file FormulaConfig.properties that is saved 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 opens this image file Pic1.gif.
|
| openTxtFile(string a) | This function opens a text file that you save 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. If you have security concerns, you can disable the function by setting enable_openTxtFile to "false" in the file FormulaConfig.properties that is saved 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. If you have security concerns, you can disable the function by setting enable_openTxtURL to "false" in the file FormulaConfig.properties that is saved 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 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 Report displays the previous 4th record before each record according to the following statement.
|
| prevMember() | You can use this function 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) | You can use this function 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) | You can use this function in the custom aggregation expression to locate the previous nth member before the member specified by start. |
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 removes 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 a Sting value indicating 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 arguments corresponding with each other. This function evaluates the elements in the first argument 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 element of the 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 banded object,
|
| 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 a Number or Currency value, or an 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. |
|
|
| totalAvailableHeight() | This function returns the total height in the current page that is available for displaying the corresponding vertical banded object or table. | An Integer value. | ||
| totalAvailableWidth() | This function returns the total width in the current page that is available for displaying the corresponding horizontal banded object or table. | An Integer value. | ||
| xor(booleanx, booleany) | This function returns a Boolean value of booleanx Exclusive OR booleany.
|
|
A Boolean value. |
|