Skip to main content
Planview Customer Success Center

Grouping and Sorting for List Reports

The ability to group and sort data is applies primarily for List reports. Reports that have axes, such as bar/column charts, have simple sorting capabilities, such as ordering the axis values or bars/columns, or ordering by count. All grouping/sorting options for all reports types are configured on the Display tab (you can also configure list report sorting in the Preview Pane).

This topic covers:

Good to know:

  • Any field can be sorted/grouped, provided it is in the list of fields included in the report on the Data tab
  • Any Standard number field can be aggregated, provided it is in the list of fields included in the report on the Data tab. For user-defined number fields, if you do not select a calculation class when creating the field, the user-defined number field will not be aggregateable. The calculation class you choose determines the types of operations available for aggregation type.
  • Every level of grouping can be sorted, in addition to the report as a whole. For example, you can group by Priority and then sort by Status.
  • On chart types, axis values are auto-grouped (there is no Grouping section on the Display tab of the chart types)
  • Can sort on aggregation results

Grouping

You can group on any field included in the list of fields you chose in the Data tab. The fields are shown in the Field drop list. You can choose up to 3 fields and choose a direction for each. Note that if you use the same fields for grouping and sorting, the directions will stay in sync.


grouping.png

 

Priority column - Indicates the order in which the fields are grouped (if more than 1 field).

Field - Each droplist contains a list of fields that are used in the report definition. Select a field to group by.

Direction - Specifies the direction to group. Ascending/Descending directions for simple fields; list fields have the additional options of List Order, and List Order Reverse. For example, the List Order might be Small, Medium, Large, and List Order Reverse is Large, Medium, Small. Note that the Direction of fields used for grouping AND sorting remain synchronized.

2020-05-21_17-38-45.png

If you change the sort direction, the group direction will change to match.

Display repeated data - For example, if you have a Project report and you group by project Owner, the Owner value will NOT display on the row with every project owned by each resource - it will appear once for the group. If you wish to see the value on each record, enable this setting.

Display/suppress repeated data

The Grouping section includes a setting that controls whether to suppress or display repeating rows - it's called Display repeated data. Enable this setting if you have grouped your report by at least one field and you want to display the repeated data. By default this setting is disabled (unchecked), meaning the data will not display.

For example, if you have a Project report and you group by project Owner, the Owner value will NOT display on the row with every project owned by each resource - it will appear once for the group. If you wish to see the value on each record, enable this setting.

See examples below of displaying vs showing data:

2020-06-09_15-51-07.png

2020-06-09_15-46-53.png

2020-06-09_15-50-30.png

2020-06-09_15-46-06.png

Grouping on a hidden field

If you want to group on a field but not display it, select it as a Grouping Field. Then, in the column header menu, open the Columns sub-menu and uncheck the field you want to hide. The field remains available as a Grouping field. For example, the screenshots below show how to group by Hour to Complete, and then hide the field from the report.

Group by Hours to Complete

2020-06-09_15-54-29.png

Hide field - you can hide/show from any column header


hide column.png

Sorting

Sorting is available for many report types, although most useful for List reports and bar charts, where you might want to order the bars. You can sort on any field included in the list of fields you chose in the Data tab. The fields are shown in the Field drop list. You can choose up to 3 fields and choose a direction for each. Note that if you use the same fields for grouping and sorting, the directions will stay in sync.


2020-06-03_15-56-39.png

Priority column - Indicates the order in which the fields are sorted (if more than 1 field).

Field - Each droplist contains a list of fields that are used in the report definition. Select a field to sort by.

Direction - Specifies the direction to sort. Ascending/Descending directions for simple fields; list fields have the additional options of List Order, and List Order Reverse. For example, the List Order might be Small, Medium, Large, and List Order Reverse is Large, Medium, Small. Note that the Direction of fields used for grouping AND sorting remain synchronized.

2020-06-03_15-56-16.png

If you change the sort direction, the group direction will change to match.

Sorting in the Preview Pane

Remember that you can also configure sorting directly in the Preview Pane by using the column header menus. We offer the tabular sorting table as an easier way to visualize the sort order (you don't have to click into the column header menu to see the sort position) and to associate a field directly with a priority. If you are configuring multiple sort fields, the Sorting section might be a bit easier to use.

Regardless of which technique you use to configure sorting, the alternate technique will stay in sync. 

Aggregation

The Aggregation section is where you choose the aggregation operation for numeric fields. Any aggregateable (numeric) field included in your list of fields will appear in this section. If you have not included any numeric fields in your report, the Aggregation section will not appear. Note that if you include the Count field in your report, it also can be aggregated.

2020-06-11_14-25-06.png
 

2020-06-11_14-19-25.png

Provided you have at least one numeric field in your report, you will see the Aggregation section - the example to the left shows Actual Hours and Hours To Comp field, drawn from the list of fields that were selected for the report. Aggregation does not happen unless you pick an aggregation type from the drop list.

Aggregation - Choose the aggregation type. The following types of aggregation are supported: Average, Minimum, Maximum, and Sum. Aggregation is available only for fields that make sense. For example, you cannot Sum on Title field values (and Title would never appear in the list of possible fields to aggregate), but you can sum the value of Hours to Complete. 

Once you apply an aggregation to a numeric field by selecting the Aggregation type from the drop list, a whole set of control appears for configuring attributes of the aggregation value(s).

Display aggregation totals only - You can render the entire report, including sub-totals and a grand total, or you can render the sub- and grand-totals only. See example below.

Positioning totals values

  • You can position the grand aggregation values, choose the font type, style, and size; the same controls are available for sub-totals as well.
  • Position options for both grand and sub-totals are Show at Bottom, Show at Top, and Do not Show.
  • Font options for both grand and sub-totals include font type, Style, and Size.

Displaying Aggregation Totals

You have the option of displaying aggregation totals with your report content, or just the totals themselves. See the examples below:

Display aggregation totals with report content

2020-06-11_18-12-31.png

Display aggregation totals only

2020-06-11_18-12-08.png

Sorting by Aggregation Results

You can sort a report according to the results of an aggregation. For example, say you have grouped your report by project Category, and then want to sort the categories by the sum of Hours to Complete for each project category.

Once you enter a field in the Grouping section, such as project Category, you can sort your report according to the aggregation results of a numeric sort field. For example (assuming the Grouping field = Category), use a numeric field such as Hours to Complete as a sort field, and then apply an aggregation (such as Sum) to the same field. Each value of that field will be sorted in each group, and then each group as a whole will be sorted by the aggregated value of that field. See setup and example output below:

Setup:
2020-06-11_14-46-53.png

Report grouped by category, but sorted by sum of Hours to Comp for each category, lowest to highest.

2020-06-11_14-44-59.png