Advanced Reports: Sorts

Sorting is the process of ordering data in 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 order. The way in which rows are sorted depends on the type of value in the field:

Numeric
Ascending Lower values     Higher values  Descending

Date
Ascending Past     Future Descending

Text
Ascending A     Z Descending

 

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.

Relationship Between Sorts and Groups

Sorts are a prerequisite for making Groups. 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.

 

Tip

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.

Add, Edit, Delete Sorts

1. Click the Sorts icon in the toolbar to open the Report Sorts dialog.
   

   

   The Report Sorts dialog for a report that will first sort by the manufacturer’s name, then by the Product Category name then finally by the Product’s name

    

2. Add data fields to sort by first selecting a Data Object from the dropdown, and then either:
   • clicking the arrow icon to the right of the data field’s name
   • clicking the Add button at the bottom of the data field’s tree
   • double-clicking on the field’s name
   • drag-and-dropping the data object from the tree to the Sort By panel
   • clicking the Add Formula button to add a formula sort to the report. For more information, refer to the Formula Sorts section of this article.
3. Click the Formula Editor icon to change this sort to a Formula Sort.
4. Set the Sort Order to either Ascending or Descending with the dropdown.
5. If necessary, change the precedence of the sorts by either:
   • clicking the Move Item Up or Move Item Down icons
   • click and drag the Grip icon to move the sort to the desired location
6. Repeat steps 2–5 for any additional sorts to be added
7. To delete a sort from the panel, click the Delete icon at the end of the row.

Formula Sorts

Note

This feature is recommended for advanced users.

It is also possible to sort by formula instead of a plain data field, to gain finer control over group sections.

If there is no single data field as a unique key to indicate the end of one group and the start of another (called a break), a sort formula with concatenation of two fields may be used instead. For example, EmployeeId plus TerritoryId fields:

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

Or if a plain data field would generate too many groups, sorting on a portion of a field may be helpful. For example, 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.