Functions¶

Use functions to perform mathematical operations and manipulations to the report data. There are different formulas for string manipulations, date and time and numbers. These are described below:

Aggregate functions
String functions
Math functions
Date and Time functions
Operators
Other Functions

Aggregate Functions¶

Performs different mathematical operations.

Function	Description	Parameters	Example
sum()	Returns the sum of a repeating numerical item/value within a table	A reference to the item for which you would like to calculate a sum	`sum(tns:AMOUNT[.!=''])` will sum all amount elements within the iteration and return the result as a number. The "[.!='']" part is added by the framework so that the sum will be valid even if one row in the iteration doesn't contain an amount element or the amount is null
count()	Returns the count of a repeating item within a table	A reference to the item you would like to count.	`count(tns:ROW_NO)` will count all ROW_NO elements within the iteration and return the number of occurences.
avg()	Returns the average value of a repeating numerical item/value within a table.	A reference to the item for which you would like to calculate an average.	`vldtaggr:average(tns:SALARY)` will calculate an average salary when looping over a number of employees.
max()	Returns the maximum of a repeating numerical item within a table.	A reference to the item you would like to get the maximum value for.	`math:max(tns:SALARY)` will return the maximum salary when looping over a number of employees.
min()	Returns the minimum of a repeating numerical item within a table.	A reference to the item you would like to get the minimum value for.	`math:min(tns:SALARY)` will return the minimum salary when looping over a number of employees.

String Functions¶

Performs string manipulations.

