Difference between revisions of "SUN Report Builder/Functions"

From Apache OpenOffice Wiki
Jump to: navigation, search
 
(12 intermediate revisions by 9 users not shown)
Line 1: Line 1:
−
The Sun Report Builder supplies the following functions:
+
[[fr:FR/Documentation/Base/Rapports/Fonctions]]
 +
{{Database/Lang|Base/Reports/Functions}}
  
 +
'''Date/Time'''<br>
  
−
'''Rounding'''<br>
+
{| cellpadding="10" border="1" class="prettytable"
−
{| class="prettytable" cellpadding="10" border="1"
+
|-
−
| '''''Function Name'''''
+
| '''''Function Name'''''  
 
| '''''Description'''''  
 
| '''''Description'''''  
−
| '''''Example '''''
+
| '''''Example'''''
−
 
+
 
|-
 
|-
−
| INT
+
| DATE
−
| Returns a number down to the nearest integer
+
| Provides an internal number for the date given.
 +
{| cellpadding="10" border="1" class="prettytable"
 +
|-
 +
| '''''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.
 +
{| cellpadding="10" border="1" class="prettytable"
 +
|-
 +
| '''''Parameter Name'''''
 +
| '''''Parameter Description'''''
 +
|-
 +
| EndDate
 +
| Second date.
 +
|-
 +
| Format
 +
| Format code.
 
|}
 
|}
−
<br>
 
−
'''Information'''<br>
 
−
{| class="prettytable" cellpadding="10" border="1"
 
−
| '''''Function Name'''''
 
−
| '''''Description'''''
 
−
| '''''Example '''''
 
  
 +
|
 
|-
 
|-
−
| CHOOSE
+
| DATEVALUE
−
| Uses an index to return a value from a list of values.
+
| Returns an internal number for a text having a possible date format.  
 
|  
 
|  
−
 
 
|-
 
|-
−
| HASCHANGED
+
| DAY
−
| Checks, whether the columns specified by the given names has changed.
+
| 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.
 +
{| cellpadding="10" border="1" class="prettytable"
 +
|-
 +
| '''''Parameter Name'''''
 +
| '''''Parameter Description'''''
 +
|-
 +
| Date_1
 +
| The start date for calculating the difference in days.
 +
|}
  
 +
|
 
|-
 
|-
−
| ISBLANK
+
| HOUR
−
| Checks, whether the value is undefined (null).
+
| Determines the sequential number of the hour of the day (0-23) for the time value.  
 
|  
 
|  
−
 
 
|-
 
|-
−
| ISERR
+
| MINUTE
−
| Tells if the parameter is of error type but returns false if the error is of type NA.
+
| Determines the sequential number for the minute of the hour (0-59) for the time value.  
 
|  
 
|  
−
 
 
|-
 
|-
−
| ISERROR
+
| MONTH
−
| Tells if the parameter is of error type but returns false.
+
| Determines the sequential number of a month of the year (1-12) for the date value.  
 
|  
 
|  
−
 
 
|-
 
|-
−
| ISEVEN
+
| NOW
−
| Checks whatever the value is an even number.
+
| Determines the current time of the computer.  
 
|  
 
|  
−
 
 
|-
 
|-
−
| ISLOGICAL
+
| SECOND
−
| Checks whatever the value is of type Logical.
+
| 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.
 +
{| cellpadding="10" border="1" class="prettytable"
 +
|-
 +
| '''''Parameter Name'''''
 +
| '''''Parameter Description'''''
 +
|-
 +
| minute
 +
| The integer for the minute.
 +
|-
 +
| second
 +
| The integer for the second.
 +
|}
  
 +
|
 
|-
 
|-
−
| ISNA
+
| TIMEVALUE
−
| Tells if the parameter is of error type NA.
+
| Returns a sequential number for a text shown in a possible time entry format.  
 
|  
 
|  
−
 
 
|-
 
|-
−
| ISNONTEXT
+
| TODAY
−
| Checks whatever the value is not of type Text.
+
| Determines the current date of the computer.  
 
|  
 
|  
 +
|-
 +
| WEEKDAY
 +
| Returns the day of the week for the date value as an integer (1-7).
 +
{| cellpadding="10" border="1" class="prettytable"
 +
|-
 +
| '''''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)
 +
|}
  
 +
|
 
|-
 
|-
−
| ISNUMBER
+
| YEAR
−
| Checks whatever the value is of type Number.
+
| Returns the year of a date value as an integer.  
 
|  
 
|  
 +
|}
  
 +
