Sorting a table visualization
This article shows you various ways a table visualization allows you to sort a metric set.
2. Sort on multiple hierarchies
When you use a table visualization to display multiple row hierarchies, the corresponding columns can be formatted as either row header columns or regular 'flat' columns.
Row header columns are always displayed on the left and indicate how the data is grouped, while flat columns can be displayed in any order regardless of the data grouping and have more customization options. The default styling for row header cells in Dundas BI is a grey background whereas regular column cells use alternating but lighter colors for the background.
In the case of row header columns, the sorting priority is determined by the left-to-right order of the columns. This is the same as the order of the row hierarchies in the underlying metric set, and the same as the grouping order of the hierarchies.
As an example, create a new metric set with two row hierarchies.
Create a new dashboard and drag the metric set from the Explore window to the canvas.
Switch to View mode and reorder the table columns by dragging the header of the second column onto the header of the first column.
The columns, as well as the grouping of the hierarchy values, are now swapped.
Hover over the first column header. Click the A-Z icon on the right to open the Sort & Filter popup.
In the popup, click Descending in order to sort the first column in descending order. Do the same for the second column.
You will see the values in the first column are now sorted in descending order. In the second column, the values are also sorted in descending order within each grouping.
Another way to re-order the columns is via the Data Analysis Panel. Drag the Name column over the Color column to switch them back.
You will see the Name column is now first again, with the values still sorted in descending order. In the second column, the Color values are also sorted in descending order within each grouping.
2.2. Regular columns
Regular columns can also be reordered by dragging column headers in View mode. This will not affect the order of the row hierarchies in the underlying metric set's Rows. However, using the Sort & Filter popup to sort a regular column will give that column the highest sorting priority and cause the corresponding row hierarchy to be reordered first in the underlying metric set.
As an example, start with the dashboard from the previous example with two hierarchies.
Select the table visualization on the canvas, go to the toolbar, and then click Display As Flat Table.
The row hierarchies are now displayed as regular columns instead of row headers.
Switch to View mode and hover over the header of the second column. Click the A-Z icon to open the Sort & Filter popup.
Even though Descending is already selected, click on it. The second column now has the highest sorting priority and its values are all sorted in descending order regardless of the column position. This is equivalent to reordering the row header columns and changes the order of row hierarchies on the metric set.
3. Sort on multiple measures
Sorting multiple measures is similar to sorting multiple row hierarchies that are displayed as regular columns.
Measure columns can be reordered by dragging column headers as usual. This will not affect the ordering of the measures in the underlying metric set, and vice versa. Using the Sort & Filter popup to sort a measure column will give that column the highest sorting priority and cause the corresponding measure to be reordered first in the underlying metric set, without changing the order of any columns in the displayed table.
As an example, create the following table using the Name, SafetyStockLevel, and ListPrice columns from [Production].[Product].
Switch to View mode and use the column header's sort & filter popup to sort ListPrice in Descending order. Then sort SafetyStockLevel in Ascending order.
Observe that the SafetyStockLevel measure values are all in ascending order and the corresponding column has the highest sorting priority because it was sorted most recently. The ListPrice measure column has the next highest priority and its values are still sorted in descending order but only within rows with the same SafetyStockLevel value.
4. Sort on a measure and a hierarchy
You can mix and match sorting on hierarchies and measures. When the hierarchy is presented as a regular column (such as in a flat table), the result will be the same as sorting multiple measures. However, the grouping created by sorting multiple hierarchies will be maintained even when sorting by a measure. In such a case, sorting the measure will actually sort the groups by their aggregator (e.g. Totals) and separately sort the values inside each group.
As an example, create the following table using the Color, Name, and Weight columns from [Production].[Product].
By default, the Color and Name columns are sorted in ascending order, while the Weight column does not have sorting applied.
In View mode, change the sort on the Weight column to Ascending. You will see that the groups (the Color column) have been reordered based on their totals, and the rows in each group (the Name column) have been reordered based on their individual values.
Open the Sort dialog for the Weight column again. The dialog indicates which hierarchy column is responsible for how the data is grouped. You can change the hierarchy column responsible for how the data is grouped by displaying the table with row headers and following Section 2.1 or by selecting another hierarchy from the drop-down (in this example, the Name column).
You can apply sorting from the context menu when you right-click on a table cell. This option gives you the flexibility of applying sorting to just the rows of a particular hierarchy or level instead of all rows. When the column is sorted, an icon will indicate the direction of the sorting when you move the mouse over the column header.
As an example, set up a table visualization with a product hierarchy, a date hierarchy, and one measure.
Switch to View mode and then right-click over one of the measure subtotal cells for a product category. From the context menu, click Sort.
A sub-menu appears and asks you to choose the specific measure or hierarchy to sort on. Click the measure (OrderQty).
The product categories are now sorted in descending order according to their corresponding subtotal measure values. The date rows within each product category are unchanged.
Next, right-click on a measure cell corresponding to one of the date rows.
Click Sort, select OrderQty, and then click Ascending.
Date rows within the product category are now sorted in ascending order according to their measure values. The product categories are still in order by descending measure value.
5.1. Multi-leveled hierarchy
If there are multiple levels in a hierarchy, you can define sorting for each displayed level using the context menu. These settings will be saved even if you change the displayed hierarchy level: the sorting you previously set for a level will take effect when measure values for that level are displayed again.
Different levels in the hierarchy affect the sorting in a similar way to having multiple hierarchies, because data is grouped by the values in each displayed level. As a viewer, you can change the displayed levels using the context menu.
In the previous example, while in View mode, right-click the visualization and select Change Level.
Select the ProductID hierarchy and select the Product level.
Right-click the visualization again and select Change Level.
Select the ProductID hierarchy and select Change Top Level.
Select the Product Category level.
While products are still in descending order by OrderQty, and years are in ascending order by OrderQty within each product, the sorting has been affected because the products are now first grouped within their subcategories, and subcategories are grouped within their category. This is the result of choosing to display these upper hierarchy levels.
6. Sort with one click
Hierarchy headers also support one-click sorting, which allows users to simply click the header to sort the rows in ascending, descending, or unspecified order. A triangle icon above the header text tells you which direction it is currently sorted. If you do not see the icon, it means the sort direction is unspecified.
To use this option, open the Properties panel for the table and check the Enable One Click Sort property.
- Some sorting features are not available when using a Dynamic Hierarchy and editing the metric set full-screen, but you can use them when viewing a dashboard that uses this metric set.
8. See also
- Using a table visualization
- Metric set analysis tools
- Define custom measure sorting
- Define custom hierarchy sorting
- Create a metric set and add a filter