About functions

Many functions available in the Analytical report manager process the data selected from the data source and return the values to be used in the report.

To use functions in the formula, you can enter them directly in the formula editing area or select them from the list of functions provided by the.

The groups of functions in the formulas are described below.

Conversion functions

You use conversion functions, summarised in the following table to convert data from one data type to another.

Function	Description and examples
CBool(x)	Converts an expression defined in a function argument into the Boolean expression. If the expression is zero, False is returned; otherwise, True is returned. Example: `=CBool(A11+B11-C11*0.05)` (where A11, B11 and C11 are the links used in a function argument)
CDate(x)	Converts an expression defined in a function argument to a value of the Date type. The CDate function argument should be a valid date expression. CDate recognises date formats according to the locale setting of the system. Example: `=CDate(A2-B2)` (where A2 and B2 are the links used in a function argument)
CStr(x)	Converts an expression defined in a function argument to a string. If the CStr function argument equals null, CStr returns a run-time error; otherwise, it returns a string of characters. Example: `=CStr(A12)` (where A12 is the link used in a function argument)
CDbl(x)	Converts an expression defined in a function argument to a value of the Double type. Example: `=CDbl(A12/B2)` (where A12 and B2 are the links used in a function argument)
CSng(x)	Converts an expression defined in a function argument to a value of the Single type. If the expression defined in the function argument lies outside the acceptable range for the Single type, an error occurs. Example: `=CSng(A12)` (where A12 is the link used in a function argument)
CDec(x)	Converts an expression defined in a function argument to a value of the Decimal type. Example: `=CDec(A12*0.15)` (where A12 is the link used in a function argument)
CInt(x)	Converts an expression defined in a function argument to a value of the Integer type. Example: `=CInt(A10)` (where A10 is the link used in a function argument)
CShort(x)	Converts a numeric value to a value of the Short type. Example: `=CShort(B2)` (where B2 is the link used in a function argument)
CLong(x)	Converts a numeric value to a value of the Long type. Example: `=CLong(B12)` (where B12 is the link used in a function argument)

Text functions

Text functions, described in the following table, are used to perform operations with the text strings.

Function	Description and Examples
LTrim(string)	Removes all leading spaces or parsing characters from the specified character expression, or all leading zero bytes from the specified binary expression. Example: `=LTrim(CStr(A12))` (where A12 is the link used in a function argument)
RTrim(string)	Removes all trailing spaces or parsing characters from the specified character expression, or all trailing zero bytes from the specified binary expression. Example: `=RTrim(CStr(A12))` (where A12 is the link used in a function argument)
Trim(string)	Removes all trailing spaces or parsing characters from the specified character expression, or all trailing zero bytes from the specified binary expression. Example: `=Trim(CStr(A12))` (where A12 is the link used in a function argument)
Format(format, argument(s) )	Replaces the format item in a specified formatting string ( format ) with the text equivalent of the arguments ( arguments ). Example: `=Format('Currency:........ {0:C}; Account:........ {1:N}', A1, B1)` (where A1 and B1 are the links used as a function arguments; 0, 1 are the specifiers indicating where the arguments will be inserted; C is the currency format specifier; and N is the number format specifier)
UCase(string)	Returns a string that has been converted to upper case. The string argument is any valid string expression. If string contains a null value, the null value is returned. Example: `=UCase(CStr(A12))` (where A12 is the link used in a function argument)
LCase(string)	Returns a string that has been converted to lower case. The string argument is any valid string expression. If string contains a null value, the null value is returned. Example: `=LCase(CStr(A12))` (where A12 is the link used in a function argument)
InStr(string, findString)	Returns the position of the first occurrence of one string ( findString ) within another ( string ). Example: `=InStr(CStr(A12), 'rur')` (where A12 is the link used in a function argument)
InStrRev(string, findString )	Returns the position of the last occurrence of one string ( findString ) within another ( string ), starting from the right side of the string. Example: `=InStrRev(CStr(A12), 'rur')` (where A12 is the link used in a function argument)
Len(string )	Returns an integer containing either the number of characters in a string or the nominal number of bytes required to store a variable. Example: `=Len(CStr(A12))` (where A12 is the link used in a function argument)
Left(string, length )	Returns a string containing a specified number of characters from the left side of a string. If string contains the null value, the null value is returned. Example: `=Left(CStr(A12), 3)` (where A12 is the link used in a function argument)
Right(string, length )	Returns a string containing a specified number of characters from the right side of a string. If string contains a null value, the null value is returned. Example: `=Right(CStr(A12), 3)` (where A12 is the link used in a function argument)
Replace(string, oldValue, newValue )	Returns a string in which a specified sub string ( oldValue ) has been replaced with another sub string ( newValue ). Example: `=Replace(CStr(A12), 'rur', 'eur')` (where A12 is the link used in a function argument)
PadLeft(string, width, paddingChar )	Right-aligns the characters in a specified string ( string ), padding with the specified characters ( paddingChar ) on the left for a specified total width ( width ). Example: `=PadLeft(CStr(A12), 3, '%')` (where A12 is the link used in a function argument)
PadRight(string, width, paddingChar )	Left-aligns the characters in a specified string ( string ), padding with the specified characters ( paddingChar ) on the right for a specified total width ( width ). Example: `=PadRight(CStr(A12), 3, '%')` (where A12 is the link used in a function argument)

