Top N

Top N

Mercury allows you to limit your pivot report results to only the topmost values in a particular category (or categories).   "Topmost" is based on the current sort-order of the field(s) you’re restricting; if you change the sort-order the Top N result will automatically change too.

Items that don’t qualify for your Top N can be hidden completely or combined and shown as a separate "Other" category.  Totals and grand totals may change depending on whether you include "Other".

Top N is only available in Pivot reports, and only row-area and column-area fields can have Top N limits applied.​
Whenever you open the Top N grid, it shows the fields that are currently in your row and column areas .  If you drag a field with a Top N setting out of the row or column area, the field’s Top N is cleared, and the field won’t appear in the Top N grid when you next open it.  If you subsequently drag that field back into the row or column area, that field’s Top N setting is NOT automatically restored.

To open Top N settings, you can either:

  • Click on Top N Values from Pivot Report ribbon tab.

- or - 

  • Right-click a row-area or column-area field icon, and choose "Top N…"

A window similar to the one below will be displayed.  All the current row area and column area fields are shown, with corresponding settings (if any).

The Top N settings grid


Column

Description

Show Top

The number values (or the percentage level) that you want to see.  "All" means "no Top N; show everything".

Top Value Type

Choose between Value and Percent.

Value means you’ll see a specific number of items.

Percent means that you’ll see enough items to make up a desired percentage.

Example: say that there are twenty items in your row-area, and you specified a "Show Top" of 10.  Ignoring the effect of "Show Other Values", a Value Type of:

  • Value means you’ll now see 10 row-area items
  • Percent means you’ll see 2 row-area items (10% of the available 20).

For Field

The field to which grid row’s the Top N settings will be applied.  The field becomes highlighted when there is (or will be) a Top N.

Show Other Values

If checked, includes on your report all the results that don't meet the Top N criteria. These remaining results are combined into a single "Others" item.

Reset

Clears the field’s Top N setting.  A "Reset" link appears only if the field has a Top N setting.

The icons above the list can be used to change the way the fields are grouped in the grid.


 

All fields are shown together in the list, regardless of area.
 

Column and row area fields are shown in separate groups.

Top N With Multiple Row or Column Area Fields

Pivot-reports can (and often do) have multiple row-area and/or column-area fields.  Top N is applied based on the field’s position in its area, and is affected by outer fields.

Take a report like the one below - it has three row-area fields.  

  • PostingType is the outermost field
  • each PostingType value has multiple AcctCategory values that belong to it
  • each AcctCategory has multiple AccountNums
  • AccountNum is the innermost field

Top N is applied anew based on the field’s next-most outer grouping - its "parent", if you will.

  • If you apply a Top 3 to AccountNum, you’ll see three AccountNums - for every AcctCategory.  
  • If you apply a Top 3 to AcctCategory, you’ll get three AcctCategory values for every PostingType.  
  • PostingType is the outermost row-area field, so there won’t be a "repeated" Top N for PostingType.

Top N is dynamic.  If you have Top N in effect for one or more of your row-area fields, and you change the order, Top N is re-applied automatically based on your new order.  If you change the sort-order of a field that has a Top N, your results may change.




    • Related Articles

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

    • Custom Tables

      Custom Tables Occasionally you may find that your company’s database(s) are missing some information that would help your reporting and analysis. For example, perhaps you are doing multi-company reporting from your accounting platform, and the ...

    • Working with Columns

      To show or hide columns: Right click on any column header and select   Show Column Chooser.                                                                                                                                                                ...

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