You can create your own custom grouping functions to consolidate data as you please. For example, you might want to create special aging buckets for your accounts receivable. Or maybe you assign support resources based on the first letter or two of a customer ID#, so you want to group that way. Customers from A-E are handled by one support team; F – H another, and so on.

Once a Group has been created, it can be associated with any compatible field from any report  (That is, any field that contains the same type of data that the group is based on -- it could be a number, a date, or a text string). You can do this as part of the report definition (admin-users: see Reports for more), or you can apply a custom group on the fly when analyzing report results on the canvas (all users: see grouping in pivot/table reports). 
Instead of the original field value, the transformed (grouped) value is what will be shown in the report output.

To open the Manage Groups window:

  • Select the Tools > Tools ribbon at the upper right of the main Mercury window, and then click Groups.

  • A window similar to the following is displayed:

To create a new Group

  • Select New from the toolbar on Manage Groups window.

  • A new row will appear in the grid, indicated as such by the red asterisk ("*") in the Name cell.

    The list below describes the columns in the grid.

    1. Name - A name for the Group. In order to use this Group on a report, you'll associate this group name with a field on a report. See Reports for more information.
    2. Input Data Type - The data type of value supplied for evaluation through the Expression. The type of data that you want to group -- numbers, dates, or strings (text) that you can use to create an Expression.
    3. Output Data Type - The data type of result returned by evaluating the Expression. This is also the data type of the detail values that define each group (more on the detail below).

      You are defining this Group independently of any particular report-field, so Mercury has no way of knowing here what the data-type is supposed to be.

    4. Expression - A formula whose resulting value determines the specific group-value (the bucket) to which the underlying field value belongs.

      When creating your expression, use "[Value]" (without the quotes) to refer to the value of the report's field when the expression is being evaluated as the report is run.

    5. Operator - Specifies how the Expression's result is compared to the list of possibilities ("reference values") in the detail area in search of a matching item.
  • The specific groupings (the individual groups that make up this capital-G Group, if you will) are defined in the detail area. The result of the Group's Expression (evaluated for each data record) is compared against Refer.Value (the "reference value"), using the specified Operator. This determines the data value's specific small-g group.

    If data do not fall under any of the Refer. Value, a special group is created. The header of this group will be Others.
  • Control 



    Adds a new detail item 


    Deletes the selected detail item 


    Moves the selected item higher in the list 




    Moves the selected item lower in the list 



    Reverses the arrangement of the rows in the grid 

  • Click Save to create the Group.

To edit an existing Group

  • Select Edit from the Manage Groups toolbar. This is will enable all the fields for edit.

  • Make changes as desired. You can change the information in the main Group as well as add/change/delete the Group's detail items. You can make changes to as many Groups as you wish.

  • Click Save on toolbar to save all your changes.

To clone an existing Group

  • Select the Group that you wish to duplicate, and click Clone

  • A new row will appear at the end of the grid. The clone is identical to the original Group except for the Name, which is blank.

  • Provide a Name for the new Group.
  • Make other changes as desired, to the main Group and/or its detail items.

  • Click Save on the toolbar to save all your changes.

    To discard all your changes and reset the form, click Reset on the toolbar.

To delete an existing Group

  • Select the Group that you wish to delete.

  • Click Delete on the toolbar. At the confirmation prompt, click Yes to delete the Group; No to cancel and retain the Group.

To discard changes in progress

To discard all unsaved changes (as part of New, Edit, and Clone), click Reset on the toolbar.

At the confirmation prompt, choose Yes to confirm; No to continue making changes. 

Using a Group in a Report

A group can be associated with any field in a report - bound or unbound (calculated). If you want to see a field’s custom grouping and original data at the same time, you should create an unbound field for grouping purposes. The unbound field will automatically be grouped when displayed, while the original field will be shown without grouping.

For more on unbound (calculated) fields, see Admin Tools > Reports > Fields.

There are two ways to assign a group (or change a grouping) to a report field.

  1. When creating or editing a report - use the "Fields" tab’s "Group Method", as shown below.
  2. On the report canvas, you can right-click a field icon and choose a grouping (or remove your grouping) on the fly.  See Pivot Report > Right-click Menus or Table Reports > Right-click Menus for more.

To assign (or change) a Group using the report-layout’s Fields tab

  • Create a New report or Edit an existing report
    Refer to Reports for more information on creating or editing a report.
  • On the Fields tab
    • If you want to add a new (unbound) field for grouping purposes, do it  now - again, see Reports. 
    • For the field to be grouped (or re-grouped, or un-grouped), click the Group Method cell drop-down, and choose the group to apply (or "(None)" to remove any custom grouping).
    • Select the name of the Group from Group Method column.
Mercury will try to present you only with groups appropriate to the field you’re working with, but you should still ensure that the group you select is meaningful for your purposes.

Remember to Save your changes.

Now, when you use this field in a report to group data (for example, as a pivot-report row- or column-area field), the field’s custom-grouped values will be used.

    • Related Articles

    • The Table Report Ribbon

      General Run - Run the selected report (values for all parameters must be supplied for this to be enabled). See Running Table Reports for more information. Print - Print the current report. See Printing Table Reports for more information. Export - ...
    • Report List

      The Report List contains all the reports available to you. You can change its appearance in several ways. You can change how the list is presented To change the list appearance, use the small icons above the search box. Icon Description The pin ...
    • Users

      Every Mercury user must have a corresponding User record that defines report permissions and allows Mercury to manage user-specific content such as Favorites and report-colors. Only users who belong to the built-in SYSTEM (System Administrators) ...
    • Sorting and Grouping

      Sorting Pivot reports sort based on the arrangement of your row-area and column-area fields. To change the order in which columns are sorted, simply rearrange your report. For more information on how to do this, see the topic “Rearranging fields” ...
    • Right-Click Menus

      Right-click on a column header Sort Ascending - Sorts the column data in ascending order. See Searching and Sorting. Sort Descending - Sorts the column data in descending order. See Searching and Sorting. Clear Sorting - Removes sorting from ...