Function	Description	Parameters	Example
strCase(case,list)	Extracts and returns one string from a comma-delimited list of strings, based on the value given in case.	case: Any positive number including zero (0). list: A string composed of comma delimited sub strings. For example: 'dog,cat,horse,mouse,man'.	`strCase(tns:TYPE,'dog,cat,horse,mouse,man')` In this example, TYPE is an item with a data type of number. The function strCase returns one of the following depending on the value of TYPE. Case Substring returned 0 dog 1 cat 2 horse 3 mouse 4 man
concat (string1, string2)	Concatenates (combines) two strings into one single string value.	string1: The first part of the string. String2: The second part of the string (the one to be appended to the first part).	`concat('ABC', 'XYZ')` Would return the string 'ABCXYZ'.
strCompare(string1, string2)	Compares the contents of string1 to the contents of string2 and returns a number.	string1 string2 If, string1 < string2 returns a value less than zero (<0) string1 = string2 returns a zero (0) string1 > string2 returns a value greater than zero (>0)	`StrCompare('dog','cat')` Returns a value of 1, indicating that ‘dog’ is greater than ‘cat’ if it performs an ASCII sort.
StrEqualPad(string, length, char)	Maintains the string length at the specified size by either truncating the string, or adding the specified characters to both side of the string equally.	string: A character value you specify. StrEqualPad adds the specified characters to both left and right sides of the string equally or truncates characters to make this string the length you want. length: Enter the total length of string including padded characters. char: The character to be used as padding. This could be a blank space or any other character as '*', ':', '#' etc.	`StrEqualPad(JOE,6,'')` In this example, the StrEqualPad would return 'JOE'. Function would pad the contents of the input item JOE with '' characters equally to the left and right of the string to make it 9 characters in length
strIff (value,string1, string2,string3)	Returns a specific string based on the value provided by the value parameter. You can specify a maximum of three strings.	value: A number value you specify, that identifies the string to be returned. string1: A character value you specify. string2: A character value you specify. string3: A character value you specify. Returns If: value < 0 string1 is returned value = 0 string2 is returned value > 0 string3 is returned	`StrIFF(2,'name','address','phone')` In this example, the StrIFF function returns the contents of the input item phone.
strLeft(string,length)	Extracts and returns the specified number of characters, starting the extraction from the left side of the input string.	string: A character value that you specify. length: Any positive number	`StrLeft('Good Morning',4)` Returns a string value 'Good'.
*stringLength(length)*	Extracts and returns the specified number of characters, starting the extraction from the left side of the input string.	string: A character value that you specify. length: Any positive number	`StrLength('cat')` Returns the number 3.
*strLower(string)*	Converts and returns the lower case representation of the supplied string.	string: The string you would like to convert to lower case.	`vldtstr:string_lower('Good Morning')` Would return the string 'good morning'.
*strLTrim(string)*	Returns the string with the leading white spaces (blanks or tabs) removed.	string: A string of characters that you specify. StrLTrim removes the leading blank spaces from this string.	`StrLTrim(' climbed a tree')` In this example, the StrLTrim function would return the string 'climbed a tree'.
*strLTrim(string,char)*	Returns the string with the specified leading characters removed.	string: A string of characters that you specify. StrLTrim removes the leading blank spaces from this string. char: The character string to be removed. This could be a whitespace or any other character.	`StrLTrim('climbed a tree', '*')` In this example, the StrLTrim function would return the string 'climbed a tree'.
*strMid(string,start,* *length)*	Returns a substring of the input string, starting at the supplied starting point and of the specified length.	string: A character value you specify. StrMid extracts a portion of this string. start: Offset position for the start of the substring. length: Number of characters to be extracted.	`StrMid('who,what,when,where,why',4,4)` In this example, the StrMid function would return the string 'what'.
StrLPad(string, length, char)	Maintains the string length at the specified size by either truncating the string, or adding the specified characters to the left of the string.	string: A character value you specify. StrLPad adds the specified characters to the left of the string or truncates characters to make this string the length you want. length: Enter the total length of string including padded characters. char: The character to be used as padding. This could be a blank space or any other character as '*', ':', '#' etc.	`StrLPad(JOE,6,'')` In this example, the strLPad would return '*JOE'. Function would pad the contents of the input item JOE with '' characters to the left of the string to make it 6 characters in length.
*strProper(string)*	Formats each word in the string so that it starts with a capital letter and is followed by lower case letters.	string	`StrProper('title page')` In this example, the StrProper function capitalizes the first letter of each word in the string. The resulting output appears as 'Title Page'.
*strRepeat(string,number)*	This function takes the string you entered, and repeats it the specified number of times. If you want one or more spaces between each repetition of the value, then you must be sure to include those spaces in the string that you enter.	string: String value to be repeated. number: Number of times to repeat string	`StrRepeat('Line',4)` In this example, the StrRepeat function outputs a string that looks like “LineLineLineLine”.
*strReplace(original,start,* *length,replacement)*	Returns original where the characters starting at start are replaced with length number of characters from replacement.	original: String value to be changed. start: Starting offset position in original. length: Number of characters to be replaced in original from replacement. replacement: Replacement string.	`StrReplace('your property',0,4,'our ')` In this example, the StrReplace function changes 'your property' to 'our property'.
*strReplaceAll(string,old,* *replacement)*	Replace all occurrences of an existing "pattern" (old) with a new value (replacement) in a string (str).	string: Original string. old: String value to be changed. replacement: Replacement string.	`strReplace('Joe was here', 'was', 'was not')` In this example, the StrReplaceAll function changes 'Joe was here' to 'Joe was not here'.
*strRight(string,length)*	Extracts and returns the specified number of characters, starting the extraction from the right side of the input string.	string: String value to be extracted. length: Any positive number	`StrRight('Good Morning',7)` Returns a string value of 'Morning'.
*stringRTrim(string)*	Returns string with all trailing white space (blanks or tabs) removed.	string: A character value you specify. StrRTrim removes the trailing blank spaces from this string.	`StrRTrim(' short addresses ')` In this example, the StrRTrim function would return the string ' short addresses'. Only trailing blank spaces are trimmed.
*stringRTrim(string,char)*	Returns string with all trailing characters of the specified type removed.	string: A character value you specify. This function removes the trailing characters specified by char from the given string. char: The character to be removed from the string.	`StrRTrim('short addresses:', ':')` In this example, the StrRTrim function would return the string 'short addresses'. The trailing colon ':' is removed.
StrRPad(string, length, char)	Maintains the string length at the specified size by either truncating the string, or adding the specified characters to the right of the string.	string: A character value you specify. StrRPad adds the specified characters to the right of the string or truncates characters to make this string the length you want. length: Enter the total length of string including padded characters. char: The character to be used as padding. This could be a blank space or any other character as '*', ':', '#' etc.	`StrRPad(JOE,6,' ')` In this example, the StrRPad function would pad the contents of the input item JOE with blank spaces to the right of the string to make it 6 characters in length
*StrScan(string,part)*	This function searches for a specified character or characters and returns its location (offset) in the input string starting at 0. If it does not find the part, the function returns a value of -1.	string: String being scanned. part: Characters or phrases you are looking for.	`StrScan('JohnMaryBethDavid','Beth')` In this example, the StrScan function would return the number 8, showing the offset of the first occurrence of the part 'Beth'.
*StrSpaces(length)*	Returns a string with the specified number of blanks.	length: Length of the string to be returned	`StrSpaces(5)` returns the string ‘ ’ (a string consisting of 5 blanks / white spaces).
*StrTranslate(string,list1,* *list2)*	Finds the value specified by type in list1. The function takes the position of the item in list1 and finds and returns the item in list2 that is in the same position.	type: The comparison variable. Must be a string data type. list1: A string composed of comma delimited look-up values. The contents of type are compared to this list. For example, 'N,S,W,E'. list2: A string composed of comma delimited values, one of which is returned by the function. For example, 'North,South, West, East'.	`StrTranslate (region, 'N,S,W,E', 'North, South, West, East')` In this example, region is an input item with a data type of string. The StrTranslate function returns one of the following depending on the value of type. type substring returned N North S South W West E East
StrTranslate(type, list1, list2, default)	Finds the value specified by type in list1 and finds and returns the item in list2 that is in the same position. If not found returns the default value.	type: The comparison variable. Must be a string data type. list1: A string composed of comma delimited look-up values. The contents of type are compared to this list. For example, 'N,S,W,E'. list2: A string composed of comma delimited values, one of which is returned by the function. For example, 'North, South, West, East'. default: A default string value that will be returned if matching item is not found in the list.	`StrTranslate (region, 'N,S,W,E', 'North, South, West, East','North')` In this example, region is an input item with a data type of string. The StrTranslate function returns one of the following depending on the value of type. If a matching value for type is not found in the list, 'North' will be returned as a default value. type substring returned N North S South W West E East
*StrTrim(string)*	Returns the string with all extra white space (trailing, leading, blanks or tabs) removed and leaving only one space between each word.	string: A character value you specify. StrTrim removes extra white space from this string.	`StrTrim(' short addresses ')` In this example, the StrTrim function would return the string 'short addresses'. All extra blank spaces are trimmed.
*StrTrim(string,char)*	Returns string with all specified characters (both trailing and leading) removed from the string.	string: A string value you specify. This function removes the character specified by char from this string. char: The character to be removed from the string	`StrTrim('####short addresses####','#')` In this example, the StrTrim function would return the string 'short addresses'. All leading and trailing '#' characters are trimmed.
*StrUpper(string)*	Converts and returns the upper case (capital) representation of the supplied string.	string: The string you would like to capitalize.	`vldtstr:string_upper('Good Morning'')` Would return the string 'GOOD MORNING'.
New line	Adds a new line to the XML output. See below for more details.	No parameters. It is written as $new_line	`concat ('Martin', concat($new_line,'Olsson'))` returns 'Martin Olsson'

