SUN Report Builder/Functions

From OpenOffice.org Wiki

Date/Time

Function Name	Description	Example
DATE	Provides an internal number for the date given. Parameter Name Parameter Description month An integer between 1 and 12 representing the month. day An integer between 1 and 31 representing the day of the month.
DATEDIF	Returns the number of years, months, or days between two dates. Parameter Name Parameter Description EndDate Second date. Format Format code.
DATEVALUE	Returns an internal number for a text having a possible date format.
DAY	Returns the sequential date of the month as an integer (1-31) in relation to the date value.
DAYS	Calculates the number of days between two dates. Parameter Name Parameter Description Date_1 The start date for calculating the difference in days.
HOUR	Determines the sequential number of the hour of the day (0-23) for the time value.
MINUTE	Determines the sequential number for the minute of the hour (0-59) for the time value.
MONTH	Determines the sequential number of a month of the year (1-12) for the date value.
NOW	Determines the current time of the computer.
SECOND	Determines the sequential number of the second of a minute (0-59) for the time value.
TIME	Determines a time value from the details for hour, minute and second. Parameter Name Parameter Description minute The integer for the minute. second The integer for the second.
TIMEVALUE	Returns a sequential number for a text shown in a possible time entry format.
TODAY	Determines the current date of the computer.
WEEKDAY	Returns the day of the week for the date value as an integer (1-7). Parameter Name Parameter Description Type Fixes the beginning of the week and the type of calculation to be used.(1 = Sunday is the first day of the week, with value 1; Saturday has value 7, 2 = Monday is the first day of the week, with value 1; Sunday has value 7, 3 = Monday is the first day of the week, with value 0; Sunday has value 6)
YEAR	Returns the year of a date value as an integer.

Mathematical

Function Name	Description	Example
ABS	Absolute value of a number.
AVERAGE	Returns the average of a sample.
AVERAGE	Returns the average of a sample.
EVEN	Rounds a positive number up and negative number down to the nearest even integer.
MAX	Returns the maximum value in a list of arguments.
MAXA	Returns the maximum value in a list of arguments. Text is evaluated as zero.
MIN	Returns the minimum value in a list of arguments.
MINA	Returns the smallest value in a list of arguments. Text is evaluated as zero.
MOD	Calculates the remainder of a division. Parameter Name Parameter Description Divisor The number by which the dividend is divided.
ODD	Rounds a positive number up and negative number down to the nearest odd integer.
SUM	Returns the sum of all arguments.

Logical

Function Name	Description	Example
AND	Returns TRUE if all arguments are TRUE.
FALSE	Defines the logical value as FALSE.
IF	Specifies a logical test to be performed. Parameter Name Parameter Description Then_value The result of the function if the logical test returns a TRUE. Otherwise_value The result of the function if the logical test returns FALSE.
NOT	Reverses the value of the argument.
OR	Returns TRUE if an argument is TRUE.
TRUE	Returns the logical value TRUE.
XOR	Returns TRUE if a odd number of arguments are TRUE.

Information

Function Name	Description	Example
CHOOSE	Selects a value from a list of up to 30 value arguments. Parameter Name Parameter Description value Value 1, value 2,... The list of arguments from which a value is chosen.
COUNT	Counts how many numbers are in the list of arguments.
COUNTA	Counts how many values are in the list of arguments.
COUNTBLANK	Counts the blank cells in a specified range.
HASCHANGED	Checks whether the columns specified by the given names has changed.
ISBLANK	Returns TRUE if value refers to an empty cell.
ISERR	Returns TRUE if the value is an error value not equal to #N/A.
ISERROR	Returns TRUE if the value is an error value.
ISEVEN	Returns TRUE if value is an even integer.
ISLOGICAL	Returns TRUE if the value carries a logical number format.
ISNA	Returns TRUE if value equals #N/A.
ISNONTEXT	Returns TRUE if the value is not text.
ISNUMBER	Returns TRUE if value is a number.
ISODD	Returns TRUE if value is an odd integer.
ISREF	Returns TRUE if value is a reference.
ISTEXT	Returns TRUE if value is text.
NA	Not available. Returns the error value #N/A.

User-Defined

Function Name	Description	Example
NULL	Returns a NULL-Value

Text

Function Name	Description	Example
EXACT	Specifies whether two texts are identical. Parameter Name Parameter Description text_2 The second text for comparing texts.
FIND	Looks for a string of text within another (case sensitive) Parameter Name Parameter Description text The text in which a search is to be made. position The position in the text from which the search starts.
LEFT	Returns the first character or characters of a text. Parameter Name Parameter Description number The number of characters for the start text.
LEN	Calculates length of a text string.
LOWER	Converts text to lowercase.
MID	Returns a partial text string of a text. Parameter Name Parameter Description start The position from which the part word is to be determined. number The number of characters for the text.
REPLACE	Replaces characters within a text string with a different text string. Parameter Name Parameter Description position The character position from which text is to be replaced. length The number of characters to be replaced. new text The text to be inserted.
REPT	Repeats text a given number of times. Parameter Name Parameter Description number The number of times the text is to be repeated.
RIGHT	Returns the last character or characters of a text. Parameter Name Parameter Description number The number of characters for the end text.
SUBSTITUTE	Substitutes new text for old text in a string. Parameter Name Parameter Description search_text The partial string to be (repeatedly) replaced. new text The text which is to replace the text string. occurrence Which occurence of the old text is to be replaced.
T	Converts a value into text.
TEXT	Converts a number to text according to a given format.
TRIM	Removes extra spaces from text.
UNICHAR	Converts a code number into a Unicode character or letter.
UNICODE	Returns the numeric code for the first Unicode character in a text string.
UPPER	Converts text to uppercase.
URLENCODE	Applies URL-Encoding to a text given in the first parameter using the encoding specified in the second parameter. If no encoding is given, ISO-8859-1 is assumed. Parameter Name Parameter Description Encoding The Encoding to be used.

Rounding

Function Name	Description	Example
INT	Rounds a number down to the nearest integer.