Difference between revisions of "SUN Report Builder/Functions"

From Apache OpenOffice Wiki
Jump to: navigation, search
Line 6: Line 6:
 
| '''''Function Name'''''
 
| '''''Function Name'''''
 
| '''''Description'''''  
 
| '''''Description'''''  
| '''''Parameters'''''
 
 
| '''''Example '''''
 
| '''''Example '''''
  
Line 19: Line 18:
 
| '''''Function Name'''''
 
| '''''Function Name'''''
 
| '''''Description'''''  
 
| '''''Description'''''  
| '''''Parameters'''''
 
 
| '''''Example '''''
 
| '''''Example '''''
  
Line 97: Line 95:
 
| '''''Function Name'''''
 
| '''''Function Name'''''
 
| '''''Description'''''  
 
| '''''Description'''''  
| '''''Parameters'''''
 
 
| '''''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'''''  
| '''''Parameters'''''
 
 
| '''''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'''''  
| '''''Parameters'''''
 
 
| '''''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'''''  
| '''''Parameters'''''
 
 
| '''''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
Parameter Name Parameter Description
Month Month
Day Day
DATEDIF Returns the number of years, months, or days between two dates.
Parameter Name Parameter Description
Date2 Second date.
Format Format code.
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
Parameter Name Parameter Description
Minute. Minutes
Second Seconds
TODAY Returns the current date
WEEKDAY Returns the day of week from a date.
Parameter Name Parameter Description
Type The result type (from 1 to 3)
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.
Parameter Name Parameter Description
Text2 The second text.
FIND Returns the starting position of a given text.
Parameter Name Parameter Description
Text The text source.
Index The index to start from.
LEFT Returns a selected number of text characters from the left.
Parameter Name Parameter Description
Length The length.
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.
Parameter Name Parameter Description
Start The start index.
Length The length.
REPLACE Returns text where an old text is substituted with a new text within the start index and length range.
Parameter Name Parameter Description
Start The start index.
Len The length.
New The new text.
REPT Returns text repeated Count times..
Parameter Name Parameter Description
Count The count.
RIGHT Returns a selected number of text characters from the right.
Parameter Name Parameter Description
Count The count.
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.
Parameter Name Parameter Description
Old The old text.
New The new text.
Which The occurrance number.
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.
Parameter Name Parameter Description
Encoding The Encoding to be used.


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.
Parameter Name Parameter Description
Divisor A divisor value.
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
Parameter Name Parameter Description
A term that is evaluated if the condition evaluates to true. True-Term
A term that is evaluated if the condition evaluates to false. False-Term
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("
"); } }

}

Personal tools