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:
- Grouping data in list reports
- Sorting data in list reports and other report types
- Aggregating data in list reports
- Sorting by aggregation results
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
- When grouping on multi-select fields, the subtotal counts could potentially include repeated data, but the overall total will be correct.
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.
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.
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:
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
Hide field - you can hide/show from any column header
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).
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.
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. Note: If you choose to sort by a multi-select list, not only is the field relative to the report sorted, but the values in the cell will be sorted in the same direction.
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.
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.
The Aggregation section is where you choose the aggregation operation for numeric fields. Any aggregatable (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.
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
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
Display aggregation totals only
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:
Report grouped by category, but sorted by sum of Hours to Comp for each category, lowest to highest.