Formula - formal syntax specifications

Introduction

The table grid editor allows for column values to be calculated from other columns.  This functionality is based on the Apache Commons JEXL which is an open source Java Expression Language processor.

Additionally, the table grid editor adds 

  • Expression validation
    When an error is created in the configuration the administrator gets warned.

  • Loop detection and avoidance
    Formula's can reuse the values from other calculated columns. As this might create a loop (Column A depends on Column B and the way around), a configuration error is raised.



Check Formula - Examples for recipes to build formulas.

 

Literals

(Copied from JEXL Syntax)

Item

Description

Item

Description

Integer Literals

1 or more digits from 0 to 9, eg 42.

Float Literals

1 or more digits from 0 to 9, followed by a decimal point and then one or more digits from 0 to 9, optionally followed by f or F, eg 42.0 or 42.0f.

Long Literals

1 or more digits from 0 to 9 suffixed with l or L , eg 42l.

Double Literals

1 or more digits from 0 to 9, followed by a decimal point and then one or more digits from 0 to 9 suffixed with d or D , eg 42.0d.

Big Integer Literals

1 or more digits from 0 to 9 suffixed with b or B , eg 42B.

Big Decimal Literals

1 or more digits from 0 to 9, followed by a decimal point and then one or more digits from 0 to 9 suffixed with h or H (for Huge ala OGNL)) , eg 42.0H.

Natural literals - octal and hex support

Natural numbers (i.e. Integer, Long, BigInteger) can also be expressed as octal or hexadecimal using the same format as Java. i.e. prefix the number with 0 for octal, and prefix with 0x or 0X for hexadecimal. For example010 or 0x10.

Real literals - exponent support

Real numbers (i.e. Float, Double, BigDecimal) can also be expressed using standard Java exponent notation. i.e. suffix the number with e or E followed by the sign + or - followed by one or more decimal digits. For example42.0E-1D or 42.0E+3B.

String literals

Can start and end with either ' or " delimiters, e.g.

"Hello world"

and

'Hello world'

are equivalent.

The escape character is \ (backslash); it only escapes the string delimiter

Boolean literals

The literals true and false can be used, e.g.

val1 == true

Null literal

The null value is represented as in java using the literal null, e.g.

val1 == null

Functions

You can use functions on expressions such as

col.xyz.formula = math:round({price} * {discount} * {tax})

General functions

Function

Description

Function

Description

empty

Returns true if the expression following is either:

  1. null

  2. An empty string

  3. An array of length zero

  4. A collection of size zero

  5. An empty map

empty(var1)

size

Returns the information about the expression:

  1. Length of an array

  2. Size of a List

  3. Size of a Map

  4. Size of a Set

  5. Length of a string

size("Hello")

returns 5.

Math functions

The table grid editor expression language also exposes all math functions such as

abs(double a) 
          Returns the absolute value of a double value.

abs(float a) 
          Returns the absolute value of a float value.

abs(int a) 
          Returns the absolute value of an int value.

abs(long a) 
          Returns the absolute value of a long value.

acos(double a) 
          Returns the arc cosine of a value; the returned angle is in the range 0.0 through pi.

asin(double a) 
          Returns the arc sine of a value; the returned angle is in the range -pi/2 through pi/2.

atan(double a) 
          Returns the arc tangent of a value; the returned angle is in the range -pi/2 through pi/2.

atan2(double y, double x) 
          Returns the angle theta from the conversion of rectangular coordinates (x, y) to polar coordinates (r, theta).

cbrt(double a) 
          Returns the cube root of a double value.

ceil(double a) 
          Returns the smallest (closest to negative infinity) double value that is greater than or equal to the argument and is equal to a mathematical integer.

copySign(double magnitude, double sign) 
          Returns the first floating-point argument with the sign of the second floating-point argument.

copySign(float magnitude, float sign) 
          Returns the first floating-point argument with the sign of the second floating-point argument.

cos(double a) 
          Returns the trigonometric cosine of an angle.

cosh(double x) 
          Returns the hyperbolic cosine of a double value.

exp(double a) 
          Returns Euler's number e raised to the power of a double value.

expm1(double x) 
          Returns ex -1.

floor(double a) 
          Returns the largest (closest to positive infinity) double value that is less than or equal to the argument and is equal to a mathematical integer.

getExponent(double d) 
          Returns the unbiased exponent used in the representation of a double.

getExponent(float f) 
          Returns the unbiased exponent used in the representation of a float.

hypot(double x, double y) 
          Returns sqrt(x2 +y2) without intermediate overflow or underflow.

