The Expression Builder is a utility that allows you to create an expression and assign it to a variable. An expression is a calculation used to determine a value and is usually made up of operators and operands.
The operators are listed below:
Operator |
Definition |
+ |
Addition |
- |
Subtraction |
* |
Multiplication |
/ |
Division |
** |
Exponentiation |
+ |
Concatenation (with no space) |
& |
Concatenation (with one space) |
Operands, which are the part of the expression the operators are acting on, can be variables, constant values, or column names.
An example of an expression to calculate the total charge for a purchase (quantity multiplied by price), would be the following expression:
operator
|
quantity * price
\ /
operands
You can assign a variable to an expression and then place the variable on the report. When the report is used, it will display the value of the variable. A variable is a name you create that will contain the answer to an expression. For the example, vQuantity = (12 * 10), the variable vQuantity contains the value 120.
Lookup Variables
Another example of an expression would be to create a lookup variable to capture the value within a table. For example, if you wanted to know the credit limit for a customer that is stored in another table, and you already know the customer's ID#, then you can create an expression for a variable and display that variable on the report. The variable may look like this: vCrediLimit = CreditLimit IN Customer WHERE CustID = CustID.
Aggregate Functions
An aggregate function can be used to provide summary data about a group of rows in a table or for all rows in a table. Aggregate functions may be used like this: (MAX(QuoteReceivedDate)) in QuoteHeader where PartID = .vPartID
AVG |
Computes the numeric average of CURRENCY, DOUBLE, NUMERIC, REAL, INTEGER, DATE, or TIME data types. R:BASE rounds averages of integer values to the nearest integer value and currency values to their nearest unit. |
COUNT |
Determines how many non-null entries there are for a particular column item. |
LISTOF |
Creates a text string of the values separated by the current comma delimiter character. The LISTOF function can be used to populate a variable with a list of values from multiple rows. |
MAX |
Selects the maximum value in a column. This option accepts columns with any data type except BIT, VARBIT, VARCHAR, and BITNOTE. * |
MIN |
Selects the minimum value in a column. This option accepts columns with any data type except BIT, VARBIT, VARCHAR, and BITNOTE. * |
SUM |
Computes the numeric sum of CURRENCY, DOUBLE, REAL, NUMERIC, or INTEGER data types. |
* Selecting aggregate functions, such as MIN and MAX, requires that R:BASE keeps an accumulator and choose to only use the first 80 characters for NOTE values. This matches the fact that if you sort on NOTE fields, the sort will be based on the first 80 characters only.
R:BASE Functions
Expressions can contain one or more R:BASE functions. A function differs from an operator in that a function provides a predefined complex expression to evaluate standard mathematical, trigonometric, financial, or logical functions without requiring the user to enter the formula in a complete R:BASE expression. Function names are reserved words.
To define an expression in a report:
| 1. | In the "Report Designer" window, choose Variables: Add/Edit Variables... |
The "Expression Builder" dialog box is displayed.
At the top, the "Variables" panel displays a list of defined variables. Values stored in this panel include the variable name, data type, expression, and report section, or band, that the variable is associated with.
•Move Up - move a variable up the order in the list. The button would be used if a newly added variable must be moved up the order list so that another variable lower in the list can use it.
•Move Down - move a variable down the order in the list
•Copy Selected Expression(s) to Clipboard - copies the selected variable(s) to the clipboard, to be placed in another report
•Paste Expression(s) from Clipboard - pastes the variable(s) from the clipboard to the current report
•Roll Back to Original Declaration - reverses changes made to the selected variable definition
•Delete - deletes the selected variable. When doing so, R:BASE will ask if you wish to remove that object from the report as well.
The "Define Expression" panel allows you to create new variables, as well as edit any existing variables. Available fields include options for the variable name, data type, expression, and the section for the variable to calculate on. An option is available to edit the "Expression:" memo field font, which is helpful to clearly view all characters such as commas, parenthesis, etc.
The Expression memo field can also accept curly brace comments "{}" within expressions, to document the purpose of the variable, and perhaps the logic behind a complex expression.
•Add - adds a new variable to the list
•Change - changes the data type, expression, section, and/or order for a selected variable
•Global - adds a variable to the list of global variables within R:BASE. After closing the Report Designer, the variable definition may be verified at the R> Prompt with the SHOW VAR command.
Note: For more information about expressions, see "Expressions".