Math functions

Mathematical functions, described in the following table, perform calculations, usually based on input values provided as arguments, and return numeric values.

Function	Description and Examples
Abs(x)	Returns the absolute value of a number. Example: `=Abs(A10-B10)` Here A10 and B10 are the links used in a function argument.
Floor(x)	Returns the largest integer that is not greater than the argument. Example: `=Floor(A10-A12)` Here A10 and A12 are the links used in a function argument.
Ceiling(x)	Returns the smallest integer that is not less than the argument. Example: `=Ceiling(A10-C11)` Here A10 and C11 are the links used in a function argument.
Round(x, decimals )	Returns a numeric expression, rounded to the specified precision ( decimals ). Example: `=Round((A10-B12), 5)` Here A10 and B12 are the links used in a function argument, and 5 is the number of digits after the decimal separator.
Min(x, y)	Returns the smaller of the two values. Example: `=Min(A10, A12)` `=Min(A10, 12)` Here A10 and A12 are the links used as function arguments)
Max(x, y)	Returns the greater of the two values. Example: `=Max(A12, A14)` `=Max(A12, 240)` Here A12 and A14 are the links used as function arguments.
Pow(x, power )	Computes the value of x raised to the specified power ( power ). Example: `=Pow((A12, 2))` Here A12 is the link used as a function argument, and 2 is the power index.

DateTime functions

The DateTime functions (described below) perform operations on input values and return string, numeric, or DateTime value results.

Function	Description and Examples
Now()	Returns the current date and time according to the system date and time on the local computer. Example: `=Now()`
Today()	Returns the current date according to the system date and time on the local computer. Example: `=Today()`
NowUTC()	Returns the current date and time according to the user’s time zone. The system gets the user’s time zone from the following sources, which are ordered by the priority from the highest to the lowest: User’s preferences specified in the User settings (SM203010) window. The employee calendar specified in the Work calendar (CS209000) window and selected for the user’s employee in the Employees (EP203000) window. Example: `=NowUTC()`
TodayUTC()	Returns the current date according to the user’s time zone. The system gets the user’s time zone from the following sources, which are ordered by the priority from the highest to the lowest: User’s preferences specified in the User settings (SM203010) window. The employee calendar specified in the Work calendar (CS209000) window and selected for the user’s employee in the Employees (EP203000) window. Example: `=TodayUTC()`
DateAdd(date, interval, number )	Returns the new date calculated as a date parameter to which the specified time interval has been added. The interval argument is a string expression that is the interval to be added. This argument can have the following values: yyyy year (a number of years will be added to a date ) m month (a number of months will be added to a date ) y day of year (same as day ) d day (a number of days will be added to a date ) h hour (a number of hours will be added to a date ) n minute (a number of minutes will be added to a date ) s second (a number of seconds will be added to a date ) The number is the numeric expression that is the number of intervals to be added. The numeric expression can either be positive, for dates in the future, or negative, for dates in the past. The date argument is the date to which interval is added. Example: `=DateAdd(CDate('31/01/1995'), 'm', -2)` `=DateAdd(Today(), 'y', 3)` `=DateAdd(Now(), 'd', 1)`
Year(date )	Returns the year component of the date. Example: `=Year(Now())`
Month(date )	Returns the month component of the date. Example: `=Month(Now())`
Day(date )	Returns the day component of the date. Example: `=Day(Now())`
DayOfWeek(date )	Returns the day of week for a date. Example: `=DayOfWeek(Today())`
DayOfYear(date )	Returns the day of the year for a date. Example: `=DayOfYear(Now())`
Minute(date )	Returns the minutes for a date. Example: `=Minute(Now())`
Second(date>)	Returns the second component of the date. Example: `=Second(Now())`

