Sorting Data in Reports

How Sorting Can Help with Analysis

Information can be easier to understand when it appears in an intentional order. Applying a sort order to one or more data items in the designer enables you to arrange rows and columns in tables and axis labels on charts in some order, such as alphabetically or highest to lowest numerically. Interactively changing the order of data can provide you with a different perspective that often facilitates valuable insight. For example, in a report, sales employees who are initially arranged alphabetically can be re-sorted by sales amount.
You can create a custom sort so that data items in a table or graph can sort to the top or to the bottom. For more information, see Using a Custom Sort.
Ranking can help reduce the amount of visible data and is often used in combination with sorting. For more information, see Add a New Rank.

Sort Data in a List Table

To sort values in a list table, right-click on a column heading, and select Sort. Then, select either Ascending or Descending.
Sort Menu Items for List Tables
Sort Menu Items for List Tables
Note: You can also press the spacebar to sort a single column in a table.
Tip
Use the Ctrl key to select and sort multiple columns.

Sort Data in a Crosstab

To sort by category or measure values in a crosstab, right-click on a column or row heading, and select Sort. Then, select either Ascending or Descending.
Sort Menu Items for a Crosstab
Sort Menu Items for a Crosstab
You can also sort categories, rather than measures, by clicking the category name.

Sort Data in a Graph

To sort by values in a graph, right-click on a measure name or category name, and select Sort. Then, select either Ascending or Descending.
Here are some key points about sorting data in a graph:
  • Sorting in a pie chart is based on the measure. If you do not use a measure, then sorting is based on the frequency (and automatically applied).
  • Only the Category role in a schedule chart can be sorted.
Sort Menu Items for a Graph
Sort Menu for a Category in a Graph

Using a Custom Sort

Add a Custom Sort

You can create a custom sort so that data items in a table or graph can sort to the top or to the bottom. Category data items, calculated items that are categories, and custom categories support custom sorts. You can select up to 25 data items for a custom sort. For example, suppose that your company has manufacturing facilities in multiple cities across the country. You can use a custom sort to make sure that certain cities are always sorted before or after other cities in a list table.
Here are some key points for custom sorting:
  • For graphs, a custom sort works only if the data item containing the custom sort is on the X axis, Y axis, or the outermost lattice role.
  • For graphs, a custom sort does not work if the data item is assigned to one of the legend data roles (either Color or Group). The same restriction applies when sorting on the raw values of a data item that has a user-defined format.
  • Because user-defined formats have their own sort order, a custom sort works only on the X axis (the Category role).
  • A custom sort takes precedence over both formatted and unformatted user-defined formats.
To create a custom sort:
  1. On the Data tab, select a category data item. Right-click, and select New Custom Sort. The New Custom Sort window is displayed.
  2. Select the category data items that you want to sort. Click the Right Arrow to move the data items to the Sorted Items list. You can also drag and drop data items in the New Custom Sort window. Use the Ctrl key to select multiple data items.
    The New Custom Sort Window
  3. (Optional) Use the up and down arrows to arrange the data items in the Sorted Items list.
  4. Click OK.

Edit a Custom Sort

To edit a custom sort:
  1. On the Data tab, select a category data item, calculated item that is a category, or a custom category. Right-click, and select Edit Custom Sort. The Edit Custom Sort window is displayed.
  2. Modify the Sorted Items list.
  3. Click OK.

Delete a Custom Sort

You can delete a custom sort on the Data tab. Select the category data item with a custom sort. Right-click, and select Delete Custom Sort. Click Delete in the confirmation message that is displayed.
When you delete a custom sort, the data item is sorted on the data.