How to sort matrix by indicator
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
public class ReportScript { // the key of the pair is the value in the cell of the matrix, the value of the pair is the y-coordinate of the cell // the dictionary will be sorted by cell value in this way private SortedDictionary<double, int> numbers = new SortedDictionary<double, int>(); private void Matrix1_ModifyResult(object sender, EventArgs e) { int x = 1; int y = 2; // let's collect the values of the cells in the column for 2011, we will sort by it for ( ; y < Matrix1.ResultTable.RowCount - 1; y++) { object val = Matrix1.ResultTable.GetCellData(x, y).Value; double dval = 0.0; if (val != null) { // here it is important to know the types of values or to check them // the cell format is Currency in this example, so we first convert it to a string // the default cell format is string Double.TryParse(val.ToString(), out dval); numbers.Add(dval, y); } // we add a pair with a value of 0.0 to the dictionary if there is an empty string in the cell // as a result, empty strings will be taken into account when sorting and will go first else { numbers.Add(dval, y); } } // copy the rows of the matrix into the backing array // then we will take the necessary rows from it and insert into the matrix object[] originalRows = Matrix1.ResultTable.Rows.ToArray(); int i = 2; // the number of the row where we will start deleting rows in the matrix // now we delete the second row in the matrix as many times as there are rows to be sorted // we keep deleting the second row, because all rows will move up one position after deleting it for (int j = 0; j < numbers.Count; j++) { Matrix1.ResultTable.Rows.RemoveAt(i); } i = 2; // now we just add all the rows in order according to the sorted list foreach (int v in numbers.Values) { int rowNum = v; Matrix1.ResultTable.Rows.Insert(i, originalRows[rowNum] as TableRow); i++; } } } |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 |
public class ReportScript { public class DescendingComparer<T>: IComparer<T> where T : IComparable<T> { public int Compare(T x, T y) { return y.CompareTo(x); } } // the key of the pair is the value in the cell of the matrix, the value of the pair is the y-coordinate of the cell // the dictionary will be sorted by cell value in this way private SortedList<int, double> numbers = new SortedList<int, double>(); private void Matrix1_ModifyResult(object sender, EventArgs e) { int x = 3; int y = 2; Dictionary<int, double> cellsMonth = new Dictionary<int, double>(); Dictionary<int, double> cellsFirst = new Dictionary<int, double>(); Dictionary<int, double> cellsSecond = new Dictionary<int, double>(); Dictionary<int, double> cellsThird = new Dictionary<int, double>(); Dictionary<int, double> cellsFourth = new Dictionary<int, double>(); Dictionary<int, double> cellsTotal = new Dictionary<int, double>(); List<List<int>> allCells = new List<List<int>>(); bool other = false; int z = 2; double val2 = 0.0; var val3 = 0.0; string message = ""; List<string> years = new List<string>(); for (int j=0; j<Matrix1.ResultTable.RowCount; j++) { var column = Matrix1.ResultTable.Columns[0] as TableColumn; try { years.Add(Matrix1.ResultTable.GetCellData(0,j).Value.ToString()); } catch (Exception) {} } //We do the cycle for each year foreach (var year in years) { total = false; // We get the cell values for each year for a given column while (!total) { // We exclude Total being in the list of sorted values if (Matrix1.ResultTable.GetCellData(1,z).Text!="Total") { //Column of months var value = Matrix1.ResultTable.GetCellData(1,z).Value; if (value!=null) { Double.TryParse(value.ToString(),out val3); cellsMonth.Add(z,val3); } else cellsMonth.Add(z, 0.0); //Column for first employee value = Matrix1.ResultTable.GetCellData(2,z).Value; if (value!=null) { Double.TryParse(value.ToString(),out val3); cellsFirst.Add(z,val3); } else cellsFirst.Add(z, 0.0); //Column for the second employee value = Matrix1.ResultTable.GetCellData(3,z).Value; if (value!=null) { Double.TryParse(value.ToString(),out val3); cellsSecond.Add(z,val3); } else cellsSecond.Add(z, 0.0); //Column for the third employee value = Matrix1.ResultTable.GetCellData(5,z).Value; if (value!=null) { Double.TryParse(value.ToString(),out val3); cellsFourth.Add(z,val3); } else cellsFourth.Add(z, 0.0); //Sort column. It will serve as a sorting reference for other columns value = Matrix1.ResultTable.GetCellData(4,z).Value; if (value!=null) { Double.TryParse(value.ToString(),out val3); cellsThird.Add(z,val3); } else cellsThird.Add(z, 0.0); //Column for the fifth employee value = Matrix1.ResultTable.GetCellData(6,z).Value; if (value!=null) { Double.TryParse(value.ToString(),out val3); cellsTotal.Add(z,val3); } else cellsTotal.Add(z, 0.0); } else { total = true; } z++; } //Let’s sort the cellsThird by the list, which is the column for the third employee var keys = cellsThird.OrderByDescending(i=>i.Value).Select(key => key.Key).ToList(); //We set a new value for the cells in all strings in the required columns according to the order in the sorted dictionary for the third column int k = 0; foreach(var key in keys) { Matrix1.ResultTable.GetCellData(1, cellsThird.Keys.ElementAt(k)).Text = cellsMonth[key].ToString(); Matrix1.ResultTable.GetCellData(2, cellsThird.Keys.ElementAt(k)).Text = cellsFirst[key].ToString(); Matrix1.ResultTable.GetCellData(3, cellsThird.Keys.ElementAt(k)).Text = cellsSecond[key].ToString(); Matrix1.ResultTable.GetCellData(4, cellsThird.Keys.ElementAt(k)).Text = cellsThird[key].ToString(); Matrix1.ResultTable.GetCellData(5, cellsThird.Keys.ElementAt(k)).Text = cellsFourth[key].ToString(); Matrix1.ResultTable.GetCellData(6, cellsThird.Keys.ElementAt(k)).Text = cellsTotal[key].ToString(); k++; } cellsThird.Clear(); } } } |
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.