IEEEremainder(double f1, double f2) 
          Computes the remainder operation on two arguments as prescribed by the IEEE 754 standard.

log(double a) 
          Returns the natural logarithm (base e) of a double value.

log10(double a) 
          Returns the base 10 logarithm of a double value.

log1p(double x) 
          Returns the natural logarithm of the sum of the argument and 1.

max(double a, double b) 
          Returns the greater of two double values.

max(float a, float b) 
          Returns the greater of two float values.

max(int a, int b) 
          Returns the greater of two int values.

max(long a, long b) 
          Returns the greater of two long values.

min(double a, double b) 
          Returns the smaller of two double values.

min(float a, float b) 
          Returns the smaller of two float values.

min(int a, int b) 
          Returns the smaller of two int values.

min(long a, long b) 
          Returns the smaller of two long values.

nextAfter(double start, double direction) 
          Returns the floating-point number adjacent to the first argument in the direction of the second argument.

nextAfter(float start, double direction) 
          Returns the floating-point number adjacent to the first argument in the direction of the second argument.

nextUp(double d) 
          Returns the floating-point value adjacent to d in the direction of positive infinity.

nextUp(float f) 
          Returns the floating-point value adjacent to f in the direction of positive infinity.

pow(double a, double b) 
          Returns the value of the first argument raised to the power of the second argument.

random() 
          Returns a double value with a positive sign, greater than or equal to 0.0 and less than 1.0.

rint(double a) 
          Returns the double value that is closest in value to the argument and is equal to a mathematical integer.

round(double a) 
          Returns the closest long to the argument.

round(float a) 
          Returns the closest int to the argument.

scalb(double d, int scaleFactor) 
          Return d × 2scaleFactor rounded as if performed by a single correctly rounded floating-point multiply to a member of the double value set.

scalb(float f, int scaleFactor) 
          Return f × 2scaleFactor rounded as if performed by a single correctly rounded floating-point multiply to a member of the float value set.

signum(double d) 
          Returns the signum function of the argument; zero if the argument is zero, 1.0 if the argument is greater than zero, -1.0 if the argument is less than zero.

signum(float f) 
          Returns the signum function of the argument; zero if the argument is zero, 1.0f if the argument is greater than zero, -1.0f if the argument is less than zero.

sin(double a) 
          Returns the trigonometric sine of an angle.

sinh(double x) 
          Returns the hyperbolic sine of a double value.

sqrt(double a) 
          Returns the correctly rounded positive square root of a double value.

tan(double a) 
          Returns the trigonometric tangent of an angle.

tanh(double x) 
          Returns the hyperbolic tangent of a double value.

toDegrees(double angrad) 
          Converts an angle measured in radians to an approximately equivalent angle measured in degrees.

toRadians(double angdeg) 
          Converts an angle measured in degrees to an approximately equivalent angle measured in radians.

ulp(double d) 
          Returns the size of an ulp of the argument.

ulp(float f) 
          Returns the size of an ulp of the argument.

 

String functions

These are the string functions as provided by the java.lang.String class.

  • When the containing column is of type 'string', the expression value will be truncated to 254 chars.

  • When applying a string function on a variable, you need to use the toString function to ensure that the variable is cast to a string.
    A variable can contain a number.
    For instance {product.name} .toString().toUpperCase() is required as {product.name} could resolve to a number

charAt(int index) 
          Returns the char value at the specified index.

 

compareTo(String anotherString) 
          Compares two strings lexicographically.

compareToIgnoreCase(String str) 
          Compares two strings lexicographically, ignoring case differences.

concat(String str) 
          Concatenates the specified string to the end of this string.

contains(CharSequence s) 
          Returns true if and only if this string contains the specified sequence of char values.

contentEquals(CharSequence cs) 
          Compares this string to the specified CharSequence.

contentEquals(StringBuffer sb) 
          Compares this string to the specified StringBuffer.

copyValueOf(char[] data) 
          Returns a String that represents the character sequence in the array specified.

copyValueOf(char[] data, int offset, int count) 
          Returns a String that represents the character sequence in the array specified.

endsWith(String suffix) 
          Tests if this string ends with the specified suffix.

equals(Object anObject) 
          Compares this string to the specified object.

equalsIgnoreCase(String anotherString) 
          Compares this String to another String, ignoring case considerations.

format(Locale l, String format, Object... args) 
          Returns a formatted string using the specified locale, format string, and arguments.

format(String format, Object... args) 
          Returns a formatted string using the specified format string and arguments.

getBytes() 
          Encodes this String into a sequence of bytes using the platform's default charset, storing the result into a new byte array.

