Calculated Fields

Calculated Fields

Calculated fields (also called "unbound columns" or "unbound fields") are report fields that you create. 


A calculated field is not part of the report’s original data source but is 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 or calculates a “still outstanding” amount based on a billed amount and a paid-so-far amount. 


Create a new calculated field: 

  1. Click the Add field button from the Report’s ribbon.

  1. A window similar to the following will be displayed. 

  1. Enter a caption (a field-name) for the calculated field.
  2. Select a data type (the available types may vary based on your version of Mercury).
    1. Integer
    2. Decimal
    3. DateTime
    4. String
    5. Boolean
    6. Object- no specific type is defined.
  3. Optional: Enter the display-format you want (applies to numbers and dates only). Mercury accepts standard Excel-type formation notation (e.g. "##,###.00", or "m/d/yy") as well as standard .NET format abbreviations.  
For details about .NET formats, follow this link.
  1. Select a Sort Method.
    1. Value - Sorts using the field’s underlying value.
    2. Display Text - Sorts using the field’s displayed (formatted) value.
    3. Custom - If you have created any Custom Sorts for Mercury, those are available here.
  2. Insert Column - Select the column-position where you would like to have the calculated field appear.  You can choose from First, Last or After (after a particular field).  As with any field, you can drag it to a new position later.
  3. Create your expression / the formula, in the Expression Editor, which is the big box in the middle of the form together with the 3 sections beneath it.  More information on creating Expressions is available in the next Section.
  4. Click OK. The calculated field will appear in the report.

Expressions

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:
  1. Variables - Properties of the report’s underlying data set. Functions - Built-in functions that can be used in your expression.
  2. 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.)
  3. Parameters - The parameters used by the database query or stored procedure that supplies the report’s data.
  4. Constants - predefined names or symbols for special values such as Null, True, and False.
  5. Operators - Arithmetic and other operators
  6. 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.

Entering Elements Using the Keyboard:

  1. Field names must be surrounded by square brackets - e.g. [OrderDate].
  2. Date-time constants must be wrapped in hashtags (#) - e.g. [OrderDate] >= #1/1/2018#
  3. Surround strings with single-quote marks (apostrophes) - e.g. [Region] = 'North'
  4. To represent a parameter value, use the prefix "Parameter." - e.g. Parameter.CMPNYNAME
  5. To represent a null reference (one that does not refer to any object), use a question mark - e.g. [Region] != ?

Entering Elements Using the Mouse:

  1. 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.
  2. To insert the item into the formula being created in the Expression box, double-click on the item.
If you make a mistake - for example, if you insert the wrong item or if you insert it in the wrong place - click in the Expression Editor box and use the keyboard to edit your expression.

Invalid Expressions

If your expression isn’t valid, for example, perhaps you’ve typed in a fieldname but didn’t get the spelling 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.

    • Related Articles

    • Calculated Fields

      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 ...

    • Customizing Fields, Rows, and Columns

      Rearranging fields In most cases you can rearrange fields by dragging and dropping. You can change the field order, exchange row-area and column-area fields, and move fields among different areas. For example, you could drag a field from the filter ...

    • Reports

      Manage Reports The Manage Reports form lets you create and edit reports, assign User accesses, and perform other maintenance tasks. (Note: Mercury allows users who run the report to modify the layout and save as a Favorite, so you don't have to worry ...

    • Right-Click Menus

      Right-click a filter-area icon Hide this Field - Removes the field. (To get it back, see the Field List below.) Order - Moves the field to a different location in the same area: Move to Beginning - Moves the field to the beginning of the list. Move ...

    • The Pivot Report Ribbon

      General Run - Run the selected report (values for all parameters must be supplied for this to be enabled). See Running Pivot Reports for more information. Print and Quick Print - See Printing Reports. Export - See Exporting Report Results for more ...