MS Visual Studio How to sort similar matrices through one-dimensional array on several pages in FastReport .NET

[FireWind]

How to sort similar matrices through one-dimensional array on several pages in FastReport .NET
September 22, 2021 by Dmitriy Fedyashov

Let's say we have the task: to sort the matrix on the first page in the desired order, remember this order and apply for similar matrices on other pages.

This may be needed when you have several pages in the report that display matrices that are identical in headings, but which contain different data. For example, the first matrix displays the number of products sold, and the second displays the sales amounts by product. We need to sort by quantity or amount, and then apply the same order for the second matrix. This case is quite common in analytical reports.

Let's see it in practice. Let's take a completely hypothetical fruit sales statistics. However, only the types of fruits are not enough, there will be a list of fruit importing countries. The number of sold goods will be displayed for three years.

Table structure:

• country_name
• fruit_type
• year
• amount
• price
• sum

Sorting​

Standard sorting mechanisms will not help us here. Therefore, we will sort the number of fruits sold for each country. Let's outline a sequence of steps:

1. To get a list of countries.

2. For each country:

2.1. to get the values of cells with types of fruits and the number of products sold for each year;

2.2. to sort the values for the desired year;

2.3. for each row to fill the cells of fruits and the number for all years according to the indices of the rows in the sorted list.

The first column is the country, and this is ok for us, which means that we will sort the cells of the remaining columns. We first need to remember them, so that we can arrange them in the desired order according to the sorting plan. We will select one of the columns with data for a specific year and sort it in descending or ascending order. Then we will use the resulting index order to sort all the cells by column.

Let's get started. The matrix has an event for modifying an already constructed object - ModifyResult. Let's create a handler for this event in the report script.

private List<List<int>> sortOrders = new List<List<int>>();
//List of sorting orders for each collection of fruit species by country
 
 private void Matrix1_ModifyResult(object sender, EventArgs e)
 {
//Dictionaries in which we will store the row index and cell value
 Dictionary<int, double> firstYearCells = new Dictionary<int, double>();
 Dictionary<int, double> secondYearCells = new Dictionary<int, double>();
 Dictionary<int, double> thirdYearCells = new Dictionary<int, double>();
 Dictionary<int, string> typeCells = new Dictionary<int, string>();
 Dictionary<int, double> sortCells = new Dictionary<int, double>();
 
//bool prevYearSortNeeded = false;
 
 var total = false;
 var z = 1;
 var val2 = 0.0;
 var val3 = 0.0;
 
 List<string> countries = new List<string>();
 //We will store the list of countries in this list
 //We get all countries from the first column
 for (int j=2; j<(sender as TableBase).ResultTable.RowCount-1; j++)
 {
 try
 {
 var val = (sender as TableBase).ResultTable.GetCellData(0,j).Value.ToString();
 if (val.Length > 0) countries.Add(val);
 }
 catch (Exception)
 {}
 }
 
 int columnFirstYearIndex=0;
 int columnSecondYearIndex=0;
 int columnThirdYearIndex=0;
 int columnTypeIndex=0;
 
//We go through all the columns of the matrix to save the cells in dictionaries
 for (int t=0; t < (sender as TableBase).ResultTable.ColumnCount; t++)
 {
 
 if ((sender as TableBase).ResultTable.GetCellData(t,0).Text.Contains("2017"))
 {
 columnFirstYearIndex=t;
 }
 if ((sender as TableBase).ResultTable.GetCellData(t,0).Text.Contains("2018"))
 {
 columnSecondYearIndex=t;
 }
 if ((sender as TableBase).ResultTable.GetCellData(t,0).Text.Contains("2019"))
 {
 columnThirdYearIndex=t;
 }
 if ((sender as TableBase).ResultTable.GetCellData(t,0).Text.Contains("Fruit"))
 {
 columnTypeIndex=t;
 }
 }
 
 int countryOrder =0;
 
 //We run a loop to identify the fruit groups and sort them for each country
 foreach (var country in countries)
 {
 total = false;
 
 sortCells.Clear();
 //We clear the list for sorting
 
 //We select cells from rows until we see Total, since Total should not be sorted
 while (!total)
 {
 if ((string)(sender as TableBase).ResultTable.GetCellData(columnTypeIndex,z).Text!="Total")
 {
 //We select cells for the first year
 var value = (sender as TableBase).ResultTable.GetCellData(columnFirstYearIndex,z).Value;
 if (value!=null)
 {
 Double.TryParse(value.ToString(),out val3);
 firstYearCells.Add(z,val3);
 }
 else
 firstYearCells.Add(z, 0.0);
 
//We select cells for the second year
 value = (sender as TableBase).ResultTable.GetCellData(columnSecondYearIndex,z).Value;
 if (value!=null)
 {
 Double.TryParse(value.ToString(),out val3);
 secondYearCells.Add(z,val3);
 }
 else
 secondYearCells.Add(z, 0.0);
 
 //We select cells for the third year
 value = (sender as TableBase).ResultTable.GetCellData(columnThirdYearIndex,z).Value;
 if (value!=null)
 {
 Double.TryParse(value.ToString(),out val3);
 thirdYearCells.Add(z,val3);
 }
 else
 thirdYearCells.Add(z, 0.0);
 
//We select cells for fruit types
 value = (sender as TableBase).ResultTable.GetCellData(columnTypeIndex,z).Text;
 typeCells.Add(z,value.ToString());
 }
 else
 {
//Exit condition of the loop
 total = true;
 }
 z++;
 }
 
 sortCells = firstYearCells;
 //We set the column for sorting - in this case by the first year
 
 List<int> keys = new List<int>();
 
//If we have a filled list of sorts for all countries, then the first page of the report has been built and you can use this list on the second page. This is where sorting through one-dimensional array is ensured.
 if ( sortOrders.Count == countries.Count )
 {
 keys = sortOrders.ElementAt(countryOrder);
 }
 else
 keys = sortCells.OrderByDescending(i=>i.Value).Select(key => key.Key).ToList();
//Sort the array in descending order using the Linq library
 
 int k = 0;
//Loop through all the elements of the sorted list
 foreach(var key in keys)
 {
//Build cell values for all columns in sort order
 (sender as TableBase).ResultTable.GetCellData(columnFirstYearIndex, firstYearCells.Keys.ElementAt(k)).Text = firstYearCells[key].ToString();
 (sender as TableBase).ResultTable.GetCellData(columnSecondYearIndex, secondYearCells.Keys.ElementAt(k)).Text = secondYearCells[key].ToString();
 (sender as TableBase).ResultTable.GetCellData(columnThirdYearIndex, thirdYearCells.Keys.ElementAt(k)).Text = thirdYearCells[key].ToString();
 (sender as TableBase).ResultTable.GetCellData(columnTypeIndex, typeCells.Keys.ElementAt(k)).Text = typeCells[key].ToString();
 k++;
 }
 if (keys.Count>0) sortOrders.Add(new List<int>(keys));
//Save the sort order for the current country
 
 //It's important to clear
 firstYearCells.Clear();
 secondYearCells.Clear();
 thirdYearCells.Clear();
 typeCells.Clear();
 countryOrder++;
//Go to the next country
 }
}
}

Now we copy the report page with the matrix, but instead of the amount field we will output sum.

We will select the handler we have created for ModifyResult in the matrix events.

After running the report, we will see that the order of the fruit types on the two pages is the same. This means that the sorting on the first page is applied to the second page.