New line¶

This is a String Constant which adds an empty line (also known as a linefeed) to the XML output. The New line constant is represented by $new_line. This can be called inside other string formulas by passing it as any other string parameter. For example if you wish to add/concatenate two strings and add a new line between the two strings, this is the way to do it. Say you need to concatenate 'STRING1' and 'STRING2' using the concat function and display the output in two lines, STRING1 in one line and STRING2 in a new line. You can achieve this output by combining the concat function together with a New line constant as shown below.

concat('STRING1',concat($new_line,'STRING2'))

In the above formula, New line constant is taken in as any other string parameter by the concat function, in this case the string happens to be a new line. The output of the inner concat function is a string that has an empty line and 'STRING2' on the next line. When this is appended to 'STRING1' by the outer concatenate function you can achieve the following output.

Math functions¶

Performs various operations on numbers.

Function	Description	Parameters	Example
*Abs(num)*	Returns the absolute value of number	number	`Abs(NetBalance)` Find the absolute value of the input item NetBalance. If NetBalance is equal to -5689.05, the NumberAbsolute function would return a value of 5689.05.
*NumIff(nInput,number1,* *number2,number3)*	Returns one of number1, number2, or number3, depending on the value returned by the nInput parameter	nInput number1 number2 number3 Returns If nInput < 0 number1 is returned nInput = 0 number2 is returned nInput > 0 number3 is returned	`NumIFF(2,22,55,77)` In this example, the NumIFF function returns the value 77.
*NumRound(number,* *precision)*	Returns the input number rounded to precision decimal places.	number: The number value which is rounded to the required degree of precision. precision: The number of decimal places to the right of the decimal point.	`NumRound(980.56753,3)` In this case the function NumRound returns the number 980.568.
*NumNVL(number)*	If input value is not a number or null, the value zero (0) is returned	number: Input variable	`vldtmath:nvl(tns:OPTIONAL_VALUE)` If in this case the OPTIONAL_VALUE element in the XML doesn't contain any value, the value 0 will be returned. If the XML element contains a proper numerical value, that value will be returned
*NumTruncate(number,* *precision)*	Truncates the input number at the specified number of decimal places. No rounding of values occurs.	number: The number to be truncated. precision: Specify the number of decimal places to the right of the decimal point.	`NumberTruncate(89432.563,1)` The function returns a number value that prints as 89432.5. This function does NOT round the number, it only truncates the number.
*NumPower(nNumber,* *nRaised)*	Returns the input nNumber raised to the power of nRaised. A negative number returns NULL.	nNumber: The number value you want raised to a specified power. nRaised : The number value of the power.	`Power(100,2)` Returns 100 to the power of 2, or 10000.
*NumSqrt(number)*	Returns the correctly rounded positive square root of a numerical value	number: The number of which you would like to calculate the square root.	`vldtmath:numSqrt(2)` returns 1.41421356...
GetTotal(list1,list2)	Returns the total of the Sum values for the given two nodes.	list1: first node set in which to calculate the sum. list2: second node set to calculate the sum.	`vldtmath:getTotal(list1,list2)` If the sum of list1 is 50 and the sum of list2 is 70 then the function returns the total sum of list1 and list2 (50+20) as 70.
AmountToWords (amount, languageCode, currency)	Returns the amount along with the currency as a translated word	amount: The number to be translated in to words. languageCode: The ifs language code used in runtime. This value can be fetched from the LANG_CODE entry listed under FORMATTING_OPTIONS. One could also use any other XPath value which returns a valid language code. It is also possible to hardcode this value if necessary. currency: Currency to be displayed along with the amount. You could either hardcode this value or use a XPath value which returns a valid currency code supported by IFS.	Using tns:LANG_CODE from FORMATTING_OPTIONS: `vldtmath:AmountToWords (2500.50,/tns:PRINT_CHECK_REP_REQUEST/tns:PROCESSING_INFO/ tns:FORMATTING_OPTIONS/tns:LANG_CODE,tns:CURR_CODE)` Using custom XPath for languageCode: `vldtmath:AmountToWords(2500.50,tns:LANGUAGE,tns:CURR_CODE)` Hardcoded languageCode: `vldtmath:AmountToWords(2500.50,'en',tns:CURR_CODE)` If runtime values fetched for languageCode is 'en' and currency is 'USD' in the above examples the functions would return two thousand five hundred dollars and fifty cents.
FormattedNumber(number_format)	Returns the number as a string after formatting it according to the formatting criteria you specify. See below for more details.	number_format: number together with the format string. The formatting can be selected from the Format Number dialog shown when selecting a number field.	`concat( FormattedNumber(number), string2)` In this example the concat function would return the formatted number as a string append with the string2 parameter.

