Difference between revisions of "SUN Report Builder/Functions"
From Apache OpenOffice Wiki
DrewJensen (Talk | contribs) (→1st draft) |
m |
||
(16 intermediate revisions by 10 users not shown) | |||
Line 1: | Line 1: | ||
− | + | [[fr:FR/Documentation/Base/Rapports/Fonctions]] | |
+ | {{Database/Lang|Base/Reports/Functions}} | ||
+ | '''Date/Time'''<br> | ||
− | + | {| cellpadding="10" border="1" class="prettytable" | |
+ | |- | ||
+ | | '''''Function Name''''' | ||
+ | | '''''Description''''' | ||
+ | | '''''Example''''' | ||
+ | |- | ||
+ | | DATE | ||
+ | | 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. | ||
+ | |} | ||
+ | |||
+ | | | ||
+ | |- | ||
+ | | 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. | ||
+ | {| cellpadding="10" border="1" class="prettytable" | ||
+ | |- | ||
+ | | '''''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. | ||
+ | {| cellpadding="10" border="1" class="prettytable" | ||
+ | |- | ||
+ | | '''''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). | ||
+ | {| 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) | ||
+ | |} | ||
+ | |||
+ | | | ||
+ | |- | ||
+ | | YEAR | ||
+ | | Returns the year of a date value as an integer. | ||
+ | | | ||
+ | |} | ||
+ | |||
+ | <br> '''Mathematical'''<br> | ||
+ | |||
+ | {| cellpadding="10" border="1" class="prettytable" | ||
+ | |- | ||
+ | | '''''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. | ||
+ | {| cellpadding="10" border="1" class="prettytable" | ||
+ | |- | ||
+ | | '''''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. | ||
+ | | | ||
+ | |} | ||
+ | |||
+ | <br> '''Logical'''<br> | ||
+ | |||
+ | {| cellpadding="10" border="1" class="prettytable" | ||
+ | |- | ||
+ | | '''''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. | ||
+ | {| cellpadding="10" border="1" class="prettytable" | ||
+ | |- | ||
+ | | '''''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. | ||
+ | | | ||
+ | |} | ||
+ | |||
+ | <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" | ||
+ | |- | ||
+ | | '''''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. | ||
+ | | | ||
+ | |} | ||
+ | |||
+ | <br> '''User-Defined'''<br> | ||
+ | |||
+ | {| cellpadding="10" border="1" class="prettytable" | ||
+ | |- | ||
+ | | '''''Function Name''''' | ||
+ | | '''''Description''''' | ||
+ | | '''''Example''''' | ||
+ | |- | ||
+ | | NULL | ||
+ | | 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" | ||
+ | |- | ||
+ | | '''''Parameter Name''''' | ||
+ | | '''''Parameter Description''''' | ||
+ | |- | ||
+ | | text_2 | ||
+ | | The second text for comparing texts. | ||
+ | |} | ||
+ | |||
+ | | | ||
+ | |- | ||
+ | | FIND | ||
+ | | Looks for a string of text within another (case sensitive) | ||
+ | {| cellpadding="10" border="1" class="prettytable" | ||
+ | |- | ||
+ | | '''''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. | ||
+ | {| cellpadding="10" border="1" class="prettytable" | ||
+ | |- | ||
+ | | '''''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. | ||
+ | {| cellpadding="10" border="1" class="prettytable" | ||
+ | |- | ||
+ | | '''''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. | ||
+ | {| cellpadding="10" border="1" class="prettytable" | ||
+ | |- | ||
+ | | '''''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. | ||
+ | {| cellpadding="10" border="1" class="prettytable" | ||
+ | |- | ||
+ | | '''''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. | ||
+ | {| 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. | ||
+ | |} | ||
+ | |||
+ | | | ||
+ | |- | ||
+ | | 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. | ||
+ | {| cellpadding="10" border="1" class="prettytable" | ||
+ | |- | ||
+ | | '''''Parameter Name''''' | ||
+ | | '''''Parameter Description''''' | ||
+ | |- | ||
+ | | Encoding | ||
+ | | The Encoding to be used. | ||
+ | |} | ||
+ | |||
+ | | | ||
+ | |} | ||
+ | |||
+ | CONCAT or CONCATENATE functions are not supported, but you can use [Textfieldname]&" "&[Textfieldname] to concatenate text fields. | ||
+ | |||
+ | <br> '''Rounding'''<br> | ||
+ | |||
+ | {| cellpadding="10" border="1" class="prettytable" | ||
+ | |- | ||
+ | | '''''Function Name''''' | ||
+ | | '''''Description''''' | ||
+ | | '''''Example''''' | ||
+ | |- | ||
+ | | INT | ||
+ | | Rounds a number down to the nearest integer. | ||
+ | | | ||
+ | |} | ||
+ | |||
+ | <br> | ||
+ | [[Category:Database]] [[Category:Extensions]] |
Latest revision as of 13:34, 21 May 2024
Date/Time
Function Name | Description | Example | ||||||
DATE | Provides an internal number for the date given.
|
|||||||
DATEDIF | Returns the number of years, months, or days between two dates.
|
|||||||
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.
|
|||||||
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.
|
|||||||
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).
|
|||||||
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.
|
|||||
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.
|
|||||||
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.
|
|||||
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.
|
|||||||||
FIND | Looks for a string of text within another (case sensitive)
|
|||||||||
LEFT | Returns the first character or characters of a text.
|
|||||||||
LEN | Calculates length of a text string. | |||||||||
LOWER | Converts text to lowercase. | |||||||||
MID | Returns a partial text string of a text.
|
|||||||||
REPLACE | Replaces characters within a text string with a different text string.
|
|||||||||
REPT | Repeats text a given number of times.
|
|||||||||
RIGHT | Returns the last character or characters of a text.
|
|||||||||
SUBSTITUTE | Substitutes new text for old text in a string.
|
|||||||||
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.
|
CONCAT or CONCATENATE functions are 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. |