Lesson 6: Using Variables and Expressions
In this lesson, you will learn how to use variables and expressions in reports.
Variables and Expressions: General Information
In the Acumatica Report Designer, you can use variables to store values and substitute values in expressions. You can use expressions to define the values to be displayed in a report, or to specify report properties, such as group conditions and the visibility of sections or elements of the report. In expressions, you can use variables and user-specified report parameters. (For more information about parameters, see Parameters and Filters: General Information.) In the Report Designer, you can type an expression or you can compose it in the Expression Editor.
Learning Objectives
In this chapter, you will learn how to do the following in the Report Designer:
- Create a variable
- Specify an expression
Applicable Scenarios
You may want to use variables and expressions in the Acumatica Report Designer in the following circumstances:
- You are responsible for the customization of Acumatica ERP in your company, including developing and modifying reports to give users the information they need to do their jobs.
- You may want to perform calculations on values before presenting them or transform the data in some way.
System and User Variables
System variables are available in all reports. These variables can be inserted into a report as values or included in expressions. The following table shows the system variables defined in the Report Designer.
Table: System Variables
System Variable Description
PageIndex Places in the expression the page index value selected in the data source definition of the
current report.
PageCount Places in the expression the page count value for the current report.
PageOf Places in the expression the page number and total page count values for the current report.
A user variable is the one that you specify for a report section. This variable is visible in all other sections of the report. If you declare the same variable in multiple sections, the variable is shared between them. If the variable is modified in one section, the new value of the variable is passed to the next section where the variable is used. The variable is initiated in the topmost section where it is used. Then the variable is sequentially modified in the following sections in the order in which these sections appear in the report. Lesson 6: Using Variables and Expressions | 58
Defining of a User Variable
To define a user variable, you use the Behavior > Variables property of the report section. The variables have the $VariableName format, where VariableName is the name of the variable defined in the report. The following example illustrates the use of the $RowNumber user variable in an expression.
=Assign( '$RowNumber', $RowNumber + 1 )
In a report, you can use a variable defined in a subreport. In this case, the variable has the $ReportName_VariableName format, where ReportName is the name of the report (that is, the subreport) in which the variable is defined. For example, suppose that you have defined the $pmt variable in the payment report. If you need to use this variable in the main report in your Acumatica ERP instance, you refer to this variable as $payment_pmt.
Addition and Deletion of a Variable in a Report Section
To define a variable, you use the ReportVariable Collection Editor (that is, the ReportVariable Collection Editor dialog box). To open the ReportVariable Collection Editor, you select the report section where you want to add the variable; then on the Properties tab of the Properties pane, you click the More button to the right of the Behavior > Variables property. If you are adding more than one variable in a section, in the ReportVariable Collection Editor, you should list variables in the Members list in the order in which they are calculated. For example, suppose that you have specified two variables—A and B. Also suppose that $B=F($A). In this case, A should be listed first and B should be listed second. To change the order of variables in the ReportVariable Collection Editor, you use buttons with arrows to the right of the Members list. To delete a variable from the list of existing variables, you open the ReportVariable Collection Editor, select this variable in the Members list, and click Remove.
Processing of Variables and Property Values of Visual Elements
When you define a variable in the ReportVariable Collection Editor, the ProcessOrder property of the variable is set to WhileRead by default. The ProcessOrder property specifies when a variable value is processed during the generation of the report. A value can be processed when the data is read from the data source (WhileRead), when the report is printed (WhilePrint), or in both cases (Always). In most cases, when a value is read from the data source and then displayed in the report, WhileRead is sufficient. The ProcessOrder property is also used for sections and visual elements. Once this property has been specified for a section, the property is applied to every visual element in this section unless it is overridden for a visual element. By default, the ProcessOrder property of the page header and page footer sections is set to Always. For other sections, WhileRead is the default value for this property. You can specify the WhilePrint value for the ProcessOrder property if the value being calculated depends on data that will be known only at the report rendering stage. For example, suppose that you have added two text boxes to the page footer section. The Appearance > Value property of the first text box is Continued ..., and the Appearance > Value property of the second text box is =PageOf. The visibility of the Continued ... string depends on the page number and page count, which are calculated by using the PageIndex and the PageCount system variables. Both numbers for each page will be known only at the report rendering stage. Similarly, the page number, which is calculated by using the PageOf system variable, will be known only at the report rendering stage. Page numbers are typical data fields that require the WhilePrint processing mode. Thus, Always, which is the default value for the page footer section, is an appropriate setting. If you select the WhileRead setting for the page footer section, the Continued ... string will be printed on every page, and the page number will never be printed. Lesson 6: Using Variables and Expressions | 59
Variables and Expressions: Functions in Expressions
You can use functions to perform specific tasks that facilitate the processing of data for reports and generic inquiries. The Expression Editor provides many functions that process data and return values, which you can use in reports and generic inquiries. To use functions in expressions, you can enter them manually in the expression editing area or select them from the list of functions provided by the Expression Editor. You can use the following groups of functions in expressions.
Type Conversion Functions
You use the type conversion functions to convert data from one data type to another. The following table lists the type conversion functions available in the Conversion subnode of the Functions node in the Expression Editor.
Function Description and Examples
CBool(x) Converts the expression used as the function argument into a Boolean expression. Returns False
if the Boolean value is 0; otherwise, returns True. The return type is Boolean.
Example: CBool($CurrCompanyTot - $CompanyTot)
In this example, CurrCompanyTot and CompanyTot are report variables.
CDate(x) Converts the expression used as the function argument into a value of the Date type. The argu-
ment should be a valid date expression according to the locale selected for the import or export
scenario.
Example: CDate($DueDate - 1)
In this example, DueDate is a report variable.
CStr(x) Converts the expression used as the function argument into a string. If the argument is Null, the
function returns a run-time error; otherwise, it returns a string. The return type is String.
Example: CStr($PrintDoc)
Here, PrintDoc is a report variable.
CDbl(x) Converts the expression defined in the function argument into a value of the Double type. The
return type is Double.
Example: CDbl($CurrBal/$CurrTot)
Here, CurrBal and CurrTot are report variables.
CSng(x) Converts the expression used as the function argument into a value of the Single type. If the ex-
pression has a value outside the acceptable range for the Single type, this function returns an
error. The return type is Float.
Example: CSng($StCycCurrTot/$CompanyTot)
In this example, StCycCurrTot and CompanyTot are report variables.
Lesson 6: Using Variables and Expressions | 60
Function Description and Examples
CDec(x) Converts the expression used as the function argument into a value of the Decimal type. The re-
turn type is Decimal.
Example: CDec($CompanyTot)
In this example, CompanyTot is a report variable.
CInt(x) Converts the expression used as the function argument into a value of the Integer type. The re-
turn type is Int32.
Example: CInt([ARPayment.ExtRefNbr])
In this example, ARPayment.ExtRefNbr is an attribute from the database schema.
CShort(x) Converts a numeric value to a value of the Short type. The return type is Int16.
Example: CShort([ARPayment.ImpRefNbr])
ARPayment.ImpRefNbr is an attribute from the database schema.
CLong(x) Converts a numeric value to a value of the Long type. The return type is Int64.
Example: CLong($CurrTot)
In this example, CurrTot is a report variable.
Aggregate Functions
Aggregate functions perform a calculation on a set of values and return a single value. The following table lists the aggregate functions available in the Aggregates subnode of the Functions node in the Expression Editor.
Function Description and Examples
Avg(expression) Returns the average of all non-null values of the specified expression.
Example: Avg($StCycAgeTot00, $StCycAgeTot01)
In this example, StCycAgeTot00 and StCycAgeTot01 are report variables.
Sum(expression) Returns the sum of the values of the specified expression.
Example: Sum([ARInvoice.TaxTotal], $CurrTot)
In this example, ARInvoice.TaxTotal is an attribute from the database
schema, and CurrTot is a report variable.
Count(expression) Returns the count of the values from the specified expression.
Example: Count($AgeBal00, $AgeBal01)
In this example, AgeBal00 and AgeBal01 are report variables.
Max(expression) Returns the maximum value from all non-null values of the specified expression.
Example: Max($CurrCompanyTot, $CompanyTot)
In this example, CurrCompanyTot and CompanyTot are report variables.
Lesson 6: Using Variables and Expressions | 61
Function Description and Examples
Min(expression) Returns the minimum value from all non-null values of the specified expression.
Example: Min($CurrCompanyTot, $CompanyTot)
In this example, CurrCompanyTot and CompanyTot are report variables.
Next(expression) Returns the next value (from the current one) in the specified expression.
Example: Next([ARInvoice.LineTotal],[ARInvoice.TaxTotal])
In this example, ARInvoice.LineTotal and ARInvoice.TaxTotal are at-
tributes from the database schema.
Prev(expression) Returns the previous value (from the current one) in the specified expression.
Example: Prev([ARInvoice.LineTotal],[ARInvoice.TaxTotal])
ARInvoice.LineTotal and ARInvoice.TaxTotal are attributes from the
database schema.
First(expression) Returns the first value in the specified expression.
Example: First([ARInvoice.LineTotal],[ARInvoice.TaxTotal])
In this example, ARInvoice.LineTotal and ARInvoice.TaxTotal are at-
tributes from the database schema.
Last(expression) Returns the last value in the specified expression.
Example: Last([ARInvoice.LineTotal],[ARInvoice.TaxTotal])
In this example, ARInvoice.LineTotal and ARInvoice.TaxTotal are at-
tributes from the database schema.
String Functions
String functions perform an operation on a string input value and return a string or numeric value. Listed below are the string functions available in the Text subnode of the Functions node in the Expression Editor.
Function Description and Examples
LTrim(string) Removes all leading spaces or parsing characters from the specified character ex-
pression, or all leading 0 bytes from the specified binary expression.
Example: LTrim(CStr([Contact.LastName]))
In this example, Contact.LastName is an attribute from the database schema.
RTrim(string) Removes all trailing spaces or parsing characters from the specified character ex-
pression, or all trailing 0 bytes from the specified binary expression. The return type
is String.
Example: RTrim(CStr([Contact.LastName]))
In this example, Contact.LastName is an attribute from the database schema.
Lesson 6: Using Variables and Expressions | 62
Function Description and Examples
Trim(string) Removes all leading and trailing spaces or parsing characters from the specified character expression, or all leading and trailing 0 bytes from the specified binary ex- pression. The return type is String. Example: Trim(CStr(Contact.FirstName+Contact.Mid- Name+Contact.LastName)) In this example, Contact.FirstName, Contact.MidName, and Contac- t.LastName are attributes from the database schema.
Format(format, Replaces the format item in a specified formatting string (format) with the text argument(s)) equivalent of the arguments (arguments). The return type is String. Example: Format('Curr. Balance: . . . . . . . . {0:C}; Total Amount: . . . . . . . . {1:N}', $CurrBal, $CurrTot) In this example, CurrBal and CurrTot are report variables; 0 and 1 are 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 uppercase. The string argument can be any valid string expression. The return type is String. If string contains Null, Null is returned. Example: UCase(CStr(RowContact.MidName)) In this example, RowContact.MidName is an attribute from the database schema.
LCase(string) Returns a string that has been converted to lowercase. The string argument can be any valid string expression. The return type is String. If string contains Null, Null is returned. Example: LCase(CStr(Contact.Email)) In this example, Contact.Email is an attribute from the database schema.
InStr(string, Returns the position of the first occurrence of one string (findString) within another findString) (string).The return type is String. Example: InStr(CStr(Contact.Email), '@') In this example, Contact.Email is an attribute from the database schema.
InStrRev(string, Returns the position of the last occurrence of one string (findString) within another findString) (string), starting from the right side of the string. Example: InStrRev(CStr(Contact.Email), '@') In this example, Contact.Email is an attribute from the database schema.
Len(string) Returns an integer containing either the number of characters in the string or the nominal number of bytes required to store a variable. The return type is Int32. Example: Len(CStr(Contact.Email)) In this example, Contact.Email is an attribute from the database schema. Lesson 6: Using Variables and Expressions | 63
Function Description and Examples
Left(string, Returns a string containing the specified number of characters from the le side of a
length) string. The return type is String. If string contains Null, Null is returned.
Example: Left(CStr([Contact.Email]), 7)
In this example, Contact.Email is an attribute from the database schema.
Right(string, Returns a string containing a specified number of characters from the right side of a
length) string. The return type is String. If string contains Null, Null is returned.
Example: Right(CStr([Contact.Email]), 10)
In this example, Contact.Email is an attribute from the database schema.
Replace(string, Returns a string in which the specified substring (oldValue) has been replaced with
oldValue, newVal- another substring (newValue). The return type is String.
ue)
Example: Replace(CStr([Contact.Email]), '@.', '@')
In this example, Contact.Email is an attribute from the database schema.
PadLeft(string, Right-aligns the characters in the specified string (string), padding with the specified
width, characters (paddingChar) on the le for the specified total width (width).
paddingChar)
Example: PadLeft(CStr([Contact.Email]), 7, '@')
In this example, Contact.Email is an attribute from the database schema.
PadRight(string, Le-aligns the characters in a specified string (string), padding with the specified
width, character (paddingChar) on the right up to the specified total width (width). The re-
paddingChar) turn type is String.
Example: PadRight(CStr([Contact.Email]), 10, '@')
In this example, Contact.Email is an attribute from the data schema.
Substring(string, Returns a string containing the specified number of characters (length) from the le
start, length) side of the specified string (string) starting from the specified symbol number (start).
The numbering is 0 based, meaning that the number of the first symbol in the string
is 0.
Example: Substring([ARInvoice.DocDesc], 0, 10)
In this example, ARInvoice.DocDesc is an attribute from the data schema.
Mathematical Functions
Mathematical functions perform calculations, usually based on input values provided as arguments, and return numeric values. The following table lists the mathematical functions available in the Math subnode of the Functions node in the Expression Editor.
Function Description and Examples
Abs(x) Returns the absolute value of the number.
Example: Abs($CurrBal - $CurrTot)
In this example, CurrBal and CurrTot are the report variables.
Lesson 6: Using Variables and Expressions | 64
Function Description and Examples
Floor(x) Returns the largest integer that is not greater than the argument.
Example: Floor([Contact.NoteID])
In this example, Contact.NoteID is an attribute from the database schema.
Ceiling(x) Returns the smallest integer that is not less than the argument.
Example: Ceiling([Contact.NoteID])
In this example, Contact.NoteID is an attribute from the database schema.
Round(x, dec- Returns a numeric expression, rounded to the specified precision (decimals).
imals)
Example: Round($CurrTot, 2)
In this example, CurrTot is a report variable.
Min(x, y) Returns the smaller of two values.
Example: Min($CurrTot, $CurrCompanyTot)
In this example, CurrTot and CurrCompanyTot are report variables.
Max(x, y) Returns the greater of two values.
Example: Max($CurrTot, $CurrCompanyTot)
In this example, CurrTot and CurrCompanyTot are report variables
Pow(x, power) Computes the value of x raised to the specified power (power).
Example: Pow(([Contact.NoteID], 2))
In this example, Contact.NoteID is an attribute from the database schema.
Date and Time Functions
The date and time functionsperform operations on input values and return values of the following types: string, numeric, or date and time. The following table lists the string functions available in the Date/Time subnode of the Functions node in the Expression Editor. Lesson 6: Using Variables and Expressions | 65
Function Description and Examples
DateAdd(date, in- Returns a new date, which is calculated by adding the specified number (number) terval, number) of time intervals (interval) to the date (date). The interval argument specifies the type of time interval and can be one of the following options:
- y, yy, yyyy, or year: The specified number (number) of years will be added to the specified date (date).
- m, mm, or month: The specified number (number) of months will be added to the specified date (date).
- d, dd, or day: The specified number (number) of days will be added to the speci- fied date (date).
- h, hh, or hour: The specified number (number) of hours will be added to the specified date (date).
- n, mi, or minute: The specified number (number) of minutes will be added to the specified date (date).
- s, ss, or second: The specified number (number) of seconds will be added to the specified date (date).
- w, ww, wk, or week: The specified number (number) of weeks will be added to the specified date (date).
- q, qq, or quarter: The specified number (number) of quarters will be added to the specified date (date). Examples: DateAdd($DueDate, 'm', -2) DateAdd(CDate('31/01/1995'), 'm', -2) DateAdd($DueDate, 'y', -2) DateAdd(Cdate($DueDate), 'd',
In these examples, DueDate is a report variable.
DateDiff(interval, Returns the count (as a signed integer value) of the specified interval boundaries date1, date2) that are crossed between the specified date1 and date2. The interval argument, which specifies the type of time interval, can be one of the following options:
- y, yy, yyyy, or year: The time interval is specified in years.
- m, mm, or month: The time interval is specified in months.
- d, dd, or day: The time interval is specified in days.
- h, hh, or hour: The time interval is specified in hours.
- n, mi, or minute: The time interval is specified in minutes.
- s, ss, or second: The time interval is specified in seconds.
- w, ww, wk, or week: The time interval is specified in weeks.
- q, qq, or quarter: The time interval is specified in quarters. Lesson 6: Using Variables and Expressions | 66
Function Description and Examples
Day(date) Returns the day (as an integer) extracted from the specified date (date). The re- turn type is Int32. Examples: Day(ARPayment.ClearDate) Day($DueDate) Day(Cdate($DueDate)) Day(CDate('31/01/1995')) In these examples, DueDate is a report variable, and ARPayment.ClearDate is an attribute from the database schema.
DayOfWeek(date) Returns the day of the week associated with the specified date (date) as an inte- ger. The return type is Int32. Examples: DayOfWeek(ARPayment.ClearDate) DayOfWeek($DueDate) DayOfWeek(Cdate($DueDate)) DayOfWeek(CDate('31/01/1995')) In these examples, DueDate is a report variable, and ARPayment.ClearDate is an attribute from the database schema.
DayOfYear(date) Returns the day of the year calculated for the specified date (date). The return type is Int32. Examples: DayOfYear(ARPayment.ClearDate) DayOfYear($DueDate) DayOfYear(Cdate($DueDate)) DayOfYear(CDate('31/01/1995')) In these examples, DueDate is a report variable, and ARPayment.ClearDate is an attribute from the database schema.
DayOrdinal(day) Converts the specified integer value (day) to a string that contains the ordinal number in the current locale. For example, for the English locale, the string con- tains the short form of the ordinal number, such as 1st or 2nd. For numbers that are less than 1 or greater than 31, the function returns an empty string. Example: DayOrdinal(1) Lesson 6: Using Variables and Expressions | 67
Function Description and Examples
Hour(date) Returns the hours extracted from the specified date (date). The return type is Int32. Examples: Hour(ARPayment.ClearDate) Hour($DueDate) Hour(Cdate($DueDate)) In these examples, DueDate is a report variable, and ARPayment.ClearDate is an attribute from the database schema.
Minute(date) Returns the number of minutes extracted from the specified date (date). The re- turn type is Int32. Examples: Minute(ARPayment.ClearDate) Minute($DueDate) Minute(Cdate($DueDate)) Minute(CDate('31/01/1995')) In these examples, DueDate is a report variable, and ARPayment.ClearDate is an attribute from the database schema.
Month(date) Returns the month, as an integer, extracted from the specified date (date). The return type is Int32. Examples: =Month(ARPayment.ClearDate) =Month($DueDate) =Month(Cdate($DueDate)) =Month(CDate('31/01/1995')) In these examples, DueDate is a report variable, and ARPayment.ClearDate is an attribute from the database schema.
MonthName(date) Returns a string that contains the name of the month extracted from the specified date (date) in the current locale. Example: MonthName(CDate('3/12/2022'))
Now() Returns the business date and time according to the system date and time on the local computer. The return type is DateTime. Example: Now() Lesson 6: Using Variables and Expressions | 68
Function Description and Examples
NowUTC() Returns the business date and time in the user's time zone. The system extracts the user's time zone from one of the following sources, which are ordered by prior- ity from the highest to the lowest:
- The time zone specified for the user in the Time Zone box (General Info tab) of the User Profile (SP203010) form.
- The employee calendar selected for the user in the Calendar box of the Gener- al Info tab of the Employees (EP203000) form. The time zone of the calendar is specified in the Summary area of the Work Calendar (CS209000) form.
- The time zone specified for the site in the Login Time Zone box on the Site Pref-
erences (SM200505) form.
Example: NowUTC()
Second(date) Returns the seconds extracted from the specified date (date) as an integer. The return type is Int32. Examples: Second(ARPayment.ClearDate) Second($DueDate) Second(Cdate($DueDate)) Second(CDate('31/01/1995')) In these examples, DueDate is a report variable, and ARPayment.ClearDate is an attribute from the database schema.
Today() Returns the business date according to the system date and time on the local computer. The return type is DateTime. Example: Today()
TodayUTC() Returns the business date in the user's time zone. The system extracts the user's time zone from one of the following sources, which are ordered by priority from the highest to the lowest:
- The time zone specified for the user in the Time Zone box (General Info tab) of the User Profile (SP203010) form.
- The employee calendar selected for the user in the Calendar box (General Info tab) of the Employees (EP203000) form. The time zone of the calendar is speci- fied in the Summary area of the Work Calendar (CS209000) form.
- The time zone in the site preferences, which are specified in the Login Time
Zone box on the Site Preferences (SM200505) form.
Example: TodayUTC()
Lesson 6: Using Variables and Expressions | 69
Function Description and Examples
Year(date) Returns the year, as an integer, extracted from the specified date (date). The re-
turn type is Int32.
Examples:
Year([ARPayment.ClearDate])
Year(Cdate($DueDate)) Year($DueDate)
Year(CDate('31/01/1995'))
In these examples, DueDate is a report variable, and ARPayment.ClearDate
is an attribute from the database schema.
Shortcut Functions
The shortcut functions perform miscellaneous operations. The following tables list the string functions available in the Math subnode of the Program Shortcut node in the Expression Editor.
Function Description and Examples
Assign('$name', Assigns the result of the expression calculation to the variable specified as the para-
expression) meter. The function can be used to assign a value to an existing variable, or a new
variable can be created with the expression calculation value assigned to it.
Example: Assign(PrintDoc, (IsNull([RowARRegister.Cus-
tomerID])))
In this example, PrintDoc is a report variable, and ARRegister.CustomerID
is an attribute from the data schema).
Assign('$name', Assigns the result of the expression calculation to the variable specified as the para-
expression, reset- meter. The expression value is assigned to the variable when the variable is set, and
Expression) the resetExpression value defines when the variable value should be reset. The func-
tion can be used to assign a value to an existing variable, or a new variable can be
created and the expression calculation value is assigned to it.
Example: Assign(<nowiki>'PrintDoc'</nowiki>, (IsNull([AR-
Register.CustomerID])), IsNull([APPayment.AdjFinPeri-
odID]))
In this example, PrintDoc is a report variable, and ARRegister.CustomerID
is an attribute from the database schema).
IIf(expression, Returns one of two values, depending on the evaluation of the expression: If the ex-
truePart, falsePa- pression evaluates to True, the function returns the truePart value; otherwise, it
rt) returns the falsePart value.
Example: IIf(($CurrTot-$CurrBal)<>0), CStr([ARRegis-
ter.DocBal]), 'No data available')
In this example, CurrTot and CurrBal are report variables, and ARRegis-
ter.DocBal is an attribute from the database schema.
You can also use this function to set a field value to Null.
Example: =IIf([Document.OrderType]='QT', 'FEDEX', NULL)
Lesson 6: Using Variables and Expressions | 70
Function Description and Examples
IsNull(value, nul- Returns nullValue if value is NULL; otherwise, returns value.
lValue)
Example: IsNull($PrintDoc, 'NULL')
In this example, PrintDoc is a report variable.
NullIf(value1, Returns NULL if value1 is equal to value2.
value2)
Switch(expres- Returns the value (value_n) that corresponds to the first expression (expression_n)
sion_1, value_1, that evaluates to True. For example, expression_1 and expression_2 are Boolean
expression_2, val- expressions.
ue_2, ...)
Example: Switch((($CurrTot-$CurrBal)<0), $CurrBal, (($Cur-
rTot-$CurrBal)>0), $CurrTot)
In this example, CurrTot and CurrBal are report variables.
Application-Specific Functions Application-specific functions are specific for the application in which you will run the report. That is why these functions are not listed in the Expression Editor dialog box; you need to enter these functions manually. The following table includes the application-specific functions available in Acumatica Report Designer.
Function Description and Examples
GetAPPaymentInfo(ac- Returns the value of the specified AP payment attribute (detailID) for a specif-
countCD, payment- ic cash account (accountCD), payment method (paymentMethodID), and ven-
MethodID, detailID, dor (acctCD). The function returns the attribute value as it is specified in the
acctCD) Payment Instructions section on the Payment Settings tab of the Vendors
(AP303000) form.
If the specified record is not available, the function returns an empty string.
Example: Payments.GetAPPaymentInfo('102000','FED-
WIRE','INSTRUCTIONS','V000213')
GetARPaymentInfo(ac- Returns the value of the specified AR payment attribute (detailID) for a specific
countCD, payment- cash account (accountCD), payment method (paymentMethodID), and customer
MethodID, detailID, (acctCD). The function returns the attribute value as it is specified on the Pay-
pMInstanceID) ment Method Details tab of the Customer Payment Methods (AR303010) form.
If the specified record is not available, the function returns an empty string.
Example: Payments.GetARPaymentInfo('102000','FED-
WIRE','ACCOUNTNO','C0003163')
Lesson 6: Using Variables and Expressions | 71
Function Description and Examples
GetRemitPaymentIn- Returns the value of the specified payment attribute (detailID) for a specific cash
fo(accountCD, pay- account (accountCD), payment method (paymentMethodID), and vendor or cus-
mentMethodID, de- tomer (acctCD). The function returns the attribute value as it is specified on the
tailID) Remittance Settings tab of the Cash Accounts (CA202000) form.
If the specified record is not available, the function returns an empty string.
Example: Payments.GetRemitPaymentInfo('102000','FED-
WIRE','ACCOUNTNO')
DAC Field Modifiers
The following table includes the modifiers that can be applied to a DAC field available in Acumatica Report Designer.
Function Description and Examples
.Raw Returns the original field value without any additional processing (as it is stored in data-
base).
Example: AMBomOper.RunUnitTime.Raw returns 60, while AMBomOper.RunUnit-
Time returns 1:00.
.Format Depending on a field type returns one of the following:
- List: Returns allowed labels and values surrounded with square brackets. Label and value are delimited with the \u001e Unicode character. Each pair is delimited with the \u001f Unicode character. Example: A\u001eAuto\u001fM\u001eManual.
- String: Returns specified input mask surrounded with curly brackets. Example: {##- ####}.
- Date: Returns specified display mask or d if a mask is not specified. Example: MMMM dd, yyyy.
- Numeric with floating point: Returns Nx where x is a precision specified for the field. Ex-
ample: N8.
.DisplayName Returns the display name for the field. Example: SOOrder.CustomerRefNbr.DisplayName returns External Reference.
Variables and Expressions: Expression Editor
You use the Expression Editor—that is, the Expression Editor dialog box—to define expressions in reports and generic inquiries.
Expression Editor
In the Report Designer, you can open the Expression Editor in any of the following places:
- On the Relationships tab of the Schema Builder, by clicking the Parent Formula or Child Formula button
- On the Parameters tab of the Schema Builder, by clicking the More button to the right of any of the following boxes: Input mask, View name, and Default Value Lesson 6: Using Variables and Expressions | 72
- On the Filters tab of the Schema Builder, by clicking the Data Field Formula button
- On the Sorting and Grouping tab of the Schema Builder, by clicking the Grouping Field Formula button
- In the Appearance > Value property of any TextBox element The Expression Editor dialog box consists of the following four panes:
- The le pane of the dialog box (see Item 1 in the screenshot below) displays a list of data access classes and their fields defined for the report.
- The middle pane of the dialog box (Item 2) lists groups of parameters, operators, functions, and variables.
- The right pane of the dialog box (Item 3) contains the parameters, operators, functions, or variables in the group you have selected in the middle pane.
- The bottom pane of the dialog box (Item 4) is used to compose and edit the expression. You can double-click data fields listed in the le pane and items listed in the right pane to add them to the bottom pane.
Figure: The Expression Editor
You perform the following steps (repeating the steps as needed) to enter an expression in the Expression Editor:
- In the middle pane, you selectively expand the hierarchical structure of existing entities, and select a group of parameters, variables, operators, or functions to display the list of available items in the right pane.
- In the right pane, you click the item you want to insert in the bottom pane.
- In the expression, you click in the place where you want to insert the selected item.
- You double-click the item that you previously selected in the right pane.
- In the le pane, you expand the hierarchical structure of classes defined for the report, and select a data field to insert it in the bottom pane.
- In the expression, you click in the place where you want to insert the selected data field.
- You double-click the item that you previously selected in the le pane. You can add as many items and fields to the bottom pane as you need.
- You validate the expression.
- You save the expression. Lesson 6: Using Variables and Expressions | 73
Variables and Expressions: Data Formats of Values
You can specify a custom format for the numeric and date values of text boxes on the report layout. The format determines how the values in a text box should be displayed in a report. To format the value of a text box, you enter one of the following types of format specifiers in the Format property of the text box:
- Format specifiers of any data fields used in a report
- Standard numeric format specifiers
- Custom numeric format specifiers
- Standard date format specifiers
- Custom date format specifiers
Application of Standard Format Specifiers
When a user runs the resulting report, the system applies the standard format specifiers by using the language this user selected when signing in to Acumatica ERP, as shown in the following screenshot. If your instance of Acumatica ERP does not allow the user to select a language, the specifiers are applied by using the default language of the installed application, which is English (United States).
Figure: Selection of a language on the Sign-In page of Acumatica ERP
The list of the languages available at sign-in is specified on the System Locales (SM200550) form, as shown in the following screenshot. Lesson 6: Using Variables and Expressions | 74
Figure: Available languages in the system
Formatting of Values According to the Format of a Data Field
You can use any data field as a format specifier for a text box. To do this, you select the Format property of this text box, and click the More button to open the Expression Editor. In the dialog box, you select the data field to use as a format specifier, and add .Format to the end of the data field name inside brackets, as shown in the following example: =ARTran.TranAmt.Format.
Formatting of Numeric Values
The following table lists the standard numeric format specifiers, along with a description of each specifier and examples that show the output produced by a particular format specifier. To use the standard numeric format specifiers, you type a format specifier, such as N, into the Format property of a text box.
Format specifier Description Examples
D or d Result: Integer digits with an optional negative sign 1234 (D) –>1234
Supported by: Integral types only -1234 (D6) -> -001234
Precision specifier: The minimum number of digits
F or f Result: Integral and decimal digits with an optional 1234.567 (F, en-US) -> 1234.57
negative sign
1234.567 (F, fr-FR) -> 1234,57
Supported by: All numeric types
- 1234 (F1, en-US) -> -1234.0 Precision specifier: The needed number of decimal
- 1234.56 (F4, fr-FR) -> -1234,5600
digits
Default precision specifier: 2
N or n Result: Integral and decimal digits, group separa- 1234.567 (N, en-US) -> 1,234.57 tors, and a decimal separator with an optional neg- 1234.567 (N, ru-RU) -> 1 234,57 ative sign 1234 (N1, en-US) -> 1,234.0 Supported by: All numeric types - 1234.56 (N3, en-US) -> -1,234.560
Precision specifier: The needed number of decimal
places
Default precision specifier: 2
Lesson 6: Using Variables and Expressions | 75
Format specifier Description Examples
P or P Result: A number multiplied by 100 and immediate- 1 (P, en-US) -> 100.00 % ly followed by a percent symbol 1 (P, fr-FR) -> 100,00 % Supported by: All numeric types - 0.39678 (P1, en-US) -> -39.7 % Precision specifier: The needed number of decimal
- 0.39678 (P3, fr-FR) -> -39,678 %
places
Default precision specifier: 2
The following table lists the custom numeric format specifiers, along with a description of each specifier and examples that show the output produced by a particular format specifier. You can combine custom numeric format specifiers, as shown in the following example: #,#.00.
Format specifier Description Examples
0 Replaces the 0 symbol with the corresponding 1234.5678 (00000) -> 01235 digit if one is present; otherwise, 0 appears in the 0.45678 (0.00, en-US) -> 0.46 resulting string. 0.45678 (0.00, fr-FR) -> 0,46Replaces the # symbol with the corresponding 1234.5678 (#####) -> 1235
digit if one is present; otherwise, no digit appears 0.45678 (#.##, en-US) -> .46 in the resulting string. 0.45678 (#.##, fr-FR) -> ,46
. Determines the location of the decimal separator 0.45678 (0.00, en-US) -> 0.46 in the resulting string. .45 (0.0000, fr-FR) -> 0,4500
, Inserts a localized group separator character be- 1234567 (#,##, en-US) -> 1,234,567 tween each group. 1234567 (#,##, fr-FR) -> 1.234.567
For more information on formatting numeric values, see Microsoft documentation about custom numeric format strings in .NET Framework.
Formatting of Date Values
The following table lists the standard date format specifiers, along with a description of each specifier and an example that shows the output produced by a particular format specifier. To use the standard date format specifiers, you type the format specifier into the Format property of a text box, such as d.
Format specifier Description Example (en-US)
d Short date pattern 2009-06-15T13:45:30 -> 6/15/2009
D Long date pattern 2009-06-15T13:45:30 -> Monday, June 15, 2009
f Full date/time pattern (short 2009-06-15T13:45:30 -> Monday, June 15, 2009 1:45 PM
time)
Lesson 6: Using Variables and Expressions | 76
Format specifier Description Example (en-US)
F Full date/time pattern (long 2009-06-15T13:45:30 -> Monday, June 15, 2009 1:45:30 time) PM
g General date/time pattern 2009-06-15T13:45:30 -> 6/15/2009 1:45 PM (short time)
G General date/time pattern 2009-06-15T13:45:30 -> 6/15/2009 1:45:30 PM (long time)
M or m Month/day pattern 2009-06-15T13:45:30 -> June 15
t Short time pattern 2009-06-15T13:45:30 -> 1:45 PM
T Long time pattern 2009-06-15T13:45:30 -> 1:45:30 PM
Y or y Year/month pattern 2009-06-15T13:45:30 -> June 2009
The following table lists the custom date format specifiers, along with a description of each specifier and examples that show the output produced by a particular format specifier. You can combine custom numeric format specifiers, as shown in the following example: dddd/MMMM/yyyy hhtt:mm.
Format specifier Description Examples (en-US)
d The day of the month, from 1 through 31 2009-06-01T13:45:30 -> 1 2009-06-15T13:45:30 -> 15
dd The day of the month, from 01 through 2009-06-01T13:45:30 -> 01 31 2009-06-15T13:45:30 -> 15
ddd The abbreviated name of the day of the 2009-06-15T13:45:30 -> Mon week
dddd The full name of the day of the week 2009-06-15T13:45:30 -> Monday
h The hour (from 1 to 12), using a 12-hour 2009-06-15T01:45:30 -> 1 clock 2009-06-15T13:45:30 -> 1
hh The hour (from 01 to 12), using a 12- 2009-06-15T01:45:30 -> 01 hour clock 2009-06-15T13:45:30 -> 01
H The hour (from 0 to 23), using a 24-hour 2009-06-15T01:45:30 -> 1 clock 2009-06-15T13:45:30 -> 13
HH The hour (from 00 to 23), using a 24- 2009-06-15T01:45:30 -> 01 hour clock 2009-06-15T13:45:30 -> 13 Lesson 6: Using Variables and Expressions | 77
Format specifier Description Examples (en-US)
m The minute (from 0 through 59) 2009-06-15T01:09:30 -> 9
2009-06-15T13:29:30 -> 29
mm The minute (from 00 through 59) 2009-06-15T01:09:30 -> 09
2009-06-15T01:45:30 -> 45
M The month (from 1 through 12) 2009-06-15T13:45:30 -> 6
MM The month (from 01 through 12) 2009-06-15T13:45:30 -> 06
MMM The abbreviated name of the month 2009-06-15T13:45:30 -> Jun
MMMM The full name of the month 2009-06-15T13:45:30 -> June
s The second (from 0 through 59) 2009-06-15T13:45:09 -> 9
ss The second (from 00 through 59) 2009-06-15T13:45:09 -> 09
t The first character of the AM/PM desig- 2009-06-15T13:45:30 -> P
nator
tt The AM/PM designator 2009-06-15T13:45:30 -> PM
yy The year (from 00 to 99) 1900-01-01T00:00:00 -> 00
2019-06-15T13:45:30 -> 19
yyyy The year as a four-digit number 1900-01-01T00:00:00 -> 1900
2009-06-15T13:45:30 -> 2009
/ The date separator 2009-06-15T13:45:30 (yyyy/mm/dd) ->
2009/06/15
: The time separator 2009-06-15T13:45:30 (hh:mm:ss) -> 13:45:30
For more information on formatting date values, see Microsoft documentation about custom date and time format strings in the .NET Framework.
Variables and Expressions: To Add a Variable and an Expression
In the following activity, you will learn how to use variables and expressions in a report.
This activity is based on the U100 dataset. If you are using another dataset, or if any system settings
have been changed in U100, these changes can affect the workflow of the activity and the results of
the processing. To avoid any issues, restore the U100 dataset to its initial state.
Lesson 6: Using Variables and Expressions | 78
Story
Suppose that you are a technical specialist in your company who is working on simple customizations. A sales manager of your company has requested a report that displays data about vendors, so that vendors are grouped by vendor class. In each class, vendors should be numbered sequentially. You know that a colleague has created such a report, but without numbering. You decide to change the report that your colleague has created.
Process Overview
In the Report Designer, you will open the AP6550C6.RPX report, which is a modified copy of the Vendor Summary (AP6550C6) report. In the section in which vendors are listed, you will add two variables. The first variable will calculate the visibility of a vendor. The second variable will calculate the sequential number of a vendor in a particular class and will be incremented by one for only the vendors that are visible.
System Preparation
Before you begin performing the steps of this activity, launch the Acumatica ERP website with the U100 dataset preloaded, and sign in as a system administrator Kimberly Gibbs by using the gibbs username and the 123 password. Also, to prepare for use the file that is intended for this activity, do the following:
- Download the AP6550C6.rpx file.
- Open the downloaded file in the Report Designer.
- On the Report Designer menu bar, select File > Save To Server, which opens the Save Report on Server dialog box.
- In the dialog box, specify the connection string and sign-in credentials of your Acumatica ERP instance, type AP6550C6 as the report name, and click OK. The report is saved on the server.
Step 1: Adding Variables to a Report Section To add variables to a section of the AP6550C6 report, do the following:
- In the Report Designer, make sure that the AP6550C6 report (which you have saved to the server) is open.
- Select the groupHeaderSection1 (Header of group1) section. In the Properties pane, in the Behavior > Variables property of the Properties tab, click the More button. The ReportVariable Collection Editor opens, where you can add variables to the report and define their properties.
- In the Members pane of the ReportVariable Collection Editor, click Add to add a new variable.
- In the Misc > Name property on the right pane, type IsGroup1Visible to specify the name of the variable. You will use this variable to calculate the visibility of a vendor.
- In the Misc > ValueExpr property, click the More button. The Expression Editor opens.
- In the bottom pane of the Expression Editor, enter the following expression:
=IIF(@SupressZeroBal=True AND Sum(APHistory.FinYtdBalance) = 0, False,
True) AND IIF(@SupressInactiveVendors=True AND Vendor.Status = 'I',
False, True).
Lesson 6: Using Variables and Expressions | 79
This is a visibility expression of the groupHeaderSection1 (Header of group1) section. This expression is true if the summary balance of the vendor is not zero and the status of the vendor is not inactive. You can type this expression in the bottom pane of the Expression Editor or you can compose the expression by selecting necessary components from the le, middle, and right panes of the Expression Editor. - Click OK to close the Expression Editor.
- In the Members pane of the ReportVariable Collection Editor, click Add to add a new variable.
- In the Misc > Name property on the right pane, type Num, which is the name of the second variable.
10.In the Misc > ProcessOrder property, leave the default option—that is, WhileRead. This option directs the
system to process the values of variables while reading these variables.
11.In the Misc > ResetGroup property, select NewGroup to reset the value of the Num variable.
This means that in the NewGroup group, the variable should be calculated locally. If you have selected this
property, for each instance of the specified group, the variable has an independent value. At the end of each
group, the variable is reset.
12.In the Misc > ValueExpr property, type =$Num + IIF($IsGroup1Visible, 1, 0).
This means that the value of the Num variable will be incremented by 1 only if the IsGroup1Visible
variable is true.
As with the IsGroup1Visible variable, you can compose the expression in the Expression Editor. Notice
that you have specified IsGroup1Visible first and Num second because the Num value is calculated by
using the IsGroup1Visible value.
The following screenshot shows the variables added to groupHeaderSection1 (Header of
group1).
Figure: Variables in the ReportVariables Collection Editor 13.Click OK to save your changes and close the ReportVariable Collection Editor. 14.On the Report Designer window toolbar, click Save.
Step 2: Adding a Text Box to Display Numbers To add a text box to display the numbers for the vendors, while you are still working with the AP6550C6 report in the Report Designer, do the following:
- From the Tools pane, drag the TextBox element to the le side of the groupHeaderSection1 (Header of group1) section, and enter the =$Num value for the added text box. Lesson 6: Using Variables and Expressions | 80
- Optional: To set a uniform style for text boxes in the section, while the text box is selected, in the Appearance > StyleName property on the Properties tab, type Normal.
- On the Report Designer window toolbar, click Save to save the report on the server.
The report in design mode is shown in the following screenshot.
Figure: Report in design mode
Step 3: Viewing the Report To view the report, do the following:
- In Acumatica ERP, open the S150 Vendor Summary (AP6550C6) report form by searching for its identifier.
This report, which you have modified in this activity, has been published in the U100 dataset. That is, it has been added to the Site Map (SM200520) form, and you can access it in Acumatica ERP. - On the report form toolbar, click Run Report.
The resulting report is shown in the following screenshot. Lesson 6: Using Variables and Expressions | 81
Figure: The Vendor Summary (AP6550C6) report with vendors numbered in each vendor class Lesson 7: Using Parameters and Filters | 82