Hitachi Vantara Pentaho Community Wiki
Skip to end of metadata
Go to start of metadata

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.

  • No labels