getBytes(Charset charset) 
          Encodes this String into a sequence of bytes using the given charset, storing the result into a new byte array.

getBytes(String charsetName) 
          Encodes this String into a sequence of bytes using the named charset, storing the result into a new byte array.

getChars(int srcBegin, int srcEnd, char[] dst, int dstBegin) 
          Copies characters from this string into the destination character array.

hashCode() 
          Returns a hash code for this string.

indexOf(int ch) 
          Returns the index within this string of the first occurrence of the specified character.

indexOf(int ch, int fromIndex) 
          Returns the index within this string of the first occurrence of the specified character, starting the search at the specified index.

indexOf(String str) 
          Returns the index within this string of the first occurrence of the specified substring.

indexOf(String str, int fromIndex) 
          Returns the index within this string of the first occurrence of the specified substring, starting at the specified index.

intern() 
          Returns a canonical representation for the string object.

isEmpty() 
          Returns true if, and only if, length() is 0.

lastIndexOf(int ch) 
          Returns the index within this string of the last occurrence of the specified character.

lastIndexOf(int ch, int fromIndex) 
          Returns the index within this string of the last occurrence of the specified character, searching backward starting at the specified index.

lastIndexOf(String str) 
          Returns the index within this string of the rightmost occurrence of the specified substring.

lastIndexOf(String str, int fromIndex) 
          Returns the index within this string of the last occurrence of the specified substring, searching backward starting at the specified index.

length() 
          Returns the length of this string.

matches(String regex) 
          Tells whether or not this string matches the given regular expression.

offsetByCodePoints(int index, int codePointOffset) 
          Returns the index within this String that is offset from the given index by codePointOffset code points.

regionMatches(boolean ignoreCase, int toffset, String other, int ooffset, int len) 
          Tests if two string regions are equal.

regionMatches(int toffset, String other, int ooffset, int len) 
          Tests if two string regions are equal.

replace(char oldChar, char newChar) 
          Returns a new string resulting from replacing all occurrences of oldChar in this string with newChar.

replace(CharSequence target, CharSequence replacement) 
          Replaces each substring of this string that matches the literal target sequence with the specified literal replacement sequence.

replaceAll(String regex, String replacement) 
          Replaces each substring of this string that matches the given regular expression with the given replacement.

replaceFirst(String regex, String replacement) 
          Replaces the first substring of this string that matches the given regular expression with the given replacement.

split(String regex) 
          Splits this string around matches of the given regular expression.

split(String regex, int limit) 
          Splits this string around matches of the given regular expression.

startsWith(String prefix) 
          Tests if this string starts with the specified prefix.

startsWith(String prefix, int toffset) 
          Tests if the substring of this string beginning at the specified index starts with the specified prefix.

subSequence(int beginIndex, int endIndex) 
          Returns a new character sequence that is a subsequence of this sequence.

substring(int beginIndex) 
          Returns a new string that is a substring of this string.

substring(int beginIndex, int endIndex) 
          Returns a new string that is a substring of this string.

toCharArray() 
          Converts this string to a new character array.

toLowerCase() 
          Converts all of the characters in this String to lower case using the rules of the default locale.

toLowerCase(Locale locale) 
          Converts all of the characters in this String to lower case using the rules of the given Locale.

toString() 
          This object (which is already a string!) is itself returned.

toUpperCase() 
          Converts all of the characters in this String to upper case using the rules of the default locale.

toUpperCase(Locale locale) 
          Converts all of the characters in this String to upper case using the rules of the given Locale.

trim() 
          Returns a copy of the string, with leading and trailing whitespace omitted.

valueOf(boolean b) 
          Returns the string representation of the boolean argument.

valueOf(char c) 
          Returns the string representation of the char argument.

valueOf(char[] data) 
          Returns the string representation of the char array argument.

valueOf(char[] data, int offset, int count) 
          Returns the string representation of a specific subarray of the char array argument.

valueOf(double d) 
          Returns the string representation of the double argument.

valueOf(float f) 
          Returns the string representation of the float argument.

valueOf(int i) 
          Returns the string representation of the int argument.

valueOf(long l) 
          Returns the string representation of the long argument.

valueOf(Object obj) 
          Returns the string representation of the Object argument.

Query Functions

Query functions allow to perform SQL queries to the registered datasources using namespace "queries" like that: "queries:value('jira', 'select pname from issuetype')".

value(String dataSourceName, String query)

Returns the result of execution of the query against the datasource with the specified name as a plain value of the first column of the first row of the returned result set

