Sorting data is a very important analysis tool that allows you to quickly assess the dynamics of growth or decline, as well as rank the data to make it reader-friendly. The matrix in the current version of FastReport .NET enables to sort only measurements. For example, you are building a report that displays employee sales statistics by year. The matrix has a grouping of data by year and month. We need to sort it within each group — year. The standard sorting tools will allow you to sort the names of employees, years, months, but not the data. Especially if you want to sort by a specific column.
To sort by a specific column of the constructed matrix (for example, for a specific employee), you will have to use the report script. Two ways to sort the constructed matrix are to move rows or cells.
On the surface, it seems that moving the rows at once would be a right solution, because sorting implies changing the order in which the entire row is displayed, and not a specific cell. Indeed, this will be the most correct decision, but not always.
We'll look at a case where moving the rows won't work. If your matrix has groups with subgroups in dimensions, then you will have problems moving the first row in the group. This row has the name of the group in the first cell. Subsequent rows from the group have a blank value in the first cell. Since you may change the order of displaying the first row in the group when sorting, an error will occur when a row with an empty group header comes to its place.
To avoid such problems, you will have to sort the cells in the correct column. That is, you first sort the desired column, and then, using a set of cell indices, you sort all the other columns in the matrix by that column. Obviously, this method is much more time-consuming.
Let's take a look at both cases with an example. The first one is to sort the matrix row by deleting and inserting rows in the resulting matrix.
Let's take a look at the source matrix we need to sort:
This screenshot shows a simple matrix that does not have groups with subgroups. Moving rows is ideal for this case. In fact, we will first delete the required rows and then insert them in the correct order. This will be done using the report script.
Let's say we want to sort the matrix by column for 2011. We need to determine the ordinal number of this column and obtain data for all its cells, with the exception of the resulting Total.
Let’s add the ModifyResult event for the matrix object:
In fact, the idea of the method is to read the values of cells and their indices from the desired column and write them to a sorted dictionary. We can arrange the rows in the desired order with the indices of the cells, and, accordingly, the rows. For this, we first copy the rows into a temporary list. Then we delete all rows and insert them according to the indices in the sorted cell dictionary. To insert, we use the matrix rows saved in the temporary list.
As a result, we get a matrix sorted by the column from 2011:
This is the simplest example of how to sort a matrix through one-dimension array. Now let's imagine that we have groups for measurements on the left and we will sort within each group. As noted earlier, to sort the rows is not an option in this case. Let’s view at how to sort cells.
Let's reverse the matrix from the previous example:
We also create a ModifyResult event handler for the matrix:
There are two fundamental differences from the previous method — we sort using replacement, not deletion/insertion, and sort by each column separately.
It should be clear from the comments in the code what to do and where. But still, let's take a quick look:
1) First of all, we get the values of the dimension groups in order to know how many sorting sets we need. Sort order is different for each group.
2) Next, we get the data for all the columns needed for sorting. This means for all columns, except for the first one, which contains the names of the dimension groups.
3) Then we select the set of values required for sorting and sort it.
4) You can arrange the cells in all the sorted columns according to the order of these indexes using the resulting dictionary, where the key is the cell index.
The result is a matrix sorted for Nancy Davolio:
Thus, you can sort the matrix by any column of data. Moreover, you can make custom sorting not only in descending or ascending order. Additionally, you can exclude certain rows (Total or calculated) from sorting by setting them in an individual order.