Formula collection
Written by Bettina Schmoll
Updated at May 13th, 2025
BI4-Controlling formula and reporting functions
Generally
For calculating, transforming, and manipulating values and text, as well as implementing logic sequences, several functions, many of which can also be found in Excel, have been incorporated into the system. These are listed below according to their functional purpose.
Multiple functions can also be combined and nested within each other.
IMPORTANT!
If multiple commands are separated by a slash in the heading of a subsection, these are different names for the same function. For simplicity, only the first command is listed in the syntax and example. However, the parameters and their order are identical for all commands listed.
Date & Time Functions
ACTANIVERSARY
The ACTANIVERSARY function returns the anniversary in the given year based on the second date passed. If both dates are in the same calendar year, the anniversary is returned. Otherwise, the anniversary in the year of the second date is returned.
Syntax: ACTANIVERSARY( anniversary ; date )
Anniversary : a date for determining an anniversary.
Date : a date that comes after the anniversary.
Example: ACTANIVERSARY(01.05.2018;04.01.2019) returns the date 01.05.2019.
BDATEDIFF
The BDATEDIFF function returns the difference between two dates in whole days, taking the banking calendar into account. Each month is calculated as 30 days.
Syntax: BDATEDIFF( start date ; end date )
Start date : first date for determining the difference in the format DD.MM.YYY
End date : second date for determining the difference in the format DD.MM.YYY
Example: BDATEDIFF(01.02.2018;01.03.2018) returns the value 30 (days).
CALCDATE
The CALCDATE function calculates a new date based on the passed date and a date formula.
Syntax: CALCDATE( date ; date formula )
Date: a valid date since 01.01.1753 in the format DD.MM.YYYY
Date formula: a formula consisting of signs, numbers and the characters
“L”, … ongoing
“T” … day,
“W” … week,
“M” … month,
“Q” … quarter and
“Y” … year.
Example: CALCDATE(01.05.2018;+1M-4D)
First, one month is added to the start date (May 1, 2018), and then four days are subtracted. The result is returned as May 28, 2018.
DATEADD
The DATEADD function adds the number of days / weeks / months / quarters / years to the specified start date and returns the resulting date
Syntax: DATEADD( Date ; DatePart ; Value )
Date : a valid date since 01.01.1753 in the format DD.MM.YYYY
Date part : a letter representing the part of the date to be added. The following letters are allowed:
“T” … day
“W” … week
“M” … month
“Q” … quarter
“Y” … year
Value : an integer, both positive and negative, to be added to the specified date.
Example : DATEADD(01.05.2018;W;2) adds 2 weeks to the specified date and then returns the value 15.05.2018 as the date.
DATEDIFF
The DATEDIFF function returns the difference between two dates in whole days.
Syntax: DATEDIFF( start date ; end date )
Start date : first date for determining the difference in the format DD.MM.YYY
End date : second date for determining the difference in the format DD.MM.YYY
Example: DATEDIFF(01.05.2018;15.05.2018) returns the value 14 (days).
DAY
The DAY function returns the current day number within the month as a number.
Syntax: DAY( Date )
Date : a valid date since 01.01.1753 in the format DD.MM.YYYY
Example: DAY(15.05.2018) returns the value 15.
END DATE
The ENDDATE function returns the last day of a time range as a date. A partial date is passed. The function then determines whether it is a year, a quarter, a month, or a week and returns the last day as a date. The date code is usually passed through an appropriate filter, e.g., service date -> year.month.
Syntax : ENDDATE( datecode )
Date code : a year, a quarter, a month or a week
Example : ENDDATE(2018) returns 31.12.2018 as the date.
ENDDATE(2018Q3) returns September 30, 2018 as the date.
ENDDATE(2018.05) returns May 31, 2018 as the date.
ENDDATE(2018W44) returns 04.11.2018 as the date.
ENDOFMONTH / MONTHEND
The ENDOFMONTH function returns the last day of the month of the given date.
Syntax: ENDOFMONTH( Date )
Date : a valid date since 01.01.1753 in the format DD.MM.YYYY
Example: ENDOFMONTH(15.05.2018) returns 31.05.2018 as the date.
ENDOFWEEK
The ENDOFWEEK function returns the last day of the week (Sunday) of the given date.
Syntax: ENDOFWEEK( Date )
Date : a valid date since 01.01.1753 in the format DD.MM.YYYY
Example: ENDOFWEEK(15.05.2018) returns 20.05.2018 as the date, since 15.05 was a Tuesday
FIRST DATE
The FIRSTDATE function returns the first filled date field of the passed date fields.
Syntax : FIRSTDATE( Date1;Date2;…;DateN )
Date1;Date2;…;DateN : individual date fields that are passed
Example : FIRSTDATE(01.01.1753;31.05.2018;01.01.1753;27.06.2018) returns May 31, 2018, as the date, since this is the first field filled in. For date fields, 01.01.1753 is passed instead of NULL.
FORMATDATE
The FORMATDATE function returns the passed date as text in the format specified by the formula.
Syntax: FORMATDATE( Date ; Formula )
Date : a valid date since 01.01.1753 in the format DD.MM.YYYY
Formula : a formula
Example: FORMATDATE(15.05.2018;MM/DD/YYYY) returns 05/15/2018 as text.
LASTANIVERSARY
The LASTANIVERSARY function returns the last anniversary that occurs before the 2nd date.
Syntax: LASTANIVERSARY( anniversary ; date )
Anniversary : a date for determining an anniversary.
Date : a date that comes after the anniversary.
Example: LASTANIVERSARY(01.05.2018;01.08.2019) returns the date 01.05.2019.
MAXDATE / MAXDATEWITHEMPTYMAX
The MAXDATE function returns the largest date in a date range. A NULL value is returned as 12/31/2099.
MINDATE / MINDATEWITHEMPTY
The MINDATE function returns the smallest date in a date range. A NULL value is returned as 01/01/1753.
MONTH
The MONTH function returns the consecutive number of the month within the year as a number.
Syntax: MONTH( Date )
Date : a valid date since 01.01.1753 in the format DD.MM.YYYY
Example: MONTH(15.05.2018) returns 5 as the value.
NEXTNIVERSARY
The NEXTANIVERSARY function returns the next anniversary that occurs after the 2nd date.
Syntax: NEXT IVERSARY ( anniversary ; date )
Anniversary : a date for determining an anniversary.
Date : a date that comes after the anniversary.
Example: NEXTANIVERSARY(01.05.2018;04.01.2019) returns the date 01.05.2019.
START DATE
The STARTDATE function returns the first day of a time range as a date. A partial date is passed. The function then determines whether it is a year, a quarter, a month, or a week and returns the first day as a date. The date code is usually passed through an appropriate filter, e.g., service date -> year.month.
Syntax : STARTDATE( datecode )
Date code : a year, a quarter, a month or a week
Example : STARTDATE (2018) returns 01/01/2018 as the date.
STARTDATE (2018Q3) returns 01.07.2018 as the date.
STARTDATE (2018.05) returns 01.05.2018 as the date.
STARTDATE (2018W44) returns October 29, 2018 as the date.
YEAR
The YEAR function returns the year as a number.
Syntax: YEAR( Date )
Date : a valid date since 01.01.1753 in the format DD.MM.YYYY
Example: YEAR(15.05.2018) returns 2018 as the value.
Mathematical functions
ABS
The ABS function returns the absolute value of a value.
Syntax: ABS( value )
Value : any decimal number with sign
Example : ABS(-5) returns 5 as the result.
CEIL / CEILING
The CEILING function returns the number rounded up to the nearest integer
Syntax : CEIL( number )
Number : a number with decimal places
Example : CEIL(1.547,33) returns the value 1.548,00 as a number.
DYNROUND / DYN ROUNDS
The DYNROUND function rounds from the front to the specified places.
Syntax : DYNROUND( number1 ; number2 )
Number1 : Number to be rounded
Number2 : Number that reflects the places to which the number should be rounded
Example : DYNROUND(30486.25;1000) returns the value 30490.00 as a number.
DYNROUND(523.62;1000) returns the value 523.60 as a number.
DYNRQUOTIENT
The DYNRQUOTIENT function returns the place to which a number was rounded.
Syntax : DYNRQUOTIENT( number1 ; number2 )
Number1 : Number to be rounded
Number2 : Number that reflects the places to which the number should be rounded
Example : DYNRQUOTIENT (30486.25;1000) returns the value 10 as a number.
DYNRQUOTIENT (523.62;1000) returns the value 0.1 as a number.
FLOOR
The FLOOR function returns the number rounded to the nearest integer.
Syntax : FLOOR( number )
Number : a number with decimal places
Example : FLOOR(1.547,33) returns the value 1.547,00 as a number.
INTERESTED
The INTERESTEXTENDED function is used to calculate interest on a daily basis.
For the calculation to work, there must be a date breakdown at row level.
Syntax : INTERESTEXTENDED( amount ; percentage ; billing ; billing date ; calendar )
Amount : a column containing the starting amount
Percentage : Percentage at which the amount is interest-bearing
Invoice : Parameter that indicates whether there is a billing of costs.
Possible values: 0…..no billing
1…..Billing active
Billing date : Parameter that specifies when billing occurs.
Possible values: N…..Billing afterwards
V…..Invoicing in advance
Calendar : Parameter that specifies which calendar should be used.
Possible values: CD…calendar days
BD…banking days
MAX
The MAX function returns the largest value passed to the function.
Syntax : MAX( number1 ; number2 ;…; numberN )
Number1 ; Number2 ;…; NumberN : a series of numbers or a column
Example : MAX(37;25;69;18) returns the value 69 as a number.
MAX(B$) returns the largest value in column B.
MIN
The MIN function returns the smallest value passed to the function.
Syntax : MIN( Number1 ; Number2 ;…; NumberN )
Number1 ; Number2 ;…; NumberN : a series of numbers or a column
Example : MIN(37;25;69;18) returns the value 18 as a number.
MIN(B$) returns the smallest value in column B.
MINEXCEPT0
The function MINEXCEPT0 returns the smallest value passed to the function. However, the number 0 is omitted.
Syntax : MINEXCEPT0( Number1 ; Number2 ;…; NumberN )
Number1 ; Number2 ;…; NumberN : a series of numbers or a column
Example : MINEXCEPT0(37;25;69;18;0) returns the value 18 as a number.
MIN(B$) returns the smallest value in column B.
ROUND / ROUNDS
The ROUND function rounds a number up or down to the nearest whole number.
Syntax : ROUND( number ; decimal places )
Number : a number with decimal places or a column
Decimal places : Number of decimal places to round to
Example : ROUND(37.25;1) returns the value 37.3 as a number.
SHARE
The SHARE function returns the percentage of a partial amount of the total amount.
Syntax : SHARE( Partial Amount ; Total Amount )
Partial amount : a number or a column
Total : a number or a column from which the percentage is to be calculated
Example : SHARE(140;200) returns the value 70 as a percentage.
SHAREWIHRANGE
The SHARE function calculates the percentage of a partial amount from the total amount and allocates it to the specified range. However, it does not return the percentage, but rather a value that corresponds to the percentage of the specified range.
Syntax : SHARE( partial amount ; total amount ; range start ; range end )
Partial amount : a number or a column
Total amount : a number or a column from which the percentage is to be calculated
Start of area : a number or column that represents the beginning of the range of values
End of range : a number or a column that represents the end of the range of values
Example : SHAREWITHRANGE(140;200;1000;2000) returns the value 1,700.
Text functions
CHARCOUNT
The CHARCOUNT function returns the number of characters in a text up to a separator.
Syntax : CHARCOUNT( text ; delimiter )
Text : any string of characters.
Delimiter : a separator that marks the end of the string.
Example : CHARCOUNT(This is a text!;!) returns 16 as a number.
CON / CONCAT /CONCATE / CONCATENATE / CONCATENATE
The CONCAT function concatenates different data types and returns them as text. Any separator can be inserted between the individual values.
Syntax : CONCAT( Value 1 ; Value 2 ;…; Value n )
Value 1 : a string, a number, a date, etc.
Value 2 : a string, a number, a date, etc.
Value n : a string, a number, a date, etc.
Example : CONCAT(387;´-´;15.05.2018) returns the string 387-15.05.2018.
CONSP / CONWSP / CONCATSP / CONCATWSP / CONCATWITHSPACE / CONCATENATEWITHSPACE
The CONCATSP function is similar to the CONCAT function, except that a space is always inserted between the values.
Syntax : CONCATSP( Value 1 ; Value 2 ;…; Value n )
Value 1 : a string, a number, a date, etc.
Value 2 : a string, a number, a date, etc.
Value n : a string, a number, a date, etc.
Example : CONCATSP(387;15.05.2018) returns the string 387 15.05.2018.
CONTAIN / CONTAINS
The CONTAINS function searches a string for another string and returns 1 if it finds one. Otherwise, it returns 0.
Syntax : CONTAINS( Text 1 ; Text 2 )
Text 1 : a string to be searched.
Text 2 : a string to search for.
Example : CONTAINS(This is a text;is) returns the value 1 because the string “is” was found.
CONWD / CONCATD / CONCATWD /CONCATWDOT / CONCATWITHDOT / CONCATENATEWITHDOT
The CONCATD function is similar to the CONCATSP function, except that a period is always inserted between the values.
Syntax : CONCATD( Value 1 ; Value 2 ;…; Value n )
Value 1 : a string, a number, a date, etc.
Value 2 : a string, a number, a date, etc.
Value n : a string, a number, a date, etc.
Example : CONCATD(387;15.05.2018) returns the string 387.15.05.2018.
END WITH / ENDS WITH
The ENDSWITH function searches a string for another string and returns 1 if it is found at the end of the string. Otherwise, it returns 0.
Syntax : ENDSWITH( Text 1 ; Text 2 )
Text 1 : a string to be searched.
Text 2 : a string to search for at the end of the string.
Example : ENDSWITH(This is a text;is) returns the value 0 because the string “is” was found, but it is not at the end of the string.
FIRSTFILLED / FF
The FIRSTFILLED function returns the value of the first filled field.
Syntax : FIRSTFILLED( Text1 ; Text2 ; … ; TextN )
Text1 ; Text2 ; … ; TextN : a string that appears in different text fields.
Example : FILLEDTEXTFIELD( NULL ;Invoice; Null ) returns 2 the value “Invoice” because the first and third fields are empty.
LEFT
The LEFT function returns the specified number of characters from the beginning of a string.
Syntax : LEFT( Text ; Count )
Text : any string of characters.
Count : the number of characters to return.
Example : LEFT(This is a text;5) returns “The i”.
LEN / LENGTH
The LEN function returns the length of a string.
Syntax : LENGTH( Text )
Text : any string of characters.
Example : LENGTH(This is a text) returns 16 as a number.
MID / PART / SUBSTR / SUBSTRING
The SUBSTRING function returns the number of characters in a string.
Syntax : SUBSTRING( Text ; Start ; Count )
Text : any string of characters.
Start : Number of the character from which the string should be returned.
Count : Number of characters to return.
Example : SUBSTRING(This is a text;4;3) returns “is” as a string.
REMOVESTRING
The REMOVESTRING function removes one string from another.
Syntax : REMOVESTRING( Text 1 ; Text 2 )
Text 1 : any string.
Text 2 : the string to be removed.
Example : REMOVESTRING(This is a text;is) returns “This is a text” as a string.
REPLACE RING
The REPLACESTRING function removes one string from another.
Syntax : REPLACESTRING( Text 1 ; Text 2 ; Text 3 )
Text 1 : any string.
Text 2 : the string to be removed.
Text 3 : the string to be replaced.
Example : REPLACESTRING (This is a text;a;1) returns “This is a 1 text” as a string.
RIGHT
The RIGHT function returns the specified number of characters from the end of a string.
Syntax : RIGHT( Text ; Count )
Text : any string of characters.
Count : the number of characters to return.
Example : RIGHT(This is a text;6) returns “n text”.
RIGHTCUT
The RIGHTCUT function cuts the specified text after the desired number of characters.
Syntax : RIGHTCUT( Text ; Count )
Text : any string of characters.
Number : the number of characters after which the text should be truncated.
Example : RIGHTCUT(This is a text;11) returns “This is a”.
STARTWITH / STARTSWITH
The ENDSWITH function searches a string for another string and returns 1 if it is found at the end of the string. Otherwise, it returns 0.
Syntax : STARTSWITH( Text 1 ; Text 2 )
Text 1 : a string to be searched.
Text 2 : a string to search for at the beginning of the string.
Example : STARTSWITH(This is a text;is) returns the value 1 because the string “This” was found and is at the beginning of the string.
TEXT FIELD
The TEXTFIELD function splits a string containing one or more delimiters into individual strings. The occurrence specified by the number is returned.
Syntax : TEXTFIELD( Text ; Delimiter ; Number )
Text : a string containing one or more delimiters.
Delimiter : a delimiter with which the text should be split.
Number : a number describing the occurrence to be returned.
Example : TEXTFIELD(Invoice18-38715;-;2) returns “38715” as text.
TRIM
The TRIM function removes spaces from the beginning and end of a string.
Syntax : Trim( Text )
Text : a string containing one or more delimiters.
Example : TRIM( This is a text ) returns the string “This is a text” without the leading and trailing spaces.
UAD / UPPERALPHADIGIT
The UPPERALPHADIGIT function converts a character string to uppercase letters. All umlauts and the "ß" character are replaced. Furthermore, only the numbers 0 through 9 and the letters A through Z are returned. Punctuation and other special characters are filtered out.
Syntax : UPPERALPHADIGIT( Text )
Text : a string containing one or more delimiters.
Example : UPPERALPHADIGIT(This is a text!) returns the string “THISISATEXT”.
WORD
The WORD function splits a string into individual characters. The space character serves as the separator.
Syntax : WORD( Text )
Text : a string containing one or more spaces.
Example : WORD(This is a text) returns 4 texts, once “This”, the second time “is”, the third time “a” and finally “Text”.
Logic functions
AND / AND
The AND function is used to combine two or more logical conditions. It returns "True" if all conditions are met.
Syntax : AND( Condition 1 ; Condition 2 ;…; Condition n )
Condition 1 : a logical condition.
Condition 2 : a logical condition.
Condition n : a logical condition.
Example : AND(YEAR([TODAY])=2018;MONTH([TODDAY])=5) returns True if the current day's date is in May 2018.
CASE / SWITCH
The CASE function is used when a condition is required to decide which of several values should be returned.
Syntax : IF( Condition 1 ; Value 1 ; Condition 2 ; Value 2 ; Value 3 )
Condition 1, Condition 2 : a logical condition.
Value 1, Value 2 : the value returned when the associated condition is met.
Value 3 : the value returned if none of the conditions are met.
Example : CASE(X<5;Less than 5;X<10;Less than 10;Greater than) returns "Greater than" if the value of the variable X is greater than 10. However, if the value of X is less than 10 and greater than or equal to 5, "Less than 10" is returned.
IF / WHEN
The IF function is used when a condition is required to decide which of two values should be returned.
Syntax : IF( Condition ; Value 1 ; Value 2 )
Condition : a logical condition.
Value 1 : the value returned when the condition is met.
Value 2 : the value returned if the condition is not met.
Example : IF(X>5,Greater,Lesser) returns "Greater" if the value of the variable X is greater than 5. However, if the value of X is less than or equal to 5, "Lesser" is returned.
OR
The OR function is used to combine two or more logical conditions. It returns "True" if one of the conditions is met.
Syntax : OR( Condition 1 ; Condition 2 ;…; Condition n )
Condition 1 : a logical condition.
Condition 2 : a logical condition.
Condition n : a logical condition.
Example : OR(YEAR([TODAY])=2018;MONTH([TODDAY])=5) returns True if the current day's date is in May (of any year) or in 2018.
Conversion functions
DATE
The DATE function converts the passed text into a date and returns a value of type Date.
Syntax: DATE( day ; month ; year )
Example: DATE(12;2;2018) returns 12.02.2018 as the date value
DECIMAL / DECIMAL
The TODECIMAL function converts a string into a valid decimal value.
Syntax: TODECIMAL( text ; decimal separator )
Text : the number to be converted to a number as text
Decimal separator : the decimal separators contained in the text
Example: TODECIMAL(1.237.615,98;.,) returns the value 1237615.98 as a decimal number
Sorting functions
COLUMNALLORDER
The COLALLORDER function can be used in reports to virtually sort values in ascending order, from smallest to largest, regardless of any column-level structure. However, this function does not directly sort the values; instead, it simply returns a number indicating the position of the value in a list. Sorting at the column level is not possible.
Syntax: COLALLORDER([ ColumnNo ])
[ ColumnNo ]: the name of the column to be sorted
Example: COLALLORDER(QTY$) returns a value from 1 to the number of records, depending on the position where the current value would be in the sorted list regardless of any structure.
CLOUMNALLRANK / CLOUMNALLRANK
The COLALLRANK function can be used in reports to virtually order values in descending order, from largest to smallest, within a column-level structure. However, this function does not directly sort the values; instead, it simply returns a number indicating the position of the value in a list. Sorting at the column level is not possible.
Syntax: COLALLRANK([ ColumnNo ])
[ ColumnNo ]: the name of the column to be sorted
Example: COLALLRANK(QTY$) returns a value from 1 to the number of records, depending on the position where the current value would be in the sorted list regardless of any structure.
CORDER / COLORDER / COLUMNORDER / COLUMNDETAILORDER
The CORDER function can be used in reports to virtually sort values in ascending order, from smallest to largest, within a column-level structure. However, this function does not directly sort the values; instead, it simply returns a number indicating the position of the value in a list. Sorting at the column level is not possible.
Syntax: CORDER([ ColumnNo ])
[ Column No.]: the name of the column to be sorted
Example: CORDER(QTY$) returns a value from 1 to the number of records, depending on the position where the current value would be in the sorted list within an outline.
CRANK / COLRANK / COLUMNRANK / COLDETAILRANK / COLUMNDETAILRANK
The CRANK function can be used in reports to virtually sort values in descending order, from largest to smallest, within a column-level structure. However, this function does not directly sort the values; instead, it simply returns a number indicating the position of the value in a list. Sorting at the column level is not possible.
Syntax: CRANK([ ColumnNo ])
[ ColumnNo ]: the name of the column to be sorted
Example: CRANK(QTY$) returns a value from 1 to the number of records, depending on the position where the current value would be in the sorted list within an outline.
ORDER / ROWORDER / ROWDETAILORDER
The ORDER function can be used in reports to sort values in ascending order, from smallest to largest, within a row-level structure. However, this function does not directly sort the values; instead, it simply returns a number indicating the position of the value in a list. This number can then be specified for sorting.
Syntax: ORDER([ ColumnNo ])
[ ColumnNo ]: the name of the column to be sorted
Example: ORDER(QTY$) returns a value from 1 to the number of records, depending on the position where the current value would be in the sorted list within an outline.
RANK / RRANK / ROWRANK / ROWDETAILRANK
The RANK function can be used in reports to sort values in descending order, from largest to smallest, within a row-level structure. However, this function does not directly sort the values; instead, it simply returns a number indicating the value's position in a list. This number can then be specified for sorting.
Syntax: RANK([ ColumnNo ])
[ ColumnNo ]: the name of the column to be sorted
Example: RANK(QTY$) returns a value from 1 to the number of records, depending on the position where the current value would be in the sorted list within an outline.
ROWALLORDER
The ROWALLORDER function can be used in reports to sort values in ascending order, from smallest to largest, regardless of a row-level structure. However, this function does not directly sort the values; instead, it simply returns a number indicating the position of the value in a list. This number can then be specified for sorting.
Syntax: ROWALLORDER([ ColumnNo ])
[ ColumnNo ]: the name of the column to be sorted
Example: ROWALLORDER(QTY$) returns a value from 1 to the number of records, depending on the position where the current value would be in the sorted list regardless of any structure.
ROWALLRANK
The ROWALLRANK function can be used in reports to sort values in descending order, from largest to smallest, regardless of a row-level structure. However, this function does not directly sort the values; instead, it simply returns a number indicating the position of the value in a list. This number can then be specified for sorting.
Syntax: ROWALLRANK([ ColumnNo ])
[ ColumnNo ]: the name of the column to be sorted
Example: ROWALLRANK(QTY$) returns a value from 1 to the number of records, depending on the position where the current value would be in the sorted list regardless of the structure.
Cube functions
Column level
The COLUMNLEVEL function returns the current column-level outline level. If no column-level outline exists, the function always returns 0.
It is used specifically for conditions in formulas, conditional formatting, and editability conditions.
DETAILDIM / DETAILEDDIM / DIMDETAIL / DIMENSIONDETAIL / DD
The DETAILDIM function returns True if the current outline level exactly matches the specified dimension code.
Syntax: DETAILDIM( dimension code )
Dimension code : internal designation of a dimension used in the outline.
Example: DETAILDIM(D.COUNTRY) Returns True if the outline is equal to the dimension level Country.
DIMEXIST / DIMENSIONEXIST / DE
The DIMEXIST function returns True if the current outline level is below or equal to the specified dimension code
Syntax: DIMEXIST( dimension code )
Dimension code : internal designation of a dimension used in the outline.
Example: DIMEXIST(D.COUNTRY) Returns True if the outline is equal to the Country dimension level or a subordinate dimension level.
FLEXPARAM
The FLEXPARAM function is used to retrieve a specific value that was previously stored in the flexible parameters.
Syntax: FLEXPARAM([ Code ]; Field ; (Value-Key) )
[ Code ]: the name of the Flex.Param.
Field : Field in Flex.Param to be returned
(Value key) : optional, unless a value key is specified in the Flex.Param. Otherwise, all value keys must be specified in the order in which they are defined.
Example: FLEXPARAM('EXCHANGE RATE';'RATE') returns the exchange rate matching the data set from the Flex.Param. "EXCHANGE RATE".
FLEXPARAMDEBUG
The FLEXPARAMDEBUG function is identical in functionality to FLEXPARAM, but access is logged so that the values returned can be traced.
FLEXPARAMEXT
The FLEXPARAMEXT function is an extension of the FLEXPARAM function. It allows you to pass the value for each individual key field, thereby influencing the return value.
Syntax: FLEXPARAMEXT([ Code ]; Field ; (Value-Key) ; Key-Field )
[ Code ]: the name of the Flex.Param.
Field : Field in Flex.Param to be returned
(Value key) : optional, unless a value key is specified in the Flex.Param. Otherwise all value keys must be specified in the order of definition
Key field : Dimension and date keys in the order of definition in Flex.Param.
Note : If the values from the structure are to be adopted instead of a value in the key field, enter “=>” instead of the value.
FLEXPARAMINCLTOTALS
The FLEXPARAMINCLTOTALS function allows you to return a flex parameter for summary rows in an outline. This is especially useful for outlines with two or more levels.
For this to work, the same hierarchy must be entered in the Flex.Param. entries as used in the report's outline. An entry is then created for the total, populating only the higher level.
HEADER LEVEL
The HEADERLEVEL function returns the current level of a header structure. If no header structure exists, the function always returns 0.
It is used specifically for conditions in formulas, conditional formatting, and editability conditions.
LOOKUPEXT
The LOOKUPEXT function retrieves information from a related dimension independently of the data set in an outline. The value for which the information is to be searched is passed.
Syntax: LOOKUPEXT([ Code ]; Value )
[ Code ]: the internal designation of the connected dimension
Value : Value or field label to be passed
Example: LOOKUPEXT('D.WORKSTATIONGROUP.COSTCENTER';[No.]) returns the description of the cost center from the connected dimension Work Center-Cost Center.
ROW LEVEL
The ROWLEVEL function returns the current row-level outline level. If no row-level outline exists, the function always returns 0.
It is used specifically for conditions in formulas, conditional formatting, and editability conditions.
TRENDLINEAR / ROWTRENDLINEAR
The TRENDLINEAR function calculates the values for a linear trend line over a period of time. A column number is passed for this purpose.
Syntax: TRENDLINEAR( ColumnNo )
ColumnNo : Name for a column containing a value for which a trend is to be calculated
Example: TRENDLINEAR(QTY$) returns the trend value via the QTY column.
USER ROLE
The USERROLE function is used to check whether a user belongs to the specified user role. This is often used for editability conditions (e.g., entering budget data).
Syntax: USERROLE( user role )
User role : the name of the user role
Example: USERROLE('BUDGET') returns 1 if the current user has been assigned the role “BUDGET”.
Other functions
HSL
The HSL function returns a string for color coding in a chart for a specific value.
Syntax: HSL ( color code ; saturation ; luminosity )
Color code : a number from 0 to 359
Saturation : a number from 0 to 100 (percent)
Luminosity : a number from 0 to 100 (percent)
HSLA
The HSLA function returns a string for color coding a specific value in a chart. It differs from the previous function in that it also specifies an opacity parameter.
Syntax: HSL( color code ; saturation ; luminosity ; opacity )
Color code : a number from 0 to 359
Saturation : a number from 0 to 100 (percent)
Luminosity : a number from 0 to 100 (percent)
Coverage : a number from 0 to 1 (percent)
RGB
The RGB function returns a string for color coding a specific value in a chart. The color code is encoded in hexadecimal.
Syntax: RGB ( color value red ; color value green ; color value blue )
Color value red : a number from 0 to 255
Color value green : a number from 0 to 255
Color value blue : a number from 0 to 255
RGBA
The RGB function returns a string for color coding a specific value in a chart. The color code is encoded in hexadecimal. The difference from the previous function is that it also specifies an opacity parameter.
Syntax: RGB( color value red ; color value green ; color value blue ; opacity )
Color value red : a number from 0 to 255
Color value green : a number from 0 to 255
Color value blue : a number from 0 to 255
Coverage : a number from 0 to 1 (percent)