Difference between revisions of "SUN Report Builder/Functions"
From Apache OpenOffice Wiki
Line 6: | Line 6: | ||
| '''''Function Name''''' | | '''''Function Name''''' | ||
| '''''Description''''' | | '''''Description''''' | ||
− | |||
| '''''Example ''''' | | '''''Example ''''' | ||
Line 19: | Line 18: | ||
| '''''Function Name''''' | | '''''Function Name''''' | ||
| '''''Description''''' | | '''''Description''''' | ||
− | |||
| '''''Example ''''' | | '''''Example ''''' | ||
Line 97: | Line 95: | ||
| '''''Function Name''''' | | '''''Function Name''''' | ||
| '''''Description''''' | | '''''Description''''' | ||
− | |||
| '''''Example ''''' | | '''''Example ''''' | ||
Line 104: | Line 101: | ||
| Returns the number of days since 01.01.1900 | | Returns the number of days since 01.01.1900 | ||
{| class="prettytable" cellpadding="10" border="1" | {| class="prettytable" cellpadding="10" border="1" | ||
+ | | '''''Parameter Name''''' | ||
| '''''Parameter Description''''' | | '''''Parameter Description''''' | ||
+ | |||
+ | |- | ||
| Month | | Month | ||
+ | | Month | ||
+ | |- | ||
+ | | Day | ||
| Day | | Day | ||
|} | |} | ||
+ | |||
| | | | ||
Line 114: | Line 118: | ||
| Returns the number of years, months, or days between two dates. | | Returns the number of years, months, or days between two dates. | ||
{| class="prettytable" cellpadding="10" border="1" | {| class="prettytable" cellpadding="10" border="1" | ||
+ | | '''''Parameter Name''''' | ||
| '''''Parameter Description''''' | | '''''Parameter Description''''' | ||
+ | |||
+ | |- | ||
| Date2 | | Date2 | ||
+ | | Second date. | ||
+ | |- | ||
| Format | | Format | ||
+ | | Format code. | ||
|} | |} | ||
+ | |||
| | | | ||
Line 149: | Line 160: | ||
| Returns the time | | Returns the time | ||
{| class="prettytable" cellpadding="10" border="1" | {| class="prettytable" cellpadding="10" border="1" | ||
+ | | '''''Parameter Name''''' | ||
| '''''Parameter Description''''' | | '''''Parameter Description''''' | ||
+ | |||
+ | |- | ||
| Minute. | | Minute. | ||
+ | | Minutes | ||
+ | |- | ||
| Second | | Second | ||
+ | | Seconds | ||
|} | |} | ||
+ | |||
| | | | ||
Line 164: | Line 182: | ||
| Returns the day of week from a date. | | Returns the day of week from a date. | ||
{| class="prettytable" cellpadding="10" border="1" | {| class="prettytable" cellpadding="10" border="1" | ||
+ | | '''''Parameter Name''''' | ||
| '''''Parameter Description''''' | | '''''Parameter Description''''' | ||
+ | |||
+ | |- | ||
| Type | | Type | ||
+ | | The result type (from 1 to 3) | ||
|} | |} | ||
+ | |||
| | | | ||
Line 179: | Line 202: | ||
| '''''Function Name''''' | | '''''Function Name''''' | ||
| '''''Description''''' | | '''''Description''''' | ||
− | |||
| '''''Example ''''' | | '''''Example ''''' | ||
Line 186: | Line 208: | ||
| Reports if two text values are exactly equal using a case-sensitive comparison. | | Reports if two text values are exactly equal using a case-sensitive comparison. | ||
{| class="prettytable" cellpadding="10" border="1" | {| class="prettytable" cellpadding="10" border="1" | ||
+ | | '''''Parameter Name''''' | ||
| '''''Parameter Description''''' | | '''''Parameter Description''''' | ||
+ | |||
+ | |- | ||
| Text2 | | Text2 | ||
+ | | The second text. | ||
|} | |} | ||
+ | |||
| | | | ||
Line 195: | Line 222: | ||
| Returns the starting position of a given text. | | Returns the starting position of a given text. | ||
{| class="prettytable" cellpadding="10" border="1" | {| class="prettytable" cellpadding="10" border="1" | ||
+ | | '''''Parameter Name''''' | ||
| '''''Parameter Description''''' | | '''''Parameter Description''''' | ||
+ | |||
+ | |- | ||
| Text | | Text | ||
+ | | The text source. | ||
+ | |- | ||
| Index | | Index | ||
+ | | The index to start from. | ||
|} | |} | ||
+ | |||
| | | | ||
Line 205: | Line 239: | ||
| Returns a selected number of text characters from the left. | | Returns a selected number of text characters from the left. | ||
{| class="prettytable" cellpadding="10" border="1" | {| class="prettytable" cellpadding="10" border="1" | ||
+ | | '''''Parameter Name''''' | ||
| '''''Parameter Description''''' | | '''''Parameter Description''''' | ||
+ | |||
+ | |- | ||
| Length | | Length | ||
+ | | The length. | ||
|} | |} | ||
+ | |||
| | | | ||
Line 224: | Line 263: | ||
| Returns extracted text, given an original text, starting position, and length. | | Returns extracted text, given an original text, starting position, and length. | ||
{| class="prettytable" cellpadding="10" border="1" | {| class="prettytable" cellpadding="10" border="1" | ||
+ | | '''''Parameter Name''''' | ||
| '''''Parameter Description''''' | | '''''Parameter Description''''' | ||
+ | |||
+ | |- | ||
| Start | | Start | ||
+ | | The start index. | ||
+ | |- | ||
| Length | | Length | ||
+ | | The length. | ||
|} | |} | ||
+ | |||
| | | | ||
Line 234: | Line 280: | ||
| Returns text where an old text is substituted with a new text within the start index and length range. | | Returns text where an old text is substituted with a new text within the start index and length range. | ||
{| class="prettytable" cellpadding="10" border="1" | {| class="prettytable" cellpadding="10" border="1" | ||
+ | | '''''Parameter Name''''' | ||
| '''''Parameter Description''''' | | '''''Parameter Description''''' | ||
+ | |||
+ | |- | ||
| Start | | Start | ||
+ | | The start index. | ||
+ | |- | ||
| Len | | Len | ||
+ | | The length. | ||
+ | |- | ||
| New | | New | ||
+ | | The new text. | ||
|} | |} | ||
+ | |||
| | | | ||
Line 245: | Line 300: | ||
| Returns text repeated Count times.. | | Returns text repeated Count times.. | ||
{| class="prettytable" cellpadding="10" border="1" | {| class="prettytable" cellpadding="10" border="1" | ||
+ | | '''''Parameter Name''''' | ||
| '''''Parameter Description''''' | | '''''Parameter Description''''' | ||
+ | |||
+ | |- | ||
| Count | | Count | ||
+ | | The count. | ||
|} | |} | ||
+ | |||
| | | | ||
Line 254: | Line 314: | ||
| Returns a selected number of text characters from the right. | | Returns a selected number of text characters from the right. | ||
{| class="prettytable" cellpadding="10" border="1" | {| class="prettytable" cellpadding="10" border="1" | ||
+ | | '''''Parameter Name''''' | ||
| '''''Parameter Description''''' | | '''''Parameter Description''''' | ||
+ | |||
+ | |- | ||
| Count | | Count | ||
+ | | The count. | ||
|} | |} | ||
+ | |||
| | | | ||
Line 263: | Line 328: | ||
| 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. | | 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. | ||
{| class="prettytable" cellpadding="10" border="1" | {| class="prettytable" cellpadding="10" border="1" | ||
+ | | '''''Parameter Name''''' | ||
| '''''Parameter Description''''' | | '''''Parameter Description''''' | ||
+ | |||
+ | |- | ||
| Old | | Old | ||
+ | | The old text. | ||
+ | |- | ||
| New | | New | ||
+ | | The new text. | ||
+ | |- | ||
| Which | | Which | ||
+ | | The occurrance number. | ||
|} | |} | ||
+ | |||
| | | | ||
Line 294: | Line 368: | ||
| 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. | | 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" | {| class="prettytable" cellpadding="10" border="1" | ||
+ | | '''''Parameter Name''''' | ||
| '''''Parameter Description''''' | | '''''Parameter Description''''' | ||
+ | |||
+ | |- | ||
| Encoding | | Encoding | ||
+ | | The Encoding to be used. | ||
|} | |} | ||
+ | |||
| | | | ||
|} | |} | ||
Line 304: | Line 383: | ||
| '''''Function Name''''' | | '''''Function Name''''' | ||
| '''''Description''''' | | '''''Description''''' | ||
− | |||
| '''''Example ''''' | | '''''Example ''''' | ||
Line 336: | Line 414: | ||
| Returns the remainder when one number is divided by another number. | | Returns the remainder when one number is divided by another number. | ||
{| class="prettytable" cellpadding="10" border="1" | {| class="prettytable" cellpadding="10" border="1" | ||
+ | | '''''Parameter Name''''' | ||
| '''''Parameter Description''''' | | '''''Parameter Description''''' | ||
+ | |||
+ | |- | ||
| Divisor | | Divisor | ||
+ | | A divisor value. | ||
+ | |- | ||
+ | |- | ||
+ | |- | ||
+ | |- | ||
+ | |- | ||
+ | |- | ||
+ | |- | ||
+ | |- | ||
+ | |- | ||
+ | |- | ||
|} | |} | ||
+ | |||
| | | | ||
Line 356: | Line 449: | ||
| '''''Function Name''''' | | '''''Function Name''''' | ||
| '''''Description''''' | | '''''Description''''' | ||
− | |||
| '''''Example ''''' | | '''''Example ''''' | ||
Line 373: | Line 465: | ||
| Conditional execution | | Conditional execution | ||
{| class="prettytable" cellpadding="10" border="1" | {| class="prettytable" cellpadding="10" border="1" | ||
+ | | '''''Parameter Name''''' | ||
| '''''Parameter Description''''' | | '''''Parameter Description''''' | ||
+ | |||
+ | |- | ||
| A term that is evaluated if the condition evaluates to true. | | A term that is evaluated if the condition evaluates to true. | ||
+ | | True-Term | ||
+ | |- | ||
| A term that is evaluated if the condition evaluates to false. | | A term that is evaluated if the condition evaluates to false. | ||
+ | | False-Term | ||
|} | |} | ||
+ | |||
| | | | ||
Line 400: | Line 499: | ||
|} | |} | ||
<br> | <br> | ||
+ | ''Code to get the function form the libformula jar file | ||
+ | <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> |
Revision as of 09:50, 11 October 2007
The Sun Report Builder supplies the following functions:
Rounding
Function Name | Description | Example |
INT | Returns a number down to the nearest integer |
Information
Function Name | Description | Example |
CHOOSE | Uses an index to return a value from a list of values. | |
HASCHANGED | Checks, whether the columns specified by the given names has changed. | |
ISBLANK | Checks, whether the value is undefined (null). | |
ISERR | Tells if the parameter is of error type but returns false if the error is of type NA. | |
ISERROR | Tells if the parameter is of error type but returns false. | |
ISEVEN | Checks whatever the value is an even number. | |
ISLOGICAL | Checks whatever the value is of type Logical. | |
ISNA | Tells if the parameter is of error type NA. | |
ISNONTEXT | Checks whatever the value is not of type Text. | |
ISNUMBER | Checks whatever the value is of type Number. | |
ISODD | Checks whatever the value is an odd number. | |
ISREF | Checks whatever the value is a reference. | |
ISTEXT | Checks whatever the value is of type Text. | |
NA | Returns the constant error NA. |
Date/Time
Function Name | Description | Example | ||||||
DATE | Returns the number of days since 01.01.1900
|
|||||||
DATEDIF | Returns the number of years, months, or days between two dates.
|
|||||||
DATEVALUE | Returns date serial number from given text. | |||||||
DAY | Returns the day of a date | |||||||
HOUR | Returns the hour (0 through 23) from a time. | |||||||
MONTH | Returns the month of a date | |||||||
NOW | Returns the current time | |||||||
TIME | Returns the time
|
|||||||
TODAY | Returns the current date | |||||||
WEEKDAY | Returns the day of week from a date.
|
|||||||
YEAR | Returns the year of a date |
Text
Function Name | Description | Example | ||||||||
EXACT | Reports if two text values are exactly equal using a case-sensitive comparison.
|
|||||||||
FIND | Returns the starting position of a given text.
|
|||||||||
LEFT | Returns a selected number of text characters from the left.
|
|||||||||
LEN | Returns the lengh in characters of the given value. | |||||||||
LOWER | Returns the given text in lower case. | |||||||||
MID | Returns extracted text, given an original text, starting position, and length.
|
|||||||||
REPLACE | Returns text where an old text is substituted with a new text within the start index and length range.
|
|||||||||
REPT | Returns text repeated Count times..
|
|||||||||
RIGHT | Returns a selected number of text characters from the right.
|
|||||||||
SUBSTITUTE | 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.
|
|||||||||
T | Returns the given text value or a zero lenght string for non text type. | |||||||||
TEXT | Returns the given value as text. | |||||||||
LOWER | Returns the given text free of leading and trailing spaces. Internal multiple spaces are replaced by one. | |||||||||
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.
|
Mathematical
Function Name | Description | Example | ||||
ABS | Returns the absolute (nonnegative) of the value. | |||||
AVERAGE | Average a list of numeric values. | |||||
Even | Returns the rounding a number up to the nearest even integer. | |||||
MAX | Returns the maximum from a set of numbers. | |||||
MIN | Returns the minimum from a set of numbers. | |||||
MOD | Returns the remainder when one number is divided by another number.
|
|||||
ODD | Returns the rounding a number up to the nearest odd integer. | |||||
SUM | Sum a list of numeric values. |
Logical
Function Name | Description | Example | ||||||
AND | Logical AND | |||||||
FALSE | Logical FALSE | |||||||
IF | Conditional execution
|
|||||||
AND | Logical AND | |||||||
OR | Logical OR | |||||||
TRUE | Logical TRUE | |||||||
XOR | Logical XOR |
Code to get the function form the libformula jar file
[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 +"
"); 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("
"); } }
}