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.

To create a new calculated field:

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

  • A window similar to the following will be displayed.

  • Enter a caption for the calculated field

  • 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 these, follow this link.

  • Select the summary type of the field.
Summary Type applies only when the calculated field is shown in the "Data Area" of a Pivot-report.
  • 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 your Mercury installation has Custom Sorts defined, those will also appear in the list.
  • Select a data type.

    1. Integer

    2. Decimal

    3. DateTime

    4. String

    5. Boolean

    6. Object- no specific type is defined

  • Create your expression in Expression Editor, which is the big box in the middle of the form together with the sections beneath it.

    For details on Expressions, click here.

  • 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 shown in the picture below.

For each item in Categories List, there is an associated list of sub items that appear in the Categories Sub List. And for each specific item in the Categories Sub List, additional information appears in the Sub List Information box.

The Categories List contains the following items:

  • Functions - Built-in functions that can be used in your expression
  • Operators - Arithmetic and other operators
  • 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.)
  • Constants - For Boolean and Null values
  • Parameters - The parameters used by the database query or stored procedure that supplies the report’s data
  • Variables - Properties of the report’s underlying data set.

You can enter your expression manually from the keyboard (function names, field names, operators), or you can also use the Expression Elements List to help build your expression by choosing items with the mouse.

Entering Elements Using the Keyboard:

  • A data-field name 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] != ?

Entering Elements Using the Mouse:

  • From the Categories List, select a category. Its associated items appear in the Categories Sub List.

    To get information about an item in the Categories Sub List, single-click the item. The information will appear in the Sub List Information box.

  • To insert the item in the Categories Sub List into the Expression Editor, 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.


    • Related Articles

    • 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 ...
    • 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 remove fields. There are also some menu options that provide alternative ways to make ...
    • Reports

      The Manage Reports form lets you create, edit, and perform other report maintenance tasks, as well as determine which users can access each report. To open the Manage Reports window: Select Reports from Admin Tools from the toolbar on top of the ...
    • Right-Click Menus

      Right-click a filter-area icon Hide this Field - Removes the field. (To get it back, see Show 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 ...