This is the rest of my journey through Acumatica Report Designer expressions.
TL:DR – This article is the second part of a trip through Acumatica Report Designer expressions and how you can use them to create effective exciting reports. These are less commonly used expressions for my reports. The most used expressions in my Acumatica Reports are over in Acumatica Report Designer Variables and Expressions.
Contents
- Less commonly used functions
- Type Conversion functions
- CBool
- CDate
- CStr
- CDbl
- CSng
- CDec
- CInt
- CShort
- CLong
- Functions - Text
- LTrim
- RTrim
- Trim
- Format
- UCase
- LCase
- InStr
- InStrRev
- Len
- Left
- Right
- Replace
- PadLeft
- PadRight
- Substring
- Concat
- Functions - Program Shortcuts
- NullIf
- Assign
- Functions - DateTime
- Year
- Month
- Day
- DayofWeek
- DayOfYear
- Year
- Hour
- Minute
- Second
- MonthName
- DayOrdinal
- Functions - Math
- Abs
- Floor
- Ceiling
- Round
- Min
- Max
- Pow
- Functions - Aggregates
- Avg
- Sum
- Count
- Max
- Min
- Next
- Prev
- First
- Last
Less commonly used functions
I haven't really had cause to use many of these, and I wonder therefore what some of them are doing in the product given that they occupy budget for maintenance, testing and documentation and implementation consulting. One day however, I am sure one of them will be instrumental in saving a report from obvlivion!
Type Conversion functions
These functions convert one data type to another.
CBool
CBool( x )
This function returns false if the expression is 0 and true if the expression is not zero.
=[CBool( 0 +1 )
CDate
CDate( x )
This converts the expression to a Date..
=CDate( [@MonthEnd] )
CStr
CStr( x )
This converts the expression to a String. It won't work with a Null!
=CDate( [@MonthEnd] )
CDbl
CDbl( x )
This converts the expression to a Double type.
=CDbl( [@PageCount] )
CSng
CSng( x )
This converts the expression to a Single type and returns an error if out of range.
=CSng( [@PageCount] )
CDec
CDec( x )
This converts the expression to a Decimal type.
=CDec( [@PageCount] )
CInt
CInt( x )
This converts the expression to an Integer.
=CInt( [@PageCount] )
CShort
CShort( x )
This converts the expression to a Short data type.
=CShort( [@PageCount] )
CLong
CLong( x )
This converts the expression to a Long data type.
=CLong( [@MonthEnd] )
Functions - Text
This group of functions are string functions. They perform an operation on a text string and return either text or a number.
LTrim
Ltrim( str )
This function removes all leading spaces.
=[LTrim( [BAccount.AcctName] )
RTrim
Rtrim( str )
This function removes all trailing spaces.
=[RTrim( [BAccount.AcctName] )
Trim
Trim( str )
This function removes all trailing spaces.
=[RTrim( [BAccount.AcctName] )
Format
Format( format,argument )
This function replaces the format item with the text equivalent of the arguments.Here we're using it for date formatting of the function Now.
=Format('{0:MMMM dd yyyy}',Now())
UCase
UCase( string )
This function returns an uppercase conversion of the string.
=[UCase( [BAccount.AcctName] )
LCase
LCase( string )
This function returns an lowercase conversion of the string.
=[LCase( [BAccount.AcctName] )
InStr
InStr( string, findstr )
This function returns the first occurence of the string findstr, a space, in the example, in the string.
=[InStr( [BAccount.AcctName], ' ' )
InStrRev
InStr( string, findstr )
This function returns the last occurence of the string findstr, a dot, in the example, in the string.
=[InStr( [BAccount.AcctName], '.' )
Len
Len( string )
This function returns an integer containing the number of characters in the string.
=Len( [BAccount.AcctName] )
Left
Left( string, length )
This function returns a string containing the specified number of characters from the left side of the string.
=Left( [BAccount.AcctName], 7 )
Right
Right( string, length )
This function returns a string containing the specified number of characters from the right side of the string.
=Right( [BAccount.AcctName], 7 )
Replace
Replace( str, oldValue, newValue )
This function returns a string in which oldValue has been replaced by newValue.
=Replace( [BAccount.AcctName], 'Ltd.', 'Limited' )
PadLeft
PadLeft( str, width, paddingChar )
This function returns a right aligned string, padded with the specified character on the left to the specified width.
=PadLeft( [BAccount.AcctName], 25, ' ' )
PadRight
PadRight( str, width, paddingChar )
This function returns a left aligned string, padded with the specified character on the left to the specified width.
=PadLeft( [BAccount.AcctName], 25, ' ' )
Substring
Substring( str, start, length )
This function returns a string containing the specified number of charachers from length form the left side of the specified string str starting from the specified number of characters at start starting from zero.
=Substring( [BAccount.AcctName], 0, 10 )
Concat
Concat( str, str2 ...)
This function returns strings joined together
=Concat( 'Hello ', [Contact.Salutation], ' ', [Contact.LastName])
Functions - Program Shortcuts
These are grouped under program shortcuts by the Acumatica Expression Editor.
NullIf
NullIf( value1, value2 )
The function is evaluated and returns NULL if first and second values are equal.
I havent found a need to use this yet.
Assign
Assign( '$name', expr )
Assign( '$name', expr, resetExpr )
Assigns the result of an expression to the specified variable. The expression's value is stored when the variable is set, and the resetExpression determines when the variable should be reset. This function can either update an existing variable or create a new one with the calculated expression value.
I havent found a need to use this yet.
Functions - DateTime
This group of functions provide access to commonly needed dates. Unless you can work with the default formatting you'll need to learn how to format the dates to your specific requirements too.
Year
Year( date )
The function is evaluated and a value is returned as an integer which is the year extracted from the specified date.
=Year( [SOOrder.OrderDate] )
Month
Month( date )
The function is evaluated and a value is returned as an integer which is the year extracted from the specified date.
=Month( [SOOrder.OrderDate] )
Day
Day( date )
The function is evaluated and a value is returned as an integer which is the year extracted from the specified date.
=Day( [SOOrder.OrderDate] )
DayofWeek
DayOfWeek( date )
The function is evaluated and a value is returned as an integer which is the year extracted from the specified date.
=DayofWeek( [SOOrder.OrderDate] )
DayOfYear
DayOfYear( date )
The function is evaluated and a value is returned as an integer which is the year extracted from the specified date.
=DayOfYear( [SOOrder.OrderDate] )
Year
Year( date )
The function is evaluated and a value is returned as an integer which is the year extracted from the specified date.
=Year( [SOOrder.OrderDate] )
Hour
Hour( date )
The function is evaluated and a value is returned as an integer which is the year extracted from the specified date.
=Hour( [SOOrder.OrderDate] )
Minute
Minute( date )
The function is evaluated and a value is returned as an integer which is the year extracted from the specified date.
=Minute( [SOOrder.OrderDate] )
Second
Second( date )
The function is evaluated and a value is returned as an integer which is the year extracted from the specified date.
=Second( [SOOrder.OrderDate] )
MonthName
MonthName( date )
The function is evaluated and a value is returned as an integer which is the year extracted from the specified date.
=MonthName( [SOOrder.OrderDate] )
DayOrdinal
DayOrdinal( date )
The function is evaluated and a value is returned as an integer which is the year extracted from the specified date.
=DayOrdinal( [SOOrder.OrderDate] )
Functions - Math
This group of functions are mathematical.
Abs
Abs( x )
This function returns the absolute value of a number.
=Abs( [SOOrder.OrderTotal] )
Floor
Floor( x )
This expression returns the absolute value of a number. That is to say the largest whole number that is less than or equal to the argument. So for example below in the image, the floor value of the order total equates to a whole number (in the screenshot 59306), and the abs contains the number to two decimal places (in the screenshot 59306.06). The number is displayed by Acumatica to two decimal places unless you format it differently. Why you might want to do this is beyond me. Note that there is no rounding this is a purely mathematical function.
=Floor( [SOOrder.OrderTotal] ) =Abs( [SOOrder.OrderTotal] )
Ceiling
Ceiling( x )
This function returns the absolute value of a number. That is to say the largest whole number that is greater than the argument. So for example below in the image, the ceiling value of the order total equates to a whole number (in the screenshot 59307), and the abs contains the number to two decimal places (in the screenshot 59306.06). The number is displayed by Acumatica to two decimal places unless you format it differently. Note that there is no rounding this is a purely mathematical function.
=Ceiling( [SOOrder.OrderTotal] ) =Abs( [SOOrder.OrderTotal] )
Round
Round( x, decimals )
This function returns a number rounded to the number of decimal places specified. So for example below in the image, the rounded value to one decimal place of the order total equates to (in the screenshot 59306.10), and the abs contains the number to two decimal places (in the screenshot 59306.06). The number is displayed by Acumatica to two decimal places unless you format it differently.
=Round( [SOOrder.OrderTotal], 1) =Abs( [SOOrder.OrderTotal] )
Min
Min( x, decimals )
This function returns the smaller of the two values.
=Min( 7, 4)
Max
Max( x, y )
This function returns the larger of the two values. .
=Max( 7, 4)
Pow
Pow( x, power )
This function returns the value of x to the power of the value of power. A power is a way of writing repeated multiplication using the same number. So in our example it would be 59306.10 multiplied by 59306.10 which is 3517208752.7236, again displayed to two decimal places by default in our report.
=Pow( [SOOrder.OrderTotal], 2)
Functions - Aggregates
This group of functions perform an calculation on a group of values and return a single value.
Avg
Avg( expression )
This function returns the average of all non-null values provided in the expression.
=Avg( 7, 4)
Sum
Sum( x )
This returns the sum of the values specified.
=Sum( [SOLine.OrderQty] )
Count
Count( x )
This returns the count of the values.
=Count( [SOLine.OrderQty] )
Max
Max( x )
This returns the maximum value.
=Max( [SOLine.OrderQty] )
Min
Min( x )
This returns the minimum value.
=Min( [SOLine.OrderQty] )
Next
Min( x )
This returns the next value.
=Next( [SOLine.OrderQty] )
Prev
Prev( x )
This returns the previous value.
=Prev( [SOLine.OrderQty] )
First
First( x )
This returns the first value.
=First( [SOLine.OrderQty] )
Last
Last( x )
This returns the last value.
=Last( [SOLine.OrderQty] )
