...
In this article, we're going to cover the concepts of Sorts and Groups.
- 1. What is Sorting?
Sorting is the act process of arranging data in either ascending or descending order. Pretty simple, right? Looking at the spreadsheet below, we can see that it is completely unsorted. No one column is arranged in any ascending or descending pattern.
Designer View: A basic table containing the data for a restaurant menu.
Output: An unsorted table of restaurant menu data.
If we wanted to, though, we could sort this list by either Course Number, Course, Dish, Dish Type, Diet, or Price.
Let's say we wanted to turn this spreadsheet of unsorted data into a restaurant menu. What sort of organization does a restaurant menu typically have? In most cases, the dishes are grouped by course. Appetizers usually come first, followed by main courses, sides, and desserts. To group by course, however, we must first sort by course so we know what order the courses go in and which dishes belong to each one.
Let's sort by putting the courses in ascending order. To do this, we'll have to access the Sorts menu, located in the Report Options dropdown menu.
Accessing the Sorts menu.
Then, we'll have to select the column we wish to sort by. In this case, we would like to sort by Course and have the courses display in ascending order. Let's click 'OK' to save this setting and run the report.
Adding the course column to the Sort By pane.
Upon running the report, you'll notice that the courses are sorted in ascending alphabetical order, but they're not in the correct order for a menu. The letter "D" may come before "E", but that doesn't mean desserts should come before entrees on the menu.
Let's fix this by sorting on Course Number instead. This way, we can be sure that the courses will appear in the correct order. To do this, we will navigate back to the Sorts menu, click the red X to remove our sort on Course and then add Course Number to the Sorts By pane instead. Once again, let's be sure that the column is set to sort in ascending order. After clicking 'OK' and running the report, we can confirm that that the courses now appear in the correct order. Now we can begin to group!
Sorting by Course Number.
Output: Menu data sorted by Course Number in ascending order.
2. What is Grouping?
Grouping is the process of breaking up rows of data into sections that share a common trait. This makes the document easier for the eye to scan, allows for the removal of repeated values in the table, and broadens the types of calculations you can make.
Let's focus on the first two goals for the time being.
Grouping to Improve Readability
Since we've sorted on Course Number, we're now able to group on Course Number, so let's start separating the courses into groups by inserting a line or two of space between each one using a group header.
To add a group header to the report, we click on the section name of any existing report section in the Report Designer grid, hover over "Add Section," and then select "Group Header" from the list of available sections, clicking on that option.
We are then presented with the Group Header menu. It is at this point that we must specify what column we are grouping on by clicking on the dropdown arrow and selecting, in our case, Course Number. Congratulations, you've just created your first group! Let's click OK and take a look at the report designer with that header applied.
Group Header menu.
Looking at the Report Designer, we can see that under "Section", a new item has been added. This is our group header on Course Number. We can also see that a blank row has been inserted into the report for that section.
Designer View: A new Group Header has been added.
Like the headers and footers of a Word document, this header is just a blank space in a specific spot for you to fill with information. A page header, for example, is a blank space at the top of each page of a document, and you might choose to put an author name there or maybe a page number. In our case, this group header is just a blank space at the beginning of each group of course numbers that we can fill with whatever information we want.
Let's see what the report looks like without any information in the group header.
Simply inserting extra space between each group has improved readability! It's easy to see the three sections of dishes. Traditionally, though, menu sections have headers displaying the course name, so let's go back to the Designer and fill our group header spaces with that information.
Designer View: Clicking and dragging to add a data field to the Group Header.
It's worth noting here that even though we've grouped on Course Number, we can use the Course name to label each header. Let's add a little formatting to the header by increasing font size and add a new row above row 4 to create some padding between groups. Now, when we run the report, it has each group of course numbers labeled with the corresponding course name.
This is starting to look like a menu! Now that we have group headers, though, it's clear there's some redundant information in the table, like everything in the Course column. The Desserts header tells us that the Chocolate Souffle and Vanilla ice cream are desserts, so we don't need to repeat that information in line with each dish name.
We could also stand to remove the Course Number column now, since menus don't traditionally number their courses. It's okay to remove a sort column from your report design! The sort still remains saved in the Sorts menu. So let's remove the unnecessary info!
First, we'll drag the Course Name field currently in cell A4 to cell C4 so that we can delete columns A and B without removing the Course Name.
Designer View: Dragging a field from one cell to another.
Then we can hold down the shift key and click both columns A and B to select both columns, right-clicking thereafter to display the Delete Columns option.
Designer View: Deleting columns.
After making these two changes, our report design looks like this, and we're ready to run the report once more.
Designer View: A simplified report.
Output: A simplified report.
This is really starting to look like a restaurant menu, but there's still some repeated information on the table under Dish Type. Anytime you see repeated values in a column (in this case, multiple Breads, multiple Salads, and multiple Soups under Dish Type), you have the opportunity to sort and group on that column to simplify your table! Let's explore what happens when we introduce additional sorts and groups to the data.
3. Multiple Sorts and Groups
Sorts and groups are unique in that they nest inside one another by default. So, when you add a second sort to a table, it doesn't come after the first one--it goes inside the first one. By this we mean that a new sort or group doesn't undo any of the previous sorts or groups. They work in tandem with each other!
Let's see this in action by sorting on Dish Type in our example. To do this, we will return to the Sorts menu and add Dish Type to the Sort By pane as we did with Course Number, making sure again that the Sort Order is set to Ascending.
Sorting by Dish Type.
Let's run the report to see what effect this has on the data.
Output: Two sorts.
As you can see, sorting by Dish Type didn't undo our sort on Course Number; it just added itself to the existing sort.
Similarly, if we now group by Dish Type and add group headers for those groups, they will nest inside the group on Course Number, not replace it. Let's begin by adding a new group header on Dish Type.
Adding a second group header.
Then, we can do what we did with the Course Name and fill the new header with the information we'd like it to display. Let's have it display dish type. Since we know that we only need that information on the report once, let's drag the field over from its spot in cell B7 to the new vacancy in the new group header.
Report Designer: Dragging over a field.
Now we can remove column B from the report altogether, further simplifying the report. Remember, to do this, simply right-click on the column letter you wish to delete, and a menu will appear displaying the Delete Column option. After this step, let's increase the font size of cell A6, italicize it, add bolding to A5, and insert a new row above row 6.
Report Designer: Formatting with two group headers.
We now have a group inside another group!
Output: Two groups nested.
Now that we know how adding additional sorts works, let's complete this menu by sorting on price so that the least expensive dishes come first and the most expensive come last. This is also traditional restaurant menu formatting. Again, note that adding this new sort does not undo previous sorts. With a little currency formatting on Price and a deletion of our original column headers, we have as a final product a triple-sorted, double-grouped table designed to read like a menu.
Sorting by price.
Format Cells: Adding a currency symbol and two decimal places.
Report Designer: Deleting a row.
Output: Two groups with an additional sort on Priceordering 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
| Info |
|---|
Set the sort precedence so that nested groups are in order of their grouping level. The outermost group should have the highest precedence, with the next levels following in order. If the precedence is set incorrectly, it could result in inconsistent data groups. |
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:
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.
From the
Report Settings menu, click Filters. Then click the Top/Bottom tab.
Select the Limit the report to the top or bottom values of a defined dataset check box.
From the Top/Bottom list, select Bottom to sort in ascending order, or Top to sort in descending order.
In the # field, type
2147483647.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.
From the Value list, select the group footer cell with the aggregate formula.
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.
...
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.
...