<br> '''Mathematical'''<br>
 +
 +
{| cellpadding="10" border="1" class="prettytable"
 +
|-
 +
| '''''Function Name'''''
 +
| '''''Description'''''
 +
| '''''Example'''''
 
|-
 
|-
−
| ISODD
+
| ABS
−
| Checks whatever the value is an odd number.
+
| Absolute value of a number.  
 
|  
 
|  
−
 
 
|-
 
|-
−
| ISREF
+
| AVERAGE
−
| Checks whatever the value is a reference.
+
| Returns the average of a sample.  
 
|  
 
|  
−
 
 
|-
 
|-
−
| ISTEXT
+
| AVERAGE
−
| Checks whatever the value is of type Text.
+
| Returns the average of a sample.  
 
|  
 
|  
−
 
 
|-
 
|-
−
| NA
+
| EVEN
−
| Returns the constant error NA.
+
| Rounds a positive number up and negative number down to the nearest even integer.  
 
|  
 
|  
−
|}
 
−
<br>
 
−
'''Date/Time'''<br>
 
−
{| class="prettytable" cellpadding="10" border="1"
 
−
| '''''Function Name'''''
 
−
| '''''Description'''''
 
−
| '''''Example '''''
 
−
 
 
|-
 
|-
−
| DATE
+
| MAX
−
| Returns the number of days since 01.01.1900
+
| Returns the maximum value in a list of arguments.  
−
{| class="prettytable" cellpadding="10" border="1"
+
|  
−
| '''''Parameter Name'''''
+
−
| '''''Parameter Description'''''
+
−
 
+
 
|-
 
|-
−
| Month
+
| MAXA
−
| Month
+
| Returns the maximum value in a list of arguments. Text is evaluated as zero.
 +
|  
 
|-
 
|-
−
| Day
+
| MIN
−
| Day
+
| Returns the minimum value in a list of arguments.
−
|}
+
−
 
+
 
|  
 
|  
−
 
 
|-
 
|-
−
| DATEDIF
+
| MINA
−
| Returns the number of years, months, or days between two dates.
+
| Returns the smallest value in a list of arguments. Text is evaluated as zero.
−
{| class="prettytable" cellpadding="10" border="1"
+
|
 +
|-
 +
| MOD
 +
| Calculates the remainder of a division.  
 +
{| cellpadding="10" border="1" class="prettytable"
 +
|-
 
| '''''Parameter Name'''''  
 
| '''''Parameter Name'''''  
−
| '''''Parameter Description'''''  
+
| '''''Parameter Description'''''
−
 
+
 
|-
 
|-
−
| Date2
+
| Divisor
−
| Second date.
+
| The number by which the dividend is divided.
−
|-
+
−
| Format
+
−
| Format code.
+
 
|}
 
|}
  
 
|  
 
|  
−
 
 
|-
 
|-
−
| DATEVALUE
+
| ODD
−
| Returns date serial number from given text.
+
| Rounds a positive number up and negative number down to the nearest odd integer.  
 
|  
 
|  
−
 
 
|-
 
|-
−
| DAY
+
| SUM
−
| Returns the day of a date
+
| Returns the sum of all arguments.
 
|  
 
|  
 +
|}
  
