Skip to main content
Planview Customer Success Center

Grouping and Sorting for List Reports (Beta)

The ability to group and sort data is applies primarily to 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. 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 must select select a calculation class when creating the field or you will not be able to use the user-defined number field in an aggregation. The calculation class you choose determines the types of operations available for aggregation type.
Sorting, Grouping, and Totaling

You configure sorting, grouping, and totaling in the Sorting section, shown below. To sort, choose up to 5 sort fields by selecting fields from the Field droplist. This list offers all fields you chose in the Data tab. The sort displays in Priority order; if, for example, you have two sort fields and then remove the Priority 1 field, the Priority 2 field will bump up to the Priority 1 position. Any field you sort on can also be a Group By field. You can group without totaling, but you cannot total without grouping. The user interface will not let you do anything silly :)

The following is an explanation of how to sort and group u

2020-10-13_16-52-43.png
  • Priority - The Priority indicates the order of the sort - the Priority 1 field is sorted first, Priority 5 field is sorted last. 
  • Field - Choose the field to sort on. The Field droplist contains all fields in the report, including fields from related entities. For list and crosstab reports, you can have up to 5 sort fields. 
  • Direction - Choose the sort order. List Order and List Order Reverse are shown if the sort field is a list.

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

Sorting in the Preview Pane

Remember that you can also configure sorting directly in the Preview Pane by using the column header menus.

2020-10-14_15-54-06.png

It's generally easier to sort using the Sorting grid as you can see all the sort fields in one glance. Regardless of which technique you use to sort, the alternate technique will stay in sync. There is no way to group using the Preview Pane.

Grouping and Totaling

  • Group By - This droplist provides the options for grouping and totaling values. Grouping organizes data around a common characteristic, such as a Status or a Category; totaling performs a calculation on a group of numeric values, such as Actual Hours.  
    • Choose Group to organize your data based on a specified field. The field title will appear at the top of each group. For example, if you group on project Status, then you'll see "Proposed" on a line of its own and then all the projects of status Proposed. You can suppress the repetition of the group by field (the default), or decide to show them. See Displaying vs Suppressing Repeat Data.
    • Choose Group and total if in addition to grouping you wish to aggregate values and display sub- and/or grand totals. You must configure an aggregation field to create the totals (See Aggregation.) If you happen to pick Group and total and don't configure an aggregation, then the system will simply perform the grouping.

Displaying vs Suppressing Repeat Data

Enable this setting if you have grouped your report by at least one field and you want to display the repeated data (the "group by" value). 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 the example below.

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

Aggregation

The Aggregation section is where you choose the aggregation operation for numeric fields. Any numeric field included in your list of fields will appear in the Field column. 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-10-14_16-12-33.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 the Hours To Comp field, which was selected for the report in the Data tab. Aggregation does not happen unless you pick an aggregation method 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, another set of controls appear 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

  • Grand aggregation position - Options are: Show at bottom, Show at top, Do not show
  • Grand aggregation font - Choose the font type, style, and size
  • Sub-total position - Options are: Show at bottom, Show at top, Do not show
  • Sub-total font - Choose the 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 - examples of both shown 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 by sorting with the aggregation field. 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 select a field in the Sorting section, such as project Category, you can sort your report according to the aggregation results of a numeric sort field by choosing "Group and total" for the Group By. For example (assuming the Grouping field = Category), apply an aggregation (such as Sum) to the numeric field Hours to Complete. 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-10-14_16-06-09.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

Grouping on a hidden field

If you want to group on a field but not display it, select it as a Group By field. Then, in the column header menu, open the Columns sub-menu and uncheck that field to hide it. The field remains available as a Group By 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-10-14_16-18-22.png

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


hide column.png

Grouping on a Multi-select Field

When grouping by a multi-select field, be aware that sub-total counts could include repeated data. However, the overall total count will be correct. For example, say you have a multi-select field called Numbers that contains the values 1, 2, and 3, and you have 3 projects: Project A, Project B, and Project C. Each project is assigned the values below:

  • Project A is assigned list values 1, 2
  • Project B is assigned list values 2, 3
  • Project C is assigned list values 1, 3 

Create a report summing actual hours for the projects; group the report by the multi-select list field Numbers. Your output will look like the following: the subtotals are correct - 20 hours each - but the grand total is 30, which is correct (although the subtotals imply the grand total is 60).

group_by_multi.png