Understanding Sorting and Grouping

Understanding sorting and grouping is in many ways one of the first steps toward becoming a proficient report builder. Both allow you to better organize your data!

In this article, we're going to cover the concepts of Sorts and Groups.

Sorting is the process of ordering your data rows by a certain sequence. For each available data category, you can choose which data field should be used to sort the rows. Fields can be sorted in ascending or descending direction. The way in which rows are sorted depends on the type of value in the field:

Numeric

Asc > Lower values     Higher values < Desc

Date

Asc > Past     Future < Desc

Text

Asc > A     Z < Desc

Sorting a category by Department

A report can have multiple sorts. This can be useful when you want your highest precedence sort to affect a data field where the values for multiple rows may be the same.

For example, imagine a large company with many employees. There could be multiple people with the last name Buchanan. With only a sort on LastName, you do not know how all the people with the last name Buchanan will be ordered amongst themselves. If this matters, then you can add a second sort on, say, the FirstName field, so that people with the same last names will be ordered by their first names.

Adding sorts

In the Sorts window, add data fields to sort. The precedence of the sorts starts with the highest row and moves down the list. Drag the rows up or down to change the precedence.

Dragging a field to the Sort By pane

Relationship between sorts and groups

Sorts are a prerequisite for making groups. Here's why.

Sorting puts data in order so that data rows which share common values for the sort field are next to each other. This is essentially what grouping does as well. Grouping simply takes those common values, pulls them out of the rows, and makes sections for each group of rows which share that value. Sorts tell the report how you want to your data to be grouped.

Grouping a category by the Department sort

Sort formulas (advanced users)

You can sort by a formula instead of a data field. This allows you to have finer and more specific control over your groups.

If you do not have a single data field as a unique key, you can use a sort formula to sort on a concatenation of two fields instead. For example, EmployeeId plus TerritoryId fields:

={EmployeeTerritories.EmployeeId} & {EmployeeTerritories.TerritoryId}

Or if your sort field would generate too many groups, you can sort on a piece of the field instead. For example, you could group on only the month and year component of a date field.

=Date(Year({Employees.HireDate}),Month({Employees.HireDate}),1)

To add a sort formula, click  Add Formula, then use the formula editor to make a composite field to sort on.

Sorting by aggregates (advanced users)

You may want to sort groups by the summary, or aggregate, calculation of each group. You can do this using Top/Bottom filters.

Top/Bottom filters look for the highest or lowest values in a set, and then put those values in order. You can supply any arbitrary cell in the report in the Top/Bottom filter. So if you set the number of displayed values to "infinity", then no values are excluded, but the ordering still takes effect.

To sort by a summary calculation:

1. Ensure that your report has the appropriate aggregate formula in a group footer cell. The cell should return a numeric value in the report output, which you want to sort the groups by.

2. From the 

   

    Report Settings menu, click  Filters. Then click the Top/Bottom tab.

3. Select the Limit the report to the top or bottom values of a defined dataset check box.

4. From the Top/Bottom list, select Bottom to sort in ascending order, or Top to sort in descending order.

5. In the # field, type 2147483647.

   1. Why this number? We cannot enter "infinity", so instead we want to enter an arbitrarily large number like 99999. This is the largest number that can fit without causing a report error.

6. From the Value list, select the group footer cell with the aggregate formula.

7. If there is a For Each group, click  to remove it.

Keep in mind that this is not a Sort from the sort menu. You cannot use this to make nested groups. This only affects the order in which a group of data is shown in the output. Because this is technically a Filter, this has precedence over the report sorts.

Grouping

Groups collect a set of data rows, based on a common value, into a column called a group column. This allows you to identify rows with common factors. You can perform summary calculations on grouped data, such as counting all the rows in each group, or adding up the values for a data field in each group. You can also make charts to visualize the data in each group, and compare groups to each other.

Grouping data in an ExpressView

Making a Group

To turn a data column into a group column use  radial>up. Doing this will organize the other columns into groups. Each row in the data column is a unique group of the rows in the other columns. The group column has a new color to distinguish it from the data rows. Remember to turn on Live Data to see your actual data and verify that this is the desired grouping.

To ungroup a column, use  radial>left. This turns the group column back into a data column.

Groups can be created inside other groups. These are called nested groups. To make a nested group, add another group to an ExpressView which already has one. Additional levels of nesting can be made as needed.

Changing group level

If you have two or more levels of grouping, you may decide that they are nested improperly. For example, if you had Products grouped by Orders, then you add another grouping on Employees, you may end up with Products grouped by Employees grouped by Orders.

However, it makes more sense to have Orders grouped by Employees instead, since Employees have multiple Orders, but Orders does not have multiple Employees.

To move the Employees group up one level, use  radial>up on the group column.

Changing the data grouping

Summarizing Group Data

Each group has a footer that contains summary calculations, or aggregates, for each column. There is also a report footer, which contains summaries for the entire ExpressView. You can choose one of several calculations for each column.

To change the calculation for a column, click a footer and select one of the following options:

Sum

Totals the data values in the group. Only available for numeric fields.

Min

Shows the smallest data value, or first value alphabetically, or earliest date in the group.

Max

Shows the largest data value, or last value alphabetically, or latest date in the group.

Count

Counts the number of values in the group.

Distinct Count

Counts the number of distinct values in the group.

Avg

Takes the average, or arithmetic mean, of the values in the group. Only available for numeric fields.

None

Show no summary calculation.

Choosing an aggregate

Hiding data rows

If you only want to see the summary calculations, you can hide the data rows, for each group, or for the entire ExpressView. This does not remove the data or alter the summaries; it only hides the rows from view.

Click the header for each group to toggle whether its rows are shown or hidden.

To hide or show all the rows in the ExpressView:

	Click the Group Expand/Collapse Options icon.
	Click the Hide All Group Content to hide all the rows.
	Click the Show All Group Content to show all the rows.

If you have nested groups, this hides all but the top level groups.

To remove all data rows from the report and only show summary data, deselect Include Detail Rows. This may improve the performance for reports that do not depend on the detail values.

Key Concepts to Remember

1. You must sort on a column/field before you can group by it.

2. Sorts and groups nest inside one another. Adding a new sort or group does not replace the previous ones.

3. A field doesn't have to exist as a column on the report in order for you to sort on it.

4. You can have a group header on one field and label it with contents from another.

5. Groups are useful for improving report readability, removing redundant data, and broadening your calculation opportunities.

Happy Reporting!