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” under Pivot Reports > Customizing Fields, Rows, and Columns.

You can change the sort direction (ascending, descending) of a column in several ways.

Setting the sort direction using Field icons

The current sort direction is indicated by the Up or Down triangle in a column or row header.

To change the direction: click once on the triangle, and the direction of the sort is reversed.

In the example below, you can see that the sort direction on one of the row-area fields has been changed from ascending to descending. The other row-area fields, as well as the column-area field ("Period" in this example) remain sorted in ascending order.

Sorting using the Field List

You can also use the Show Field List dialog box to specify the direction of row-area and column-area field sorts.

1. To open the field list, either:

   • Right-click on a field and select Show Field List; or

   • Click the Show Field List button in the Pivot Report’s Ribbon.

2. In the Row Area or Column Area sections, click on the triangle next to the field name.

Sorting Using Data-area values

You can sort also sort row-area fields based on your summarized data instead of on the field-values themselves.

For example, if you have your customers in the row-area, maybe you want them sorted not by their names - but by who owes you the most money.

• Right-click on a data area field header.
• From the menu that appears, choose the row-area field you wish to sort. The data field header acquires a little triangle with a bar across the point. The triangle indicates the sorting direction; click to change it.
• Repeat as desired. Mercury remembers your sorting choices, as you sort additional row-area fields.
• To remove a sort, right-click the data field header and select the row-area field again to remove its check-mark.

If your pivot-table looks something like this:                                                                                                                                                   

Then on right-click on either "DocAmount" or "Outstanding", you’ll see this:                                                                                               

After you have sorted, it looks like this:                                                                                                                                                            

Note:

• The triangle in the Outstanding field header
• The check mark on the Customer menu item
• The customers in the report are no longer sorted in alphabetical order - they’re now sorted based on the Outstanding value (in descending order of the outstanding field).

Grouping

Grouping in pivot reports is a byproduct of your row-area and column-area fields.

By nature pivot reports summarize data, and your row-area and column-area fields determine how that is done.

As you can see in the screenshot below, the data are grouped by the PostingType and Account-category row-area fields, and also by the Year and Quarter column-area fields.                                                                                                                                                 

If you need to group a field based on something other than the set of its field values, you can create a Custom Group to do this.

Here are a couple of examples.

1. Perhaps your data include part numbers, and in your particular scheme the part numbers look like 1234-abc-00293, where the "abc" in the middle (or "def", or "hzq", or whatever it is) tells you how the part is manufactured (injection molding, extrusion, 3-d printing). You want to group not on the entire part, but just on the that 3-letter code in the middle.
2. You’re running a sales contest. Your sales staff has been divided into three teams, and you want to group by sales team.

See Tools > Tools > Groups for information on how to create custom groups and use them in reports.