−
|-
+
<br> '''Logical'''<br>
−
| HOUR
+
−
| Returns the hour (0 through 23) from a time.
+
−
|
+
  
 +
{| cellpadding="10" border="1" class="prettytable"
 
|-
 
|-
−
| MONTH
+
| '''''Function Name'''''
−
| Returns the month of a date
+
| '''''Description'''''
 +
| '''''Example'''''
 +
|-
 +
| AND
 +
| Returns TRUE if all arguments are TRUE.
 
|  
 
|  
−
 
 
|-
 
|-
−
| NOW
+
| FALSE
−
| Returns the current time
+
| Defines the logical value as FALSE.
 
|  
 
|  
−
 
 
|-
 
|-
−
| TIME
+
| IF
−
| Returns the time
+
| Specifies a logical test to be performed.
−
{| class="prettytable" cellpadding="10" border="1"
+
{| cellpadding="10" border="1" class="prettytable"
 +
|-
 
| '''''Parameter Name'''''  
 
| '''''Parameter Name'''''  
−
| '''''Parameter Description'''''  
+
| '''''Parameter Description'''''
−
 
+
 
|-
 
|-
−
| Minute.
+
| Then_value
−
| Minutes
+
| The result of the function if the logical test returns a TRUE.
 
|-
 
|-
−
| Second
+
| Otherwise_value
−
| Seconds
+
| The result of the function if the logical test returns FALSE.
 
|}
 
|}
  
 
|  
 
|  
−
 
 
|-
 
|-
−
| TODAY
+
| NOT
−
| Returns the current date
+
| Reverses the value of the argument.
 
|  
 
|  
−
 
 
|-
 
|-
−
| WEEKDAY
+
| OR
−
| Returns the day of week from a date.
+
| Returns TRUE if an argument is TRUE.  
−
{| class="prettytable" cellpadding="10" border="1"
+
|  
−
| '''''Parameter Name'''''
+
−
| '''''Parameter Description'''''
+
−
 
+
 
|-
 
|-
−
| Type
+
| TRUE
−
| The result type (from 1 to 3)
+
| Returns the logical value TRUE.
−
|}
+
−
 
+
 
|  
 
|  
−
 
 
|-
 
|-
−
| YEAR
+
| XOR
−
| Returns the year of a date
+
| Returns TRUE if a odd number of arguments are TRUE.
 
|  
 
|  
 
|}
 
|}
−
<br>
 
−
'''Text'''<br>
 