list(String dataSourceName, String query)

Returns the result of execution of the query against the datasource with the specified name as a list of values in the first column of the returned result set

Please check examples of usage here.

Operators

Operator

Description

Operator

Description

Boolean and

The usual && operator can be used as well as the word and, e.g.

cond1 and cond2

and

cond1 && cond2

are equivalent

Boolean or

The usual || operator can be used as well as the word or, e.g.

cond1 or cond2

and

cond1 || cond2

are equivalent

Boolean not

The usual ! operator can be used as well as the word not, e.g.

!cond1

and

not cond1

are equivalent

Bitwise and

The usual & operator is used, e.g.

33 & 4

, 0010 0001 & 0000 0100 = 0.

Bitwise or

The usual | operator is used, e.g.

33 | 4

, 0010 0001 | 0000 0100 = 0010 0101 = 37.

Bitwise xor

The usual ^ operator is used, e.g.

33 ^ 4

, 0010 0001 ^ 0000 0100 = 0010 0100 = 37.

Bitwise complement

The usual ~ operator is used, e.g.

~33

, ~0010 0001 = 1101 1110 = -34.

Ternary conditional ?:

The usual ternary conditional operator condition ? if_true : if_false operator can be used as well as the abbreviation value ?: if_false which returns the value if its evaluation is defined, non-null and non-false, e.g.

val1 ? val1 : val2

and

val1 ?: val2

are equivalent.

NOTE: The condition will evaluate to false when it refers to an undefined variable or null for all JexlEngine flag combinations. This allows explicit syntactic leniency and treats the condition 'if undefined or null or false' the same way in all cases.

 

Following construct means - if iSubtotal is set, return iSubtotal else return 0

{iSubtotal} ?: 0

 

 

Equality

The usual == operator can be used as well as the abbreviation eq. For example

val1 == val2

and

val1 eq val2

are equivalent.

  1. null is only ever equal to null, that is if you compare null to any non-null value, the result is false.

  2. Equality uses the java equals method

Inequality

The usual != operator can be used as well as the abbreviation ne. For example

val1 != val2

and

val1 ne val2

are equivalent.

Less Than

The usual < operator can be used as well as the abbreviation lt. For example

val1 < val2

and

val1 lt val2

are equivalent.

Less Than Or Equal To

The usual <= operator can be used as well as the abbreviation le. For example

val1 <= val2

and

val1 le val2

are equivalent.

Greater Than

The usual > operator can be used as well as the abbreviation gt. For example

val1 > val2

and

val1 gt val2

are equivalent.

Greater Than Or Equal To

The usual >= operator can be used as well as the abbreviation ge. For example

val1 >= val2

and

val1 ge val2

are equivalent.

In or Match=~

The syntactically Perl inspired =~ operator can be used to check that a string matches a regular expression (expressed either a Java String or a java.util.regex.Pattern). For example "abcdef" =~ "abc.* returns true. It also checks whether any collection, set or map (on keys) contains a value or not; in that case, it behaves as an "in" operator. Note that it also applies to arrays as well as "duck-typed" collection, ie classes exposing a "contains" method. "a" =~ ["a","b","c","d","e",f"] returns true.

Not-In or Not-Match!~

The syntactically Perl inspired !~ operator can be used to check that a string does not match a regular expression (expressed either a Java String or a java.util.regex.Pattern). For example "abcdef" !~ "abc.* returnsfalse. It also checks whether any collection, set or map (on keys) does not contain a value; in that case, it behaves as "not in" operator. Note that it also applies to arrays as well as "duck-typed" collection, ie classes exposing a "contains" method. "a" !~ ["a","b","c","d","e",f"] returns true.

Addition

The usual + operator is used. For example

val1 + val2

Subtraction

The usual - operator is used. For example

val1 - val2

Multiplication

The usual * operator is used. For example

val1 * val2

Division

The usual / operator is used, or one can use the div operator. For example

val1 / val2

or

val1 div val2

Modulus (or remainder)

The % operator is used. An alternative is the mod operator. For example

5 mod 2

gives 1 and is equivalent to

5 % 2

Negation

The unary - operator is used. For example

-12

Conditionals

Operator

Description

Operator

Description

if

Classic, if/else statement, e.g.

if ((x * 2) == 5) {
    y = 1;
} else {
    y = 2;
}

 

Notes

  • jqGrid permits to put numbers in exponential form into the number fields (i.e. 1.0E+3), and JEXL permits values such as 1.0E+3 to be input, and assumes them to be decimals so 1.0E+3 * 2 = 2000

 

Related information