The following chart outlines the supported functions for rules.
For more information about the operations with 0/null and DIV, MUL, DEL, and ADD, see Storing Zero Values and Business Rules Overview.
| Function | Description | Argument(s) | Data Type(s) | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ABS(number) | Returns the absolute value of a number. The absolute value of a number is the number without its sign. | numeric | double | ||||||||||||||||||||||
| ACOS(number) | Returns the inverse cosine of a number. The returned angle is given in radians. | numeric | double | ||||||||||||||||||||||
| ADD(number1, number2) | Returns the sum of its two arguments. | numeric | double | ||||||||||||||||||||||
| AND(expression1, expression2, ...) | Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluate to FALSE. | Boolean | Boolean | ||||||||||||||||||||||
| ASIN(number) | Returns the inverse sine. The returned angle is given in radians. | numeric | double | ||||||||||||||||||||||
| ATAN(number) | Returns the inverse tangent. The returned angle is given in radians. | numeric | double | ||||||||||||||||||||||
| AVERAGE(number1, number2, …) | Returns the average of its arguments. | numeric | double | ||||||||||||||||||||||
| CEILING(number) | Rounds a number up to the nearest integer or the nearest multiple of significance. | numeric | double | ||||||||||||||||||||||
| CHAR(number) | Returns a character specified by a code number. | numeric | integer | ||||||||||||||||||||||
| CLEAN(text) | Removes all non-printable characters from the text. | string | string | ||||||||||||||||||||||
| CODE(text) | Returns a numeric code for the first character in a text string. | string | string | ||||||||||||||||||||||
| CONCATENATE(text1, text2, ...) | Joins two or more text items into one text item. | string | string | ||||||||||||||||||||||
| CONTINUE() | If a rule evaluates to CONTINUE then that rule is skipped and search for valid rule is continued. | n/a | n/a | ||||||||||||||||||||||
| COS(number) | Returns the cosine. The number is given in radians. | numeric | double | ||||||||||||||||||||||
| COUNT(number1, number2, …) | Returns the number of values provided. | numeric | double | ||||||||||||||||||||||
| DATE(year, month, day) | Returns a serial number that represents a particular date based on the server rules time system. The year argument must be four digits that are greater than or equal to 1900 and smaller than or equal to 10,000. Example: Date(2015,1,1) returns 1420070400. (60*60*24*16436 seconds since 1.1.1970)."Month" is a number representing the month of the year. If Month is greater than 12, then the date will be calculated as follows: the month will be determined as Month – 12, and the year will be determined as Year + 1. For example, DATE(2008,14,2) returns the serial number 1233532800, which represents February 2, 2009."Day" is a number representing the day of the month. If Day is greater than the number of days in the month specified, then the date will be calculated as follows: the day will be determined as the excess number of days, and the month will be determined as Month + 1. For example, DATE(2008,1,35) returns the serial number 1202083200, which represents February 4, 2008. | all parameters numeric | integer | ||||||||||||||||||||||
| DATEFORMAT(date, format) | Converts a serial number date to string format, where "date" is a numeric date value (serial number) and "format" is a string with the following switches:
| date: numeric date value format: string | double string | ||||||||||||||||||||||
| DATEVALUE(date_text) | Returns the serial number of the date represented as MM-DD-YYYY, MM.DD.YYYY, or MM,DD,YYYY. The function also returns correct values if the date is represented as MM-DD-YY, MM.DD.YY, or MM,DD,YY, as long as YY represents a year later than 1999.Examples:=DATEVALUE("01-02-2015") returns serial number 1420156800, which represents 02-Jan-2015 (=60*60*24*16437 seconds since 1.1.1970).=DATEVALUE("01-02-70") returns serial number 3155846400, which represents 02-Jan-2070 (=60*60*24*36526 seconds since 1.1.1970). Note that this would be an incorrect result if the intended year was 1970. | date string to convert | string | ||||||||||||||||||||||
| DEL(number A, number B) | Returns the result of subtracting the second argument from the first. | numeric | double | ||||||||||||||||||||||
| DIV(number A, number B) | Returns the result of dividing the two arguments. | numeric | double | ||||||||||||||||||||||
| EQ(operand A, operand B) | Checks if the two arguments, which can be of type string or double, are exactly equal. Returns true or false accordingly. Both arguments must be of the same type. | comparison | double or string | ||||||||||||||||||||||
| ES(k-th percentile, number1, [number2, …]) | Returns the average of the values exceeding k-th percentile of values in a list, where k-th percentile is in the range 0..1 (inclusive). | numeric | doubles | ||||||||||||||||||||||
| EVEN(number) | Rounds a number up to the nearest even integer. | numeric | double | ||||||||||||||||||||||
| EXACT(text1, text2) | Checks to see if two text values are identical. Compares two text strings and returns 1 if they are exactly the same; otherwise returns 0. This function is case-sensitive. | comparison | string | ||||||||||||||||||||||
| EXIST(x) | Determines whether a cube cell holds a value or not. It returns null if x is empty, or 1 if x is numeric, string, or an error. Parameter can be numeric (including zero) or string (including empty). Note: this rule replaces ISNULL, which will be deprecated in the future. | value can be any type | numeric, string | ||||||||||||||||||||||
| EXP(number) | Returns e raised to the power of a given number. Returns null for null input. | numeric | double | ||||||||||||||||||||||
| FACT(number) | Returns the factorial of the argument. Can be slow for very large numbers. | numeric | integer | ||||||||||||||||||||||
| FIRST(number1, number2, …) | Returns first of its arguments. | numeric | double | ||||||||||||||||||||||
| FLOOR(number) | Rounds a number down toward zero. | numeric | integer | ||||||||||||||||||||||
| GE(operand A, operand B) | Checks if the first argument is greater than or equal to the second argument. Both arguments must be the same type, which can be double or string. | comparison | double or string | ||||||||||||||||||||||
| GT(operand A, operand B) | Checks if the first argument is greater than the second argument. Both arguments must be the same type, which can be double or string. | comparison | double or string | ||||||||||||||||||||||
| IF(test, value-if-true, value-if-false) * | Checks if the first parameter is TRUE. In this case, the second parameter is returned. Otherwise, the third parameter is returned.The third (false) argument can be omitted for OLAP rules; in this case, the default value for the third argument internally will be null. This option is also available for the graphical rule editor. To remove the third argument from the IF expression, click the cogwheel menu in the interface and select Delete. | test, value-if-true, value-if-false (optional) | Boolean object object (optional) | ||||||||||||||||||||||
| IFS(<condition1>,<result1>,[<default> OR <condition2>,<result2>],...[<default> OR <condition3>,<result3>]) | Evaluates multiple expressions and returns a value that corresponds to the first TRUE result. In contrast to IFS in Excel , the number of arguments must always be even. As a workaround, a "default" result could be handled by using TRUE as the last condition argument.Note: <default> (=default result) can only be the last argument, but is optional. | test, value-if-true, value-if-false (optional) | Boolean object object (optional) | ||||||||||||||||||||||
| INT(number) | Rounds a number down to the nearest integer. | numeric | integer | ||||||||||||||||||||||
| ISERROR(test) | Returns TRUE if test expression can't be evaluated or evaluates to non-string or non-numeric value. | test expression | double or string | ||||||||||||||||||||||
| ISNULL(value) | Function returns:
| Value can be any type | numeric, string | ||||||||||||||||||||||
| LAST(number1, number2, …) | Returns last of its arguments. | numeric | double | ||||||||||||||||||||||
| LE(operand A, operand B) | Checks if the first argument is less than or equal to the second argument. Both arguments must have the same type, which can be double or string. | comparison | double or string | ||||||||||||||||||||||
| LEFT(text, num_chars) | Returns the leftmost characters from a text value. "text" is the text string that contains the characters you want to extract, and "num_chars" specifies the number of characters to be extracted. The value of num_chars must be greater than or equal to zero. If num_chars is greater than the length of text, LEFT returns all of the text. If num_chars is omitted, it is assumed to be 1. | text: base string num_chars: number of chars | string integer | ||||||||||||||||||||||
| LEN(text) | Returns the number of characters in a text string. | string to be counted | string | ||||||||||||||||||||||
| LN(number) | Returns the natural logarithm of a number. | numeric | integer | ||||||||||||||||||||||
| LOG(number, base) | Returns the logarithm of a number to a specified base. | numeric | double | ||||||||||||||||||||||
| LOG10(number) | Returns the base-10 logarithm of a number. | numeric | integer | ||||||||||||||||||||||
| LOWER(text) | Converts text to lowercase. | string | string | ||||||||||||||||||||||
| LT(operand A, operand B) | Checks if the first argument is less than the second argument. Both arguments must have the same type, which can be double or string. | comparison | double or string | ||||||||||||||||||||||
| MAX(number1, number2, …) | Returns the maximum value of the given arguments. | numeric | double | ||||||||||||||||||||||
| MEDIAN(number1, number2, …) | Returns the median of the given arguments. | numeric | double | ||||||||||||||||||||||
| MID(text, start_num, num_chars) | Returns a specific number of characters from a text string, starting at the specified position and number of characters. "text" is the text string containing the characters you want to extract. "start_num" is the position of the first character you want to extract from the text. "num_chars" specifies the number of characters you want to return from text.Output:
| text: base string start_num: position num_chars: number of characters | string integer integer | ||||||||||||||||||||||
| MIN(number1, number2, …) | Returns the minimum value of the given arguments. | numeric | double | ||||||||||||||||||||||
| MOD(number, divisor) | Returns the remainder after the number is divided by divisor. The result has the same sign as the divisor. | numeric | integer | ||||||||||||||||||||||
| MUL(number A, number B) | Returns the product of multiplying the two arguments. | numeric | double | ||||||||||||||||||||||
| NE(operand A, operand B) | Checks if the two arguments, which can be of type string or double, are exactly equal. Returns true if there is no equality and false otherwise. Both arguments must be the same type. | comparison | double or string | ||||||||||||||||||||||
| NOT(boolean) | Returns the logical NOT of given argument. Can be slow when negating a large, sparse data set. | Boolean | double | ||||||||||||||||||||||
| NOW() | Returns the time when an OLAP job began processing. See note on the time system for server rules.Warning: using NOW() could slow down subsequent report calculations, because rule results that depend on the NOW() function result and other volatile data sources are not stored in cube caches. | no argument required | double | ||||||||||||||||||||||
| ODD(number) | Returns number rounded up to the nearest odd integer. | numeric | double | ||||||||||||||||||||||
| OR(expression1, expression2) | Returns the logical OR of its arguments. | Boolean | Boolean | ||||||||||||||||||||||
| PALO.CUBEDIMENSION(database, cube, num_n) | Returns the name of the nth dimension in a specified hypercube. | database, cube, number of dimension | string, string, integer | ||||||||||||||||||||||
| PALO.DATA(database, cube, coordinate1, coordinate2,..., coordinateN) | Retrieves the value of the specified element from the cube.An empty string ("") can be entered for the <database> and <cube> arguments. | database, cube, coordinates | string | ||||||||||||||||||||||
| PALO.ECHILD(database, dimension, parent element, num_child) | Retrieves the name of the specified child element. | database, dimension, parent element, number of child element to retrieve | string, string, string, integer | ||||||||||||||||||||||
| PALO.ECHILDCOUNT(database, dimension, element) | Retrieves the number of children in the specified consolidated element. | database, dimension, element | string | ||||||||||||||||||||||
| PALO.ECOUNT(database, dimension) | Retrieves the amount of dimension elements in the specified dimension. | database, dimension | string | ||||||||||||||||||||||
| PALO.EFIRST(database, dimension) | Retrieves the first element in the specified dimension. | database, dimension | string | ||||||||||||||||||||||
| PALO.EINDENT(database, dimension, element) | Retrieves the indention level of the specified element. | database, dimension, element | string | ||||||||||||||||||||||
| PALO.EINDEX(database, dimension, element) | Retrieves the position of the specified dimension element. | database, dimension, element | string | ||||||||||||||||||||||
| PALO.EISANC(database, dimension, parent, element) | Checks if a consolidated element contains a specified element in all of its descendants; results in 0 or 1.Similar to PALO.EISCHILD but also checks indirect relations between elements, e.g. Year to January, when Q1 is child of Year and Q1 is parent of January.PALO.EISANC("", "month", "Year", "January") returns 1.Function returns #ERROR when nonexistent database, dimension, or element is specified as a parameter. | database, dimension, parent, element | string | ||||||||||||||||||||||
| PALO.EISCHILD(database, dimension, parent element, element) | Checks if a consolidated element contains the specified element, results in 0 or 1. | database, dimension, parent, element | string | ||||||||||||||||||||||
| PALO.ELEVEL(database, dimension, element) | Returns the level in the dimension hierarchy of a specified element. | database, dimension, element | string | ||||||||||||||||||||||
| PALO.ENAME(database, dimension, position) | Retrieves the name of the element at the specified position (First Position is 1). | database, dimension, position | string, string, integer | ||||||||||||||||||||||
| PALO.ENEXT(database, dimension, element) | Retrieves the name of the succeeding element of a dimension element. | database, dimension, element | string | ||||||||||||||||||||||
| PALO.EOFFSET(database, dimension,element, index) | Retrieves the name of the dimension element distant by offset from specified element. | database, dimension, element, index of offset | string, string, string, integer | ||||||||||||||||||||||
| PALO.EPARENT(database, dimension, element, num_n) | Retrieves the name of the n-th parent of the specified element. | database, dimension, element, index of parent | string, string, string, integer | ||||||||||||||||||||||
| PALO.EPARENTCOUNT(database, dimension, element) | Retrieves the number of consolidated elements which contain the specified element. | database, dimension, element | string | ||||||||||||||||||||||
| PALO.EPREV(database, dimension, element) | Retrieves the name of the preceding element of a dimension element. | database, dimension, element | string | ||||||||||||||||||||||
| PALO.ESIBLING(database, dimension, element, index) | Retrieves the name of the specified sibling. | database, dimension, element, index of sibling | string, string, string, integer | ||||||||||||||||||||||
| PALO.ETOPLEVEL(database, dimension) | Returns the level number of the highest element in the consolidation hierarchy of a dimension. | database, dimension | string | ||||||||||||||||||||||
| PALO.ETYPE(database, dimension, element) | Retrieves the type of the specified element.(numeric, string, or consolidated). | database, dimension, element | string | ||||||||||||||||||||||
| PALO.EWEIGHT(database, dimension, parent, child) | Returns the consolidation weight of a specified component of an element. | database, dimension, parent, child | string | ||||||||||||||||||||||
| PALO.MARKER(database, cube, coordinate1, coordinate2, ..., coordinateN) | Adds marker for a slice.An empty string ("") can be entered for the <database> and <cube> arguments. | database, cube, coordinates | string | ||||||||||||||||||||||
| PERCENTILE(k-th percentile, number1, [number2, …]) | Returns the k-th percentile of the values in a list, where k-th percentile is in the range 0..1, inclusive. | numeric | double | ||||||||||||||||||||||
| PI() | Returns the value of pi. | no argument required | |||||||||||||||||||||||
| POWER(number, power) | Returns the result of a number raised to a power.As of version 2018.3, this function returns null for null input. As a workaround to obtain the result from previous versions, you can change POWER(['number'], ['power']) to POWER(['number'], ['power'] + 0) | numeric | double | ||||||||||||||||||||||
| PROPER(text) | Capitalizes the first letter in a text string, as well as any other letters in the text that follow any character other than a letter. Converts all other letters to lowercase letters. | string | string | ||||||||||||||||||||||
| QUOTIENT(numerator, denominator) | Returns the integer portion of a division equation. | numeric | double | ||||||||||||||||||||||
| RAND() | Returns a random number between 0 and 1. | ||||||||||||||||||||||||
| RANDBETWEEN(bottom, top) | Returns a random number in the range you specify. | numeric | double | ||||||||||||||||||||||
| REPLACE(old_text, start_num, num_chars, new_text) | Replaces part of a text string with a different text string, based on the specified number of characters. "old_text" is the text in which you want to replace some characters. "start_num" is the position of the character in old_text that you want to replace with new_text. "num_chars" is the number of characters in old_text that you want to replace with new_text. "new_text" is the text that will replace characters in old_text. | old_text: base string start_num: start index num_chars: end index new_text: replacement | string, integer, integer, string | ||||||||||||||||||||||
| REPT(text, num_times) | Repeats text a given number of times. "text" is the text you want to repeat. "num_times" is a positive number specifying the number of times to repeat text. If num_times is 0 (zero), REPT returns null. If number_times is not an integer, it is truncated. | text: base-string num_times: number of repetitions | string, integer | ||||||||||||||||||||||
| RIGHT(text, num_chars) | Returns the rightmost characters from a text value. "text" is the text string that contains the characters you want to extract. "num_chars" specifies the number of characters you want to extract and must be greater than or equal to zero. If num_chars is greater than the length of text, RIGHT returns all of the text. If num_chars is omitted, it is assumed to be 1. | text: base-string num_chars: number of chars | string, integer | ||||||||||||||||||||||
| ROUND(num_base, num_digits) | Rounds a number to a specified number of digits. If num_digits is 0, then number is rounded to the nearest integer. If num_digits is less than 0, then number is rounded to the left of the decimal point. | num_base: number to be rounded num_digits: number of digits | double, integer | ||||||||||||||||||||||
| SEARCH(find_text, within_text) | Finds one text value within another (not case-sensitive). "find_text" is the text you want to find. You can use the wildcard characters question mark (?) and asterisk (*) in find_text: question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character. Within_text is the text in which you want to search for find_text. If find_text is not found, then 0 is returned. If found, then position is returned. | find_text: string to search for within_text: string in which you want to search for find_text | string | ||||||||||||||||||||||
| SIGN(number) | Returns the sign of a number. | numeric parameter | double | ||||||||||||||||||||||
| SIN(number) | Returns the sine. The number is given in radians. | numeric | double | ||||||||||||||||||||||
| SQRT(number) | Returns a positive square root. | numeric | double | ||||||||||||||||||||||
| STET() | If a rule evaluates to STET, then the resulting cell behaves like no rule is existent for this cell. | ||||||||||||||||||||||||
| STR(number, width, precision) | Converts number to string. As of 2018.3: 2nd and 3rd parameters are optional. Default is 0. | number: number to be converted width: total length of resulting string precision: number of decimal places | double, integer, integer | ||||||||||||||||||||||
| SUBSTITUTE(text_base, old_text, new_text) | Substitutes new text for old text in a text string. "text_base" is the text (or the reference to a cell containing text) for which you want to substitute characters. "old_text" is the text you want to replace. "new_text" is the text you want to replace old_text with. | text_base: base-string old_text: string to be replaced new_text: replacement | string | ||||||||||||||||||||||
| SUM(number1, number2, …) | Returns sum of values provided through function arguments. | numeric | double | ||||||||||||||||||||||
| TAN(number) | Returns the tangent in radians. | numeric | double | ||||||||||||||||||||||
| TRIM(text) | Removes spaces from text. | string | string | ||||||||||||||||||||||
| TRUNC(number) | Truncates a number to an integer. | numeric | double | ||||||||||||||||||||||
| UPPER(text) | Converts text to uppercase. | string | string | ||||||||||||||||||||||
| VALUE(text) | Converts a string that represents a number to a number. Returns 0 if the conversion fails. | string | string | ||||||||||||||||||||||
| VALUEDATE(number) | Returns a date_text for a given serial number in the format MM-DD-YY. Target area must be of type string. | numeric argument representing a date | double | ||||||||||||||||||||||
| WEEKDAY(serial_number) | Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default. | numeric argument representing a date | double |
Server rules have a time system with 1.1.1970 as start time and seconds as units, e.g.: 1.1.1970 00:00:00 = 0, 2.1.1970 00:00:00 = 86400 (= 24h * 60min * 60sec).
Updated November 4, 2024