Other functions

The Other functions group includes the following functions.

Function	Description and examples
IIf(expression, truePart, falsePart )	Returns one of two values, depending on the evaluation of an expression. If the expression evaluates to True, the function returns the truePart value; otherwise, it returns falsePart value. Example: `=IIf((A10-B10)<>0), CStr(A12), 'No data available')` (where A10, A12, and B10 are the links used as a function arguments)
Switch(expression_1, value_1, expression_2, value_2,…)	Returns one of the values, depending on the evaluation of the expressions in the parameter. The function returns the value_n, which corresponds to the first expression in the enumeration that evaluates to True. Example: `=Switch(((A10-B10)<>0), A12, ((A10-B10)>0), B35)`
IsNull(value, nullValue )	Returns nullValue, if value is NULL; otherwise, returns value. Example: `=IsNull(A10, '0')` (if `A10` is NULL, returns 0, otherwise returns the `A10` value)
Sum(from, to)	Returns the sum of the values in the specified interval. Example: `=Sum('A11','A100')` (where A11 and A100 are the links used in a function argument)
Sort(from, to, column )	Returns the values in the specified range of rows in the specified column sorted in ascending order. Example: `Sort('0100','0145','B')` (the values in rows from ‘0100’ to ‘0145’ in the ‘B’ column will be sorted in ascending order)
SortD(from, to, column )	Returns the values in the specified range of rows in the specified column sorted in descending order. Example: `SortD('0100','0145','B')` (the values in rows from ‘0100’ to ‘0145’ in the ‘B’ column will be sorted in descending order)

Application-specific functions

This functions are specific for Visma Net.

Function	Description and Examples
ExtToInt(object field, object value )	Converts the external format of the object field parameter into the internal object presentation (for example, converts AccountCD to AccountID ). Both arguments of the ExtToInt function must have the same data type.
ExtToUI(object field, object value )	Converts the external format of the object field parameter into the UI format. Both arguments of the ExtToUI function must have the same data type.
GetDefExt(object field)	Gets the default value of the object field parameter in the external format.
GetDefInt(object field)	Gets the default value of the object field parameter in the internal format.
GetDefUI(object field)	Gets the default value of the object field parameter in the UI format.
GetDescription(object field, object value )>	Returns the description of the object field parameter as it is defined in the PXSelectorAttribute. Both arguments of the GetDescription function must have the same data type. Note: If you use this function for subaccounts, the subaccount description is not available if the On-the-fly entry option is specified for subaccounts in the Segment keys (CS202000) window.
GetDisplayName(object field)	Returns the localised name of the object field parameter.
GetFormat(object field)	Returns the data type of the object field parameter.
GetMask(object field)	Returns the mask of the object field parameter.
IntToExt(object field, object value )	Converts the external format of the object field parameter into the internal object presentation (for example, converts AccountID to AccountCD ). Both arguments of the IntToExt function must have the same data type.
IntToUI(object field, object value )	Converts the internal format of the object field parameter into the UI format. Both arguments of the IntToUI function must have the same data type.
UIToExt(object field, object value )	Converts the UI format of the object field parameter into the external object format. Both arguments of the UIToExt function must have the same data type.
UIToInt(object field, object value )	Converts the UI format of the object field parameter into the internal object format. Both arguments of the UIToInt function must have the same data type.
FormatPeriod(object period )	Performs ExtToUI data conversion for the object period parameter. This function is used to get the period defined by the report @StartPeriod and @EndPeriod dates.
FormatPeriod(object period, object period shift )	Performs ExtToUI data conversion for the object period parameter with a time shift defined by object period shift argument. This function is used to get the period defined by the report @StartPeriod and @EndPeriod dates.
FormatYear(object period )	Performs ExtToUI data conversion for the object period parameter year part. This function is used to get the period defined by the report @StartPeriod and @EndPeriod dates.
FormatYear(object period, object period shift )	Performs ExtToUI data conversion for the object period parameter year part with a time shift defined by object period shift argument. This function is used to get the period defined by the report @StartPeriod and @EndPeriod dates.

Parent topic

Formulas - overview

Concepts

About parameters