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

# Syntax description

Formulas start with a "=" or an formula prefix (any letter and number followed by a colon.) (not interpreted yet, 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.*

=1

report:1

References to fields are expressed using square brackets.

=[yourfield]

If you're using MDX fields, they should be quoted inside the outer square brackets.

=["[Measures].[Total]"]

Possible operators are:

Basic computation: +, -, *, /

Percentage: %

Power: ^

String-concatenation: &

Comparisons: =, <>, <, <=, >, >=

Formulas can have braces.

=(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 are not supported. A formula can only work on the current values of the current datarow. There is no way to access previous or next columns. (These are the same limitations as with all expressions, so this is nothing new.)

The functions we support are:

Category Rounding

INT

Category Information

CHOOSE, HASCHANGED, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, NA

Category Text

EXACT, FIND, LEFT, LEN, LOWER, MID, REPLACE, REPT, RIGHT, SUBSTITUTE, T, TEXT, TRIM, UPPER, URLENCODE

Category Mathematical

ABS, AVERAGE, EVEN, MAX, MIN, MOD, ODD, SUM

Category Date/Time

DATE, DATEDIF, DATEVALUE, DAY, HOUR, MONTH, NOW, TIME, TODAY, WEEKDAY, YEAR

Category Logical

AND, FALSE, IF, NOT, OR, TRUE, XOR

The functions are defined inside the OpenFormula standard and should behave as known from Excel or OpenOffice.

Additionally, the report engine also provides the "ISEXPORTTYPE" function, which takes a single parameter specifiying the expected export type.

=ISEXPORTTYPE("table");

returns true on any table-export.

=ISEXPORTTYPE("table/html");

returns true on all HTML-exports.