Formula Expressions

Owned by Former user (Deleted)
Last updated: Nov 12, 2013 by Former user (Deleted)Version comment
4 min read

Formula Syntax Description

Formulas start with a "=" or a formula prefix (any letter and number followed by a colon; the prefix is not interpreted yet, but will be used later to separate different formula-namespaces).

IMPORTANT: this is not the case for the formula defined in Pentaho Metadata nor Pentaho Data Integration.  The "=" prefix should be omitted in that case.

Therefore the following formulas are equivalent:

=1

report:1

References to external data-fields are expressed using square brackets.

=[yourfield]

References to external data-fields with parentheses in their titles are expressed using square brackets with quotation marks included inside them.

=["yourfield (containing parentheses)"]

Possible operators are:

  • Basic computation: +, -, *, /
  • Percentage: %
  • Power: ^
  • String-concatenation: &
  • Comparisons: =, <>, <, <=, >, >=
  • Formulas can have braces.

Example:

=(1+1)*2 / 3

results in 1.3333333

=10%

results in 0.1

String-constants are expressed using double-quotes.

="Your text here"

Strings can be concatenated using the & operator.

="Your text here " & "some more text"

The Range-Operators known from Spreadsheet applications are not supported, as they have no meaning in the reporting. A formula can only work on the current values of the current datarow. There is no way to access previous or next rows of data. (These are the same limitations as with all expressions, so this is nothing new.)

Date/Time Functions

DATE - Provides an internal number for the given date.
DATEDIF - Returns the number of years, months, or days between two dates.
TIME - Returns the current time.
DATEVALUE - Returns date serial number from given text.
NOW - Return the serial number of the current date and time. This returns the current day and time serial number, using the * current locale.
TODAY - Returns the current date, (yyyy-mm-dd).
DAY - Returns the day of the month for a date (1 through 31)
MONTH - Returns the month of a date
YEAR - Returns the year of a date
WEEKDAY - Returns the day of week from a date.
HOUR - Returns the hour (0 through 23) from a time.

Logical functions

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 an argument.
OR - Returns TRUE if an arguments are TRUE.
TRUE - Defines the logical value as TRUE.
XOR - Returns a value of TRUE only if just one of its operands is TRUE. In contrast, an inclusive OR operator returns a value of TRUE if either or both of its operands are TRUE.

Rounding functions

INT - Returns a number down to the nearest integer

Mathematical functions

SUM - Sum a list of numeric values.
ABS - Returns the absolute (nonnegative) of the value.
AVERAGE - Average a list of numeric values.
ODD - Returns the rounding a number up to the nearest odd integer.
EVEN - Returns the rounding a number up to the nearest even integer.
MOD - Returns the remainder when one number is divided by another number.
MAX - Returns the maximum from a set of numbers.
MIN - Returns the minimum from a set of numbers.

Text functions

LEN - Returns the length in characters of the given value.
LOWER - Returns the given text in lower case.
UPPER - Returns the given text in upper case.
TRIM - Returns the given text free of leading and trailing spaces. Internal multiple spaces are replaced by one.
TEXT - Returns the given value as text.
T - Returns the given text value or a zero lenght string for non text type.
FIND - Returns the starting position of a given text.
EXACT - Reports if two text values are exactly equal using a case-sensitive comparison.
REPT - Returns text repeated Count times..
MID - Returns extracted text, given an original text, starting position, and length.
LEFT - Returns a selected number of text characters from the left.
RIGHT - Returns a selected number of text characters from the right.
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.
REPLACE - Returns text where an old text is substituted with a new text within the start index and length range.
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.

Information functions

HASCHANGED - Checks, whether the columns specified by the given names has changed.
ISBLANK - Checks, whether the value is undefined (null).
NA - Returns the constant error NA.
ISNA - Tells if the parameter is of error type NA.
ISLOGICAL - Checks whatever the value is of type Logical.
ISTEXT - Checks whatever the value is of type Text.
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.
CHOOSE - Uses an index to return a value from a list of values.
ISREF - Checks whatever the value is a reference.
ISNUMBER - Checks whatever the value is of type Number.
ISODD - Checks whatever the value is an odd number.
ISEVEN - Checks whatever the value is an even number.
ISNONTEXT - Checks whatever the value is not of type Text.

Userdefined
NULL - Returns a NULL-Value

Formula Operators.

+ Add
- Subtract
* Multiply
/ Divide
= Equal
<> Not Equal
< Less Than
> Greater Than
<= Equal Less Than
>= Equal Greater Than
& Concatenate
^ Power

Prefix/Suffix Operators

% Percent (Suffix)
+ Positive (Prefix)
- Negative (Prefix)

Note: The formula syntax used in LibFormula is based on the OpenFormula standard. The full document is downloadable from Oasis: OASIS OpenFormula Specifcation