In this article
Discover datagrid table sorting methods - label, value, exclusion, header, global sorting. Examples and path writing rules included.
Datagrid has the ability to sort data by values and labels. You can set sorting rules for the whole table or only for a separate header level. You can override the global sort by the specific one. There is also column click sorting that is not connected with CDL. This is a less flexible setting that is usually used for temporary sorting.
Sorting by labels
It is the simplest and shortest sorting to define. You can use it at any header level. All you need is just to specify the sort order.
sortOrder: ascending | descending | none
Note
Note "none" is used only in case when you want to cancel global sorting for separate header ( we will talk about this in more detail later).
Example of sort by label in ascending order in a datagrid table
Sorting by values
To sort data by values, you need to point column or row you want to sort them by. It is controlled by "sortBy" property.
sortBy: "/col1.2/col2.3"
For this type of sorting sortOrder is optional (ascending by default), but you can change it.
sortBy: "/col1.2/col2.3"
sortOrder: descending
This type of sorting is applied only at the lowest header level.
What is "sortBy"? It is the path to the lowest header or header item on the opposite side of the table. And there are a few rules to write it.
This should begin with "/". If you have nested headers, you need to specify all levels with a header name and category code (and a field in case of a grid). They should be separated by slash.
Different headers have different ways of describing. You can see them in the table below.
Header Type | Required fields | Example |
Empty | Name | "/col1" |
Cut by scalar value | Name, category code | "/row1.3" |
Cut by multi | Name, field | "/row2.2" |
Cut by compound value (e.g. grid) | Name, field, category code | "/col2.2.4" |
Date header and overlapping date header | Name and: | |
breakdownBy: | ||
year | year | "/row3.2019" |
quarter | quarter (1-4) | "/col3.2" |
month | month (1-12) | "/col3.12" |
week | week (1-52) | "/row3.3" |
day | day (1-31) | "/row3.2" |
weekday | weekday (1-7) | "/col3.1" |
hour | hour (0-23) | "/col3.21" |
calendarQuarter | year+quarter (01-04) | "/row3.201701" |
calendarMonth | year+month (01-12) | "/col3.201608" |
calendarWeek | year+week (01-52) | "/row3.201715" |
calendarDate | year+month(01-12)+day(01-31) | "/row3.20160701" |
List and DrillDown | Not supported now | --- |
There are two special types of data: "total" and "not answered". If you want sort by total, you should specify path according to header type rule (table above) and write "__total__".
sortBy: "/col1.__total__"
The same for "not answered", but with "__na__".
sortBy: "/col1.__na__"
Example of sort by value ('Under 12 month' column) in descending order in a datagrid table
Exclusion
You may not want to sort some rows or columns. For these purposes "exclude" property exists. That property contains comma separated list of codes that should not be sorted (they will be moved to the end of the list).
exclude: "1" | "2017, 2018"
Example of sort by value ('Under 12 month' column) in descending order with the exception of 'Care about query' in a datagrid table
Where to Declare
We got to know different types of sorting. Now we look at this in terms of where sorting can be declared. We will deal with this in order of increasing priority.
Global sorting
Global sorting allows to sort all table data according to general rule. You can sort rows and columns independently of each other. To do this, you need to write "sort" - block at the widget level with the necessary type.
widget dataGridBeta{
view comparativeStatistic #averageColours {
backgroundColorFormatter: backgroundColorFormatter3
valueColorFormatter: valueColorFormatter2
}
size: medium
sort rows {
sortBy: "/col1.3" // = "1-3 years" column
sortOrder: ascending
}
sort columns {
sortBy: "/row1.__total__"
sortOrder: descending
}
column cut #col1 {
value: survey:CustomerCategory
cell {
value: average(score(survey:OSAT.1))
view: averageColours
target: 8
}
}
row cutByMulti #row1 {
value: survey:AgentIssue
}
}
Example of global sorting in a datagrid table
Header sorting
If you want to apply sorting to particular headers, you can use sorting at the level of those headers. In this case, all sorting settings should be placed in the header as properties.
widget dataGridBeta{
view comparativeStatistic #averageColours {
backgroundColorFormatter: backgroundColorFormatter4
valueColorFormatter: valueColorFormatter2
}
size: medium
column cut #col1 {
value: survey:CustomerCategory
cell {
value: average(score(survey:OSAT.1))
view: averageColours
target: 8
}
}
row cut {
value: survey:NPSGroup
sortOrder: ascending
}
row cutByMulti {
value: survey:AgentIssue
sortBy: "/col1.4" // = "Over 3 years" column
sortOrder: descending
}
}
Example of header sorting in a datagrid table
The presence of these two sortings allows you to make the display of data in the table as flexible as possible. You can define global sorting and redeclare or cancel it at the header level.
widget dataGridBeta{
view comparativeStatistic #averageColours {
backgroundColorFormatter: backgroundColorFormatter4
valueColorFormatter: valueColorFormatter2
}
size: medium
sort rows {
sortOrder: ascending
}
column cut #col1 {
value: survey:CustomerCategory
cell {
value: average(score(survey:OSAT.1))
view: averageColours
target: 8
}
}
row cut {
value: survey:NPSGroup
sortBy: "/col1.5" // = "Lapsed" column
sortOrder: descending // Redeclare global sorting
}
row cutByDate {
value: survey:interview_start
breakdownBy: year
sortOrder: none // Cancel global sorting
}
row drillDown {
drillDown:surveyhierarchy // Global sorting will be applied
total: first
}
}
Example of combination of global and header sorting in a datagrid table
Column click sorting
The last type of sorting is column click sorting. It is a kind of temporary sorting by values, because if you refresh the page, it is canceled. But it has the highest priority. You can switch on sorting in ascending, descending order and cancel it by consecutive column clicks.
Let’s take previous example and click on the "Total" column.
Example of column click sorting in a datagrid table (first click - ascending)
Example of column click sorting in a datagrid table (second click - descending)
Example of column click sorting in a datagrid table (third click - cancel sorting)
Some examples
Let’s try to sort more complex table in different ways.
widget dataGridBeta{
view comparativeStatistic #averageColours {
backgroundColorFormatter: backgroundColorFormatter4
valueColorFormatter: valueColorFormatter2
}
size: large
column cut #col1 {
value: survey:CustomerCategory
column cut #col2 {
value: survey:AGENTCARE
cell {
value: average(score(survey:OSAT.1))
view: averageColours
target: 8
}
}
}
row cutByMulti {
value: survey:AgentIssue
total: none
row cut {
value: survey:NPSGroup
total: none
}
}
}
Example of datagrid table with nested headers
Now we sort first row level by labels in ascending order by adding of sortOrder.
widget dataGridBeta{
view comparativeStatistic #averageColours {
backgroundColorFormatter: backgroundColorFormatter4
valueColorFormatter: valueColorFormatter2
}
size: large
column cut #col1 {
value: survey:CustomerCategory
column cut #col2 {
value: survey:AGENTCARE
cell {
value: average(score(survey:OSAT.1))
view: averageColours
target: 8
}
}
}
row cutByMulti {
value: survey:AgentIssue
total: none
sortOrder: ascending // Added sorting by labels in ascending order
row cut {
value: survey:NPSGroup
total: none
}
}
}
Example of datagrid table with nested headers and sorting of first row header
After that we will sort second row level by "Disagree" column in "Total". We need to specify path. We have two column levels. The first one is "col1" column. Describing of "Total" for this will looks like "coll1.__total__".
Next level - "col2". It is a grid. That’s why we need to point not only the name (col2) and category code of "Disagree" (1), but field "Cared about" (1) too: "col2.1.1".
At the result path will be equal to "col1.__total__/col2.1.1".
widget dataGridBeta{
view comparativeStatistic #averageColours {
backgroundColorFormatter: backgroundColorFormatter4
valueColorFormatter: valueColorFormatter2
}
size: large
column cut #col1 {
value: survey:CustomerCategory
column cut #col2 {
value: survey:AGENTCARE
cell {
value: average(score(survey:OSAT.1))
view: averageColours
target: 8
}
}
}
row cutByMulti {
value: survey:AgentIssue
total: none
sortOrder: ascending
row cut {
value: survey:NPSGroup
total: none
sortBy: "/col1.__total__/col2.1.1" // Added sorting by values in ascending order
}
}
}
Example of datagrid table with nested headers and sorting of both row headers