In your workspace, you can sort the information shown to you by sorting the characteristics and sorting the key figures. You not only have the option to simply sort the data in an ascending or descending way, but you also have the ability to re-order the data in a custom way.
Usually we need to sort data when we want to present it. You have 3 sorts direction as follows:
- Ascending: from smaller to greater
- Descending: reverse order
- Customized: based on your customized order
And we can sort on the following level:
- Column: sort data in a table based on the selected column [tabular & cross table]
- Row: Sort data in table based on the selected row [Cross table]
- Table: if you have more than one sorted data set we can order or prioritize them from this menu.
Our workspace from the previous section shows the characteristics Country and Region in the Rows and the key figures Net Value, Product Cost, and Transport Cost in the columns.
- Select the key figure Net Value in the cross tab.
- Use a right-click to open the context menu.
- Select the menu Sort (see Figure 4.39).
Figure 4.39 Sorting Data
4. Here you can decide, if you want to sort the data in an ascending or descending order.
5. Select the option Ascending.
Figure 4.40 Sorted Data Set
You will notice that the data is now sorted in an ascending way (see Figure 4.40), which means that the subtotals – in our example for characteristic Country – are sorted in an ascending way as well, and that all the values for characteristic Region for each Country are also sorted in an ascending way.
Figure 4.41 Net Value Column Header
In addition, you now also have a small sort icon (see Figure 4.41) that appears in the column header for the key figure Net Value to indicate that the values are sorted and you can quickly switch between an ascending or descending order, by a simple click on the icon. The menu to sort the data is also available on the tab Analyze (see Figure 4.42).
Figure 4.42 Tab Analyze
6. Select the key figure Net Value in the cross tab.
7. Use a right-click to open the context menu.
8. Select the menu Sort.
Figure 4.43 Sorting Key Figures
In case you would like to remove the sort, you can do so in the menu Sort on the context menu, or using the tab Analyze.
9. Select characteristic Country in the Rows area of the Layout panel.
10. Use a right-click to open the context menu.
11. Select the menu Sort (see Figure 4.44).
Figure 4.44 Sorting Characteristic
Here, you can select how the members of characteristic Country are being sorted. You can now configure an ascending or descending sort, and you can also configure, if the sort should be based on the key or text value.
12. Select the option Key.
13. Re-open the menu and select the option Z to A to create a descending sort.
Figure 4.45 Sorted Data
Even though we are not showing the key values for characteristic Country, we are able to sort based on the key values (see Figure 4.45). Similar to the sorting for key figure Net Value, we are now seeing the sort icon next to the column header for characteristic Country where we can click on the icon to quickly switch between a descending or ascending sort order.
14. Now select the value United States from characteristic Country in the cross tab.
15. Keep the mouse button clicked and move the column down so that it appears between the countries France and Germany (see Figure 4.46).
Figure 4.46 Creating a custom sort
You can create your own custom sort order by simply dragging the members in the order you would like to create. The option to create such a custom order is also available in the context menu.
16. Select the value Germany from characteristic Country in the cross tab.
17. Use a right-click to open the context menu and use the menu Reorder Members > Move (see Figure 4.47).
Figure 4.47 Moving Characteristic Values
18. Select the value United Kingdom from characteristic Country in the cross tab.
19. Use a right-click to open the context menu and use the menu Reorder Members > Insert Before.
Figure 4.48 Custom sort
The option to create such a custom order is also available for the key figures.
In this section, we had a look at the different options to sort the characteristics and key figures as part of our crosstab. In the next section, we shall learn ‘how to create subtotals and grand totals’.