FormattedNumber(number_format)¶

If one needs to use a formatted number as a string in a string manipulation operations, for an example in the concat function, then it is made possible by FormattedNumber function.

Say, you need to concatenate the description 'Order Number is:' with a number field and display the number in currency format. Use the concat function to append the description with the number and FormattedNumber to format the number and to return it as a string. The following example displays how it works:

concat('Order Number is:', format:number(tns:ORDER_ID,$language,$country,$variant,'#BASEONLOCALE#','#BASEONLOCALE#','#BASEONLOCALE#','#BASEONLOCALE#','#BASEONLOCALE#','#BASEONLOCALE#','#BASEONLOCALE#','-1.1','true','false'))

Double click on the FormattedNumber function, this will add the function call format:number( ) to the formula editor. Select a number from the Fields section of the Function Editor. When you double click on a number, the Format Number Dialog will show up, in which you specify the type of formatting you would like to have. After setting the formatting click Ok on the Format Number Dialog to continue. The number together with the selected formatting will be written on the function editor.

Remember that formatted numbers cannot be used inside other Number functions.

Date & Time functions¶

Manipulates date and time data fields.

Function	Description	Description	Example
*CurrentDateTime( )*	This function obtains the current date from the system. It obtains the date/time that the server defines. Ensure that the clock is set correctly. Use the date/time formatting features to format the date and time properly.	None	`CurrentDateTime( )` Will return the current date and time as a string in the XML specific date/time format, for instance 2004-05-29T18:36:17. The returned date/time value can then be formatted using the normal date/time formatting options in Report Designer.
*DateAddDay(startDate,* *days)*	Adds or subtracts the specified number of days from the indicated start date. Returns a date that is StartDate with days added or subtracted from it.	StartDate: Specify the start date here. days: Add to the StartDate to produce the new date. If days is a negative value, this function subtracts the specified number of days from StartDate.	`DateAddDay(startDate,67)` Will return the startDate plus 67 days as a string in the XML specific date/time format, for instance 2004-05-29T18:36:17. If startDate is 2000-07-11, the date returned will be 2000-09-??. The returned date/time value can then be formatted using the normal date/time formatting options in Report Designer.
*DateConstruct(year,month,* *day,hour,minute,second)*	This function returns a date/time value that it constructs from the inputs you provide.	All parameters must be provided in the order shown, and must have a number data type. year month day hour minutes seconds	`DateConstruct (1974,05,29,20,05,30)` The date is returned as a string with the XML specific date/time format (i.e. 1974-05-29T20:05:30). The date/time value can then be formatted using the normal date/time formatting options in Report Designer.
*DateDay(date/time)*	Extracts and returns a number representation of the day of the month (1 to 31) from the input Date/Time value you provided.	date/time	`DateDay('2004-05-29T15:26:31')` In this example, the function returns the number 29.
DateMinute(date/time)	Extracts and returns a number representation of the minute (0 to 59) from the input date/time value you provide.	date/time	`DateMinute('2004-05-29T15:26:31')` In this example the function returns the number 26.
DateSecond(date/time)	Extracts and returns a number representation of the second (0 to 59) from the input Date/Time value you provide.	date/time	`DateSecond('2004-05-29T15:26:31')` In this example the function returns the number 31.
*DateHour(date/time)*	Extracts and returns a number representation of the hour (0 to 23) from the input Date/Time value you provided.	date/time	`DateHour('2004-05-29T15:26:31')` In this example the function returns the number 15.
*DateYear(date/time)*	Extracts and returns a number representation of the year from the input Date/Time value you provided.	date/time	`DateYear('2004-05-29T15:26:31')` In this example, the function returns the number 2004.
*DateMonth(date/time)*	Extracts and returns a number representation of the month (1 to 12) from the input Date/Time value you provided.	date/time	`DateMonth('2004-05-29T15:26:31')` In this example, the function returns the number 5.
*DateMonthBegin(date/time)*	Evaluates the date/time parameter and returns a date/time value that is equal to the first day of the month and year specified by your parameter.	date/time	`DateMonthBegin('2004-05-29T15:26:31')` In this example, the function returns the date 2004-05-01.
*DateMonthEnd(date/time)*	Evaluates the date/time parameter and returns a date/time value that is equal to the last day of the month and year specified by your parameter.	date/time	`DateMonthEnd('2004-05-29T15:26:31')` In this example, the function returns the date 2004-05-31.
*WeekDay(date)*	Returns the week day of the date as a number (1 to 7), based on the supplied locale (i.e in some countries day one (1) is Sunday, in other parts of the world Monday is considered the first day of the week	date: The date of which you want the week day returned as a number.	`WeekDay('1998-09-17')` 1998-09-17 was a Thursday. The function would return 4 using a Swedish locale and 5 using a US-English locale.
*DateIff(nInput, date1,date2,date3)*	Returns one of date1, date2, or date3, depending on the value returned by the nInput parameter	nInput date1 date2 date3 Returns If: nInput < 0 date1 is returned nInput = 0 date2 is returned nInput > 0 date3 is returned	`DateIFF(2, date1, date2, date3)` In this example, the DateIFF function would return the value of the date3 variable.
FormattedDateTime(date_and_date_format)	Returns the date as a string after formatting it according to the formatting criteria you specify. See below for more details.	date and date format: date together with the format string. The formatting can be selected from the Format Date dialog shown when selecting the date field.	`concat(string1, concat(FormattedDateTime(date_and_date_format)` In this example the two functions, concat and FormattedDateTime will return the formatted date appended to string1.

FormattedDateTime(date_and_date_format)¶

One can use String functions such as concat to append strings and dates together. However, if one needs to use a formatted date when performing other string manipulations, then this is made possible by the FormattedDateTime function. To explain how this works, let's look at the following example.

Say, you need to concatenate the description 'Order Date is:' with a date field and display the date in medium format and the time in short format. Use the concat function to append the description with the date and FormattedDateTime to format the date as medium and short as shown below.

concat('Order Date is:', format:formatDateTime(tns:ORDER_DATE,$language,$country,$variant,'medium','none','tns:ORDER_DATE'))

Double click on the FormattedDateTime function, this will add the function call format:formatDateTime( ) to the formula editor. Select the date from the Fields section of the Function Editor. When you double click on the date, the Format Date Dialog will show up, in which you specify the type of formatting you'll like to have. After setting the formatting click Ok on the Format Date Dialog to continue. The date together with the selected formatting will be written on the function editor.

Remember that formatted dates cannot be used inside other Date & Time functions.

Operators¶

Helps to perform calculations on numbers.

Function	Description	Example
+ operator	Addition operator. Use to perform an addition between two numbers.	`2 + 1` would return 3
*- operator*	Subtraction operator. Use to subtract one number from another.	`2 - 1` would return 1
* operator	Multiplication operator. Use to multiply two numbers.	`2 * 2` would return 4
*div operator*	Division operator. Use to divide two numbers. Note that the normal division character '/' can not be used since this would make the XML/XPath expressions invalid.	`2 div 2` would return 1
*mod operator*	Modulus operator. Use to get the modulo (reminder/rest) of the division between two numbers.	`14 mod 5` would return 4

Other Functions¶

These two functions can be used to add text effects

Function	Description	Parameters	Example
Rotate Text (text, angle)	Rotates the given text by specified angle. Makes it possible to display text in different directions.	text: The text to be rotated. angle: Angle in which to rotate the specified text.	`Rotate Text('Martin',90);` the function would return the text rotated clockwise by 90 degrees as:
Vertical Text(text)	Returns the specified text vertically from top to bottom.	text: The text to be written vertically.	`Vertical Text('Martin');` will return the text 'Martin' written from top to bottom as: