Calculated fields (also called "unbound columns" or "unbound fields") are the report fields that you create.
A calculated field is not part of the report’s original data source, but can be derived from the data based on an expression (a formula) that you define.
For example, you might define a calculated field to estimate your commissions payable as a percentage of sales. Or a field that plucks the month (or quarter) from a date.
To create a new calculated field:
Value - Sorts using the field’s underlying value.
Display Text - Sorts using the field’s displayed (formatted) value.
Custom - If your Mercury installation has Custom Sorts defined, those will also appear in the list.
Create your expression in Expression Editor with the help of the Expression Elements lists beneath it.
The Expression Editor and its associated lists are as shown in the picture below.
For each Category there is an associated list of Items. And for each specific item, additional information is available in the Item Information box.
The Categories List contains the following:
Variables - Properties of the report’s underlying data set. Functions - Built-in functions that can be used in your expression
Fields - All the fields available to the report. (As you create calculated fields, they will also appear in this list, so that you can use them in subsequent calculated fields.)
Parameters - The parameters used by the database query or stored procedure that supplies the report’s data
Constants - predefined names or symbols for special values such as Null, True, and False.
Operators - Arithmetic and other operators
Functions
Creating Your Expression (Formula)
You can enter your expression manually from the keyboard by typing function names, field names, operators and so forth. You can also use the Categories and Items lists to help you build your expression by choosing items with the mouse.
In most cases you’ll end up using both. Most people choose field-names and functions with the mouse, and then use the keyboard for operators (like a plus sign) or function parameters.
Field names must be surrounded by square brackets - e.g. [OrderDate].
Date-time constants must be wrapped in hashtags (#) - e.g. [OrderDate] >= #1/1/2018#
Surround strings with single-quote marks (apostrophes) - e.g. [Region] = 'North'
To represent a parameter value, use the prefix "Parameter." - e.g. Parameter.CMPNYNAME
To represent a null reference (one that does not refer to any object), use a question mark - e.g. [Region] != ?
From the Categories List, select a category. Its associated items appear in the Items list..
To get information about an item, single-click it. The information will appear in the Item Information box.
To insert the item into the formula being created in the Expression box, double-click on the item.
If your expression isn’t valid – perhaps you’ve typed in a fieldname but didn’t get it right, or left out a parenthesis or a quote mark – you’ll see a “syntax error” message appear below the expression. You won’t be able to save the expression until the error has been corrected.
In this example a field-name (column-name) has been hand-entered. It’s not in square brackets but more importantly it’s not even recognized as a field name in the data set.