Grouping and Sorting List Reports

Sorting, Grouping, and Totaling

You configure sorting, grouping, and totaling in the Sorting section of the Display tab, shown below. This Field droplist contains all fields you chose in the Data tab. Select up to 5 sort fields and note that he rendered report is sorted by Priority, so pay attention to the order.  A value may appear only once in the list; to reorder the list you might have to temporarily set a field to blank, reselect the value in the new position, and then replace the blank with the desired value, if necessary. Any field you sort on can also be a Group By field.

The screenshot below is the Sorting section on the Display tab for a list report.

Display repeated data 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. Sorting in the Preview Pane Remember that you can also configure sorting directly in the Preview Pane by using the column header menus. 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.

Aggregation

The Aggregation section is where you choose the aggregation operation for numeric fields. All numeric fields 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. 

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:

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:

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

Sorting/Grouping on a hidden field

If you want to group on or sort a field but not display it, you can use the column header menu -  Columns sub-menu - to uncheck that field to hide it. The field remains available as a Sort or Group By field. For example, the screenshots below show how to group by Hours to Complete, and then hide the field from the report.