−
{| class="prettytable" cellpadding="10" border="1"
 
−
| '''''Function Name'''''
 
−
| '''''Description'''''
 
−
| '''''Example '''''
 
  
 +
<br> '''Information'''<br>
 +
 +
{| cellpadding="10" border="1" class="prettytable"
 +
|-
 +
| '''''Function Name'''''
 +
| '''''Description'''''
 +
| '''''Example'''''
 +
|-
 +
| CHOOSE
 +
| Selects a value from a list of up to 30 value arguments.
 +
{| cellpadding="10" border="1" class="prettytable"
 
|-
 
|-
−
| EXACT
 
−
| Reports if two text values are exactly equal using a case-sensitive comparison.
 
−
{| class="prettytable" cellpadding="10" border="1"
 
 
| '''''Parameter Name'''''  
 
| '''''Parameter Name'''''  
−
| '''''Parameter Description'''''  
+
| '''''Parameter Description'''''
−
 
+
 
|-
 
|-
−
| Text2
+
| value
−
| The second text.
+
| Value 1, value 2,... The list of arguments from which a value is chosen.
 
|}
 
|}
  
 
|  
 
|  
−
 
 
|-
 
|-
−
| FIND
+
| COUNT
−
| Returns the starting position of a given text.
+
| Counts how many numbers are in the list of arguments.  
−
{| class="prettytable" cellpadding="10" border="1"
+
|  
−
| '''''Parameter Name'''''
+
−
| '''''Parameter Description'''''
+
−
 
+
 
|-
 
|-
−
| Text
+
| COUNTA
−
| The text source.
+
| Counts how many values are in the list of arguments.  
 +
|
 
|-
 
|-
−
| Index
+
| COUNTBLANK
−
| The index to start from.
+
| Counts the blank cells in a specified range.  
−
|}
+
−
 
+
 
|  
 
|  
−
 
 
|-
 
|-
−
| LEFT
+
| HASCHANGED
−
| Returns a selected number of text characters from the left.
+
| Checks whether the columns specified by the given names has changed. Place a column name between double quotes.
−
{| class="prettytable" cellpadding="10" border="1"
+
|  
−
| '''''Parameter Name'''''
+
−
| '''''Parameter Description'''''
+
−
 
+
 
|-
 
|-
−
| Length
+
| ISBLANK
−
| The length.
+
| Returns TRUE if value refers to an empty cell.  
−
|}
+
−
 
+
 
|  
 
|  
−
 
 
|-
 
|-
−
| LEN
+
| ISERR
−
| Returns the lengh in characters of the given value.
+
| Returns TRUE if the value is an error value not equal to #N/A.  
 
|  
 
|  
−
 
 
|-
 
|-
−
| LOWER
+
| ISERROR
−
| Returns the given text in lower case.
+
| Returns TRUE if the value is an error value.  
 
|  
 
|  
−
 
 
|-
 
|-
−
| MID
+
| ISEVEN
−
| Returns extracted text, given an original text, starting position, and length.
+
| Returns TRUE if value is an even integer.  
−
{| class="prettytable" cellpadding="10" border="1"
+
|  
−
| '''''Parameter Name'''''
+
−
| '''''Parameter Description'''''
+
−
 
+
 
|-
 
|-
−
| Start
+
| ISLOGICAL
−
| The start index.
+
| Returns TRUE if the value carries a logical number format.  
 +
|
 
|-
 
|-
−
| Length
+
| ISNA
−
| The length.
+
| Returns TRUE if value equals #N/A.  
−
|}
+
−
 
+
 
|  
 
|  
−
 
 
|-
 
|-
−
| REPLACE
+
| ISNONTEXT
−
| Returns text where an old text is substituted with a new text within the start index and length range.
+
| Returns TRUE if the value is not text.  
−
{| class="prettytable" cellpadding="10" border="1"
+
|  
−
| '''''Parameter Name'''''
+
−
| '''''Parameter Description'''''
+
−
 
+
 
|-
 
|-
−
| Start
+
| ISNUMBER
−
| The start index.
+
| Returns TRUE if value is a number.  
 +
|
 
|-
 
|-
−
| Len
+
| ISODD
−
| The length.
+
| Returns TRUE if value is an odd integer.  
 +
|
 
|-
 
|-
−
| New
+
| ISREF
−
| The new text.
+
| Returns TRUE if value is a reference.
 +
|
 +
|-
 +
| ISTEXT
 +
| Returns TRUE if value is text.  
 +
|
 +
|-
 +
| NA
 +
| Not available. Returns the error value #N/A.
 +
|
 
|}
 
|}
  
−
|
+
<br> '''User-Defined'''<br>
  
 +
{| cellpadding="10" border="1" class="prettytable"
 
|-
 
|-
−
| REPT
+
| '''''Function Name'''''  
−
| Returns text repeated Count times..
+
| '''''Description'''''  
−
{| class="prettytable" cellpadding="10" border="1"
+
| '''''Example'''''
−
| '''''Parameter Name'''''  
+
−
| '''''Parameter Description'''''  
+
−
 
+
 
|-
 
|-
−
| Count
+
| NULL
−
| The count.
+
| Returns a NULL-Value
 +
|  
 
|}
 
|}
  
−
|
+
<br> '''Text'''<br>
  
 +
{| cellpadding="10" border="1" class="prettytable"
 +
|-
 +
| '''''Function Name'''''
 +
| '''''Description'''''
 +
| '''''Example'''''
 +
|-
 +
| EXACT
 +
| Specifies whether two texts are identical.
 +
{| cellpadding="10" border="1" class="prettytable"
 
|-
 
|-
−
| RIGHT
 
−
| Returns a selected number of text characters from the right.
 
−
{| class="prettytable" cellpadding="10" border="1"
 
 
| '''''Parameter Name'''''  
 
| '''''Parameter Name'''''  
−
| '''''Parameter Description'''''  
+
| '''''Parameter Description'''''
−
 
+
 
|-
 
|-
−
| Count
+
| text_2
−
| The count.
+
| The second text for comparing texts.
 
|}
 
|}
  
 
|  
 
|  
−
 
 
|-
 
|-
−
| SUBSTITUTE
+
| FIND
−
| Returns text where an old text is substituted with a new text. If which is specified, only this occurrance will be replaced otherwise every occurrance will be replaced.
+
| Looks for a string of text within another (case sensitive)
−
{| class="prettytable" cellpadding="10" border="1"
+
{| cellpadding="10" border="1" class="prettytable"
 +
|-
 
| '''''Parameter Name'''''  
 
| '''''Parameter Name'''''  
−
| '''''Parameter Description'''''  
+
| '''''Parameter Description'''''
 +
|-
 +
| text
 +
| The text in which a search is to be made.
 +
|-
 +
| position
 +
| The position in the text from which the search starts.
 +
|}
  
 +
|
 
|-
 
|-
−
| Old
+
| LEFT
−
| The old text.
+
| Returns the first character or characters of a text.  
 +
{| cellpadding="10" border="1" class="prettytable"
 
|-
 
|-
−
| New
+
| '''''Parameter Name'''''
−
| The new text.
+
| '''''Parameter Description'''''
 
|-
 
|-
−
| Which
+
| number
−
| The occurrance number.
+
| The number of characters for the start text.
 
|}
 
|}
  
 
|  
 
|  
−
 
 
|-
 
|-
−
| T
+
| LEN
−
| Returns the given text value or a zero lenght string for non text type.
+
| Calculates length of a text string.  
 
|  
 
|  
−
 
 
|-
 
|-
−
| TEXT
+
| LOWER
−
| Returns the given value as text.
+
| Converts text to lowercase.  
 
|  
 
|  
−
 
 
|-
 
|-
−
| LOWER
+
| MID
−
| Returns the given text free of leading and trailing spaces. Internal multiple spaces are replaced by one.
+
| Returns a partial text string of a text.  
−
|
+
{| cellpadding="10" border="1" class="prettytable"
−
 
+
−
|-
+
−
| UPPER
+
−
| Returns the given text in upper case.
+
−
|  
+
−
 
+
 
|-
 
|-
−
| 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.
 
−
{| class="prettytable" cellpadding="10" border="1"
 
 
| '''''Parameter Name'''''  
 
| '''''Parameter Name'''''  
−
| '''''Parameter Description'''''  
+
| '''''Parameter Description'''''
−
 
+
 
|-
 
|-
−
| Encoding
+
| start
−
| The Encoding to be used.
+
| The position from which the part word is to be determined.
 +
|-
 +
| number
 +
| The number of characters for the text.
 
|}
 
|}
  
 
|  
 
|  
−
|}
 
−
<br>
 
−
'''Mathematical'''<br>
 
−
{| class="prettytable" cellpadding="10" border="1"
 
−
| '''''Function Name'''''
 
−
| '''''Description'''''
 
−
| '''''Example '''''
 
−
 
 
|-
 
|-
−
| ABS
+
| REPLACE
−
| Returns the absolute (nonnegative) of the value.
+
| Replaces characters within a text string with a different text string.  
−
|  
+
{| cellpadding="10" border="1" class="prettytable"
−
 
+
 
|-
 
|-
−
| AVERAGE
+
| '''''Parameter Name'''''
−
| Average a list of numeric values.
+
| '''''Parameter Description'''''
−
|
+
−
 
+
 
|-
 
|-
−
| Even
+
| position
−
| Returns the rounding a number up to the nearest even integer.
+
| The character position from which text is to be replaced.
−
|
+
−
 
+
 
|-
 
|-
−
| MAX
+
| length
−
| Returns the maximum from a set of numbers.
+
| The number of characters to be replaced.
 +
|-
 +
| new text
 +
| The text to be inserted.
 +
|}
 +
 
 
|  
 
|  
−
 
 
|-
 
|-
−
| MIN
+
| REPT
−
| Returns the minimum from a set of numbers.
+
| Repeats text a given number of times.  
−
|  
+
{| cellpadding="10" border="1" class="prettytable"
−
 
+
 
|-
 
|-
−
| MOD
 
−
| Returns the remainder when one number is divided by another number.
 
−
{| class="prettytable" cellpadding="10" border="1"
 
 
| '''''Parameter Name'''''  
 
| '''''Parameter Name'''''  
−
| '''''Parameter Description'''''  
+
| '''''Parameter Description'''''
 +
|-
 +
| number
 +
| The number of times the text is to be repeated.
 +
|}
  
 +
|
 
|-
 
|-
−
| Divisor
+
| RIGHT
−
| A divisor value.
+
| Returns the last character or characters of a text.  
−
|-
+
{| cellpadding="10" border="1" class="prettytable"
−
|-
+
−
|-
+
 
|-
 
|-
 +
| '''''Parameter Name'''''
 +
| '''''Parameter Description'''''
 
|-
 
|-
 +
| number
 +
| The number of characters for the end text.
 +
|}
 +
 +
|
 
|-
 
|-
 +
| SUBSTITUTE
 +
| Substitutes new text for old text in a string.
 +
{| cellpadding="10" border="1" class="prettytable"
 
|-
 
|-
 +
| '''''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.
 
|}
 
|}
  
 
|  
 
|  
−
 
 
|-
 
|-
−
| ODD
+
| T
−
| Returns the rounding a number up to the nearest odd integer.
+
| Converts a value into text.  
 
|  
 
|  
−
 
 
|-
 
|-
−
| SUM
+
| TEXT
−
| Sum a list of numeric values.
+
| Converts a number to text according to a given format.  
 
|  
 
|  
−
|}
 
−
<br>
 
−
'''Logical'''<br>
 
−
{| class="prettytable" cellpadding="10" border="1"
 
−
| '''''Function Name'''''
 
−
| '''''Description'''''
 
−
| '''''Example '''''
 
−
 
 
|-
 
|-
−
| AND
+
| TRIM
−
| Logical AND
+
| Removes extra spaces from text.
 
|  
 
|  
−
 
 
|-
 
|-
−
| FALSE
+
| UNICHAR
−
| Logical FALSE
+
| Converts a code number into a Unicode character or letter.
 
|  
 
|  
−
 
 
|-
 
|-
−
| IF
+
| UNICODE
−
| Conditional execution
+
| Returns the numeric code for the first Unicode character in a text string.
−
{| class="prettytable" cellpadding="10" border="1"
+
|
 +
|-
 +
| 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.
 +
{| cellpadding="10" border="1" class="prettytable"
 +
|-
 
| '''''Parameter Name'''''  
 
| '''''Parameter Name'''''  
−
| '''''Parameter Description'''''  
+
| '''''Parameter Description'''''
−
 
+
 
|-
 
|-
−
| A term that is evaluated if the condition evaluates to true.
+
| Encoding
−
| True-Term
+
| The Encoding to be used.
−
|-
+
−
| A term that is evaluated if the condition evaluates to false.
+
−
| False-Term
+
 
|}
 
|}
  
 
|  
 
|  
 +
|}
  
−
|-
+
CONCAT or CONCATENATE functions not supported, but you can use [Textfieldname]&" "&[Textfieldname] to concatenate text fields.
−
| AND
+
−
| Logical AND
+
−
|
+
  
−
|-
+
<br> '''Rounding'''<br>
−
| OR
+
−
| Logical OR
+
−
|
+
  
 +
{| cellpadding="10" border="1" class="prettytable"
 
|-
 
|-
−
| TRUE
+
| '''''Function Name'''''
−
| Logical TRUE
+
| '''''Description'''''
−
|  
+
| '''''Example'''''
−
 
+
 
|-
 
|-
−
| XOR
+
| INT
−
| Logical XOR
+
| Rounds a number down to the nearest integer.
 
|  
 
|  
 
|}
 
|}
 +
 
<br>
 
<br>
−
''Code to get the function form the libformula jar file
+
[[Category:Database]] [[Category:Extensions]]
−
<code>[java]
+
−
import java.util.Locale;
+
−
import org.jfree.formula.DefaultFormulaContext;
+
−
 
+
−
/*
+
−
* To change this template, choose Tools | Templates
+
−
* and open the template in the editor.
+
−
*/
+
−
import org.jfree.formula.function.FunctionCategory;
+
−
import org.jfree.formula.function.FunctionDescription;
+
−
import org.jfree.formula.function.FunctionRegistry;
+
−
 
+
−
/**
+
−
*
+
−
* @author Ocke Janssen
+
−
*/
+
−
public class NewMain {
+
−
 
+
−
    /**
+
−
    * @param args the command line arguments
+
−
    */
+
−
    public static void main(String[] args) {
+
−
        DefaultFormulaContext obj = new DefaultFormulaContext();
+
−
        FunctionRegistry func = obj.getFunctionRegistry();
+
−
        FunctionCategory[] categories = func.getCategories();
+
−
        for ( int i = 0 ; i < categories.length;++i)
+
−
        {
+
−
            String display = categories[i].getDisplayName(Locale.ENGLISH);
+
−
            System.out.println( "'''" + display +"'''<br>");
+
−
            System.out.println( "{| class=\"prettytable\" cellpadding=\"10\" border=\"1\"");
+
−
            System.out.println( "| '''''Function Name'''''");
+
−
            System.out.println( "| '''''Description''''' ");
+
−
            System.out.println( "| '''''Example '''''");
+
−
           
+
−
            String[] functions = func.getFunctionNamesByCategory(categories[i]);
+
−
            for(int j= 0; j < functions.length;++j)
+
−
            {
+
−
                FunctionDescription desc = func.getMetaData(functions[j]);
+
−
                System.out.println( "");
+
−
                System.out.println( "|-");
+
−
                System.out.println("| "+ desc.getDisplayName(Locale.ENGLISH));
+
−
                System.out.println("| "+ desc.getDescription(Locale.ENGLISH));
+
−
                int count = desc.getParameterCount();
+
−
                if ( count > 1 ){
+
−
                    System.out.println( "{| class=\"prettytable\" cellpadding=\"10\" border=\"1\"");
+
−
                    System.out.println( "| '''''Parameter Name''''' ");
+
−
                    System.out.println( "| '''''Parameter Description''''' ");
+
−
                    System.out.println( "");
+
−
                }
+
−
                for(int k= 1;k < count;++k){
+
−
                    try{
+
−
                        System.out.println( "|-");
+
−
                        System.out.println("| " + desc.getParameterDisplayName(k,Locale.ENGLISH) );
+
−
                        System.out.println("| " + desc.getParameterDescription(k,Locale.ENGLISH) );
+
−
                    } catch(Exception e){
+
−
                    }
+
−
                }
+
−
                if ( count > 1 ){
+
−
                    System.out.println("|}");
+
−
                    System.out.println("");
+
−
                }                   
+
−
               
+
−
                System.out.println("| ");
+
−
            }
+
−
            System.out.println("|}");
+
−
            System.out.println("<br>");
+
−
        }
+
−
    }
+
−
 
+
−
}
+
−
</code>
+

Latest revision as of 11:50, 11 May 2010

There are documents also in DE - FR - ES -PT


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. Place a column name between double quotes.
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.

CONCAT or CONCATENATE functions not supported, but you can use [Textfieldname]&" "&[Textfieldname] to concatenate text fields.


Rounding

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


Retrieved from "https://wiki.openoffice.org/w/index.php?title=SUN_Report_Builder/Functions&oldid=167078"
Personal tools
In other languages