In many scenarios, hierarchies should be shown in a predefined sort order so the order is the same in every report and not depending on values.
If you want to sort a hierarchy in a specific order, you can do this for every level of the hierarchy. In the following example, we will show you how to create a sort order for a hierarchy showing continents and countries.
First, create a hierarchy by using drag-and-drop to add the Country to the Continent. Learn how to create and change hierarchies.
Now let’s add a Zebra BI Table visual and show Costs for the newly created Continent hierarchy. To do this, add the Continent Hierarchy from the Regions table to the Category placeholder and the Cost field from the Sales table to the Values placeholder.
Once again the default sorting is by values. The Continent with the highest value is on top and even the Countries in the hierarchy level below are sorted by value.
According to our Regions table, the sort order should be different. It should be like this:
Europe
Switzerland
Germany
Slovenia
Asia
Japan
China
Taiwan
The table above has 2 columns for sorting. The Continent Sort order indicates the Sort order of the Continent Column. So Europe is 1 and Asia is 2 which means that we want to see Europe in the first position and Asia just below. The Country Sort column is then used to sort each Country.
In general, the steps you have to take are the same as if you only had a single field in the Category placeholder. The basic sorting is explained in the article about correctly sorting by month. First, let’s sort the visual by the Category in the rows instead of the value. To do this, click on the header of the column until the arrow disappears. Then you have to select the visual > More Options > Sort By > Continent. Because the default sort order is descending, you need to change it to ascending by selecting the visual > More Options > Sort ascending.
The Continents and the Countries are now sorted alphabetically.
Now you need to define the sort order so it is sorted by the respective sort column of each dimension. Let’s do this for Continents first. Select Continent from the fields list and then Sort by column – Continent Sort.
⚠️ Don’t select the Continent in the hierarchy but the one outside the hierarchy.
Now the continents are sorted the way we want. Let’s do the same for the countries.
Select Country from the fields list and then Sort by column – Country Sort.
This is exactly the sort order we wanted.
In this sample, we already have sorting columns as part of the data model. But what do you do when you don’t have a sorting column? Learn how to proceed in such case.