Sorting Data in a Report

You can modify the order and arrangement of values in a report by using different sort orders. Reports are sorted into rows based on the values of fields in the Rows bucket, and into columns based on the values of fields in the Column Groups bucket.

Sorting is hierarchical, so values are first sorted by the first field in the Row or Column bucket, then by the second field, and so on. For example, in the following image, the report is sorted by the Product Category field, then for product subcategories under each product category, then by sale year values for each product subcategory. Product Category, Product Subcategory, and Sale Year have all been placed into the Rows bucket. Gross Profit was placed in the Summaries bucket, so it is not used to sort the report. Instead, gross profit values are sorted by the other three fields.

By default, field values are sorted into ascending order based on the numeric and alphabetical order defined in the code page that you are using. To reverse the sort order, right-click a field and click Sort descending. To return to the original sort order, right-click a field and click Sort ascending. You can also click the sort arrows on the fields in a bucket, as shown in the following image.

You can rearrange the fields within a bucket by dragging them, which allows you to prioritize certain information in the report. The previous example showed a report sorted by Product Category, then Product Subcategory, then Sale Year. If we move Sale Year to the top of the Rows bucket so that it is the primary sort field, the report becomes a breakdown of yearly sales, as shown in the following image.

You can sort by measure fields as well when they use the Summaries, Details, or Details with counter display options. To sort by aggregated measure values, right-click a field in the measure bucket and click Sort ascending or Sort descending. A hidden instance of the measure field is added to the top of the Rows bucket, as shown in the following image.

The result is a report that is sorted into rows based on the selected measure value, as shown in the following image.

In this example, sorting by the Gross Profit field from the Summaries bucket has allowed us to see at a glance that larger profits have been made in more recent years. You could change the sort order to see lowest profits first by clicking the sort arrow for the hidden Gross Profit field in the Rows bucket, just as you could for a visible field.

You can move this hidden field in relation to the other sort fields to change the sorting priority. In the following image, the hidden Gross Profit field has been moved to after Sale Year, allowing us to see the most profitable products in each year.

You can sort by multiple measures to have more granular control over how the report is sorted. If there are some matching values of the first sorting measure, the second sorting measure will sort those matching values.

When using the Details or Details with counter display option instead of the Summaries option, you can sort fields in the Details bucket in the same way. This can be helpful if you want to view a sorted list of all records for a field. Right-click a field in the Details or Details with counter bucket and click Sort ascending or Sort descending to sort the report using the values from that field.

You can remove the sorting effect of a field in the Summaries, Details, or Details with counter bucket by pointing to the invisible measure field in the Rows bucket and clicking the X, or by right-clicking the original field in the measure bucket and clicking No sort. The hidden field is removed from the Rows bucket, but the original field remains in the measure bucket. When you remove the original field from the measure bucket, the invisible field in the Rows bucket is automatically removed as well.

You can also sort by hidden dimension fields, if you do not want them to display in the chart. To hide a dimension field in your report, right-click the field in the Rows or Column Groups bucket and click Hide.

The following image shows a report sorted by Sale Day Name and Sale Date. We want to see sales information based on the day of the week. Notice, however, that Sale Day Name is sorted alphabetically, by default.

We can add the Sale Day of Week field, which assigns a number to each day, to the report as the primary sort field, and then hide it, so the days are listed in weekday order, as shown in the following image.