Sorting cross tab data
By default, a cross tab displays data sorted by dimension values. In other words, values displayed in the row and column headings are sorted in ascending order. If you wish, you can sort the dimension values in descending order.
A more common reporting requirement is to sort data by totals. Compare the cross tabs in Figure 24‑75 and Figure 24‑76. The cross tab in Figure 24‑75 displays row and column heading values in the default ascending order. In the rows, the values of the territories are in alphabetical order. In the columns, the values are also alphabetical, from Classic Cars to Vintage Cars.
Figure 24‑75 Data sorted by default
The cross tab in Figure 24‑76 displays data sorted by product grand totals, in ascending order. The product line names in the column heading are no longer in alphabetical order. The product grand totals in both cross tabs are highlighted for easier comparison.
Figure 24‑76 Data sorted by product line grand totals in ascending order
How to sort cross tab data
1 Select the cross tab.
2 In Property Editor, choose the Sorting tab.
3 In the Sorting page, choose Add.
By default, New Sort Key displays sort information for the first dimension in the column area, as shown in Figure 24‑77. The default Key value, data["PRODUCTLINE"], indicates that the values in the PRODUCTLINE dimension are sorted by product line name in ascending order.
Figure 24‑77 New Sort Key showing default sort values for the PRODUCTLINE dimension
4 In New Sort Key, supply the necessary sort information, as follows:
1 In Group Level, select the dimension on which to sort.
2 In Key, select the data on which to sort.
3 In Direction, select either Ascending or Descending.
4 In Locale, select a language. The language determines the sorting, or collation, attributes that conform to local conventions. Select Auto to sort data according to the locale set on the user’s machine.
5 In Strength, select a collation strength, also known as a collation level. The collation strength determines whether accent marks, case, or punctuation are considered when sorting strings.
For more information about locale-specific sorting and collation strength, see Sorting and grouping data.
6 Choose OK to save the sort definition.
Figure 24‑78 shows an example of sorting on product line grand totals. This sort definition was created to generate the cross tab shown in Figure 24‑76.
Figure 24‑78 New Sort Key specifying sorting on product line grand totals