Using Sort Limits

You can use sort limits to control how many values to display in a your content. While a filter allows you to limit your content by specifying which values should display, a sort limit allows you to limit it by specifying how many values should display, depending on the amount of information that you want to see.

To add a sort limit, right-click a field in a bucket, point to Sort limit, and select a value. Only fields in certain buckets, generally the default dimension and measure buckets, can have sort limits applied. When you add a sort limit to a measure field, that field is used to sort your content automatically.

When setting a sort limit, the values 5, 10, and 25 are provided for quick selection, but you can select Custom to specify a different number as the sort limit. You can remove the sort limit from a field by right-clicking the field, pointing to Sort limit, and clicking No limit.

When a sort limit is applied to a sort field, only the specified number of values is displayed for that field within each sort group. If a sort limit of 5 is applied to the primary sort field, then only the first five sort values display in the chart or report. If a sort limit of 5 is applied to a secondary sort field, then five values for that field display within each higher level sort value. In the following image, both the Customer Business Sub Region and Customer City fields have a sort limit of 5, so the report shows the first five Customer Business Sub Region values and the first five Customer City values within each subregion.

The values that display are based on the sort order of the field that has the sort limit applied. If an alphanumeric dimension field has a sort limit of 5 and is sorted ascending, then the first five alphabetic values for that field display. If the same field is sorted descending, then the last five alphabetic values display.

As a result, one of the most effective ways to use sort limits is to apply them to a sorting measure field, which allows you to limit the report to the highest or lowest aggregated values in each sort category. To do this, right-click a field in the measure bucket, point to Sort limit, and select a value. In a report, a hidden instance of the selected measure field is automatically added to the top of the Rows bucket, with the sort limit applied. You can click the arrow icon on the hidden field in the Rows bucket to change the sort order, and you can drag the hidden field into a different order in the Rows bucket to change the level at which the sort limit is applied. In a chart, you can right-click the measure field for which you created a sort limit and change the sort order from the shortcut menu.

In the following image, a sort limit of 5 has been applied to the Revenue field from the Summaries bucket of a report. The resulting Revenue sort field has been changed to descending order and moved to apply the sort limit after the Customer Business Sub Region field. As a result, the report shows the cities with the five highest Revenue values in each business subregion.

If you are sorting by a measure field with a sort limit, and multiple rows within a sort group have the same value for that measure field, then they are all displayed. This may result in a number of rows higher than the sort limit. For example, the following image shows a report in which the Quantity Sold field has a sort limit of 5. Since multiple Customer City values in the Africa subregion have the same Quantity Sold value, seven values display, representing the cities with the five highest distinct Quantity Sold values in that subregion.