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

2021-09-22

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.

Report template

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.

Creating a matrix event ModifyResult

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.

Comparing the sorting order of matrices on different pages of the report

Thus, using the report script, we can manipulate the data in the matrices, as we want. The most important thing is to apply the same sort order on different pages of the report.

November 09, 2023

How to make a report from C# to FastReport Cloud

In this article, we will look at how to create reports in FastReport Cloud using SDK and export them to any convenient format.
October 04, 2023

How to generate a report from an ASP.NET Core application using FastReport.Core.Skia

We tell you how to generate a report on Windows and Linux using FastReport.Core.Skia and a private NuGet server.
September 25, 2023

How to make price tags with product composition in FastReport VCL

Starting from version 2023.3, another powerful tool has been added to the FastReport VCL reporting engine – text reduction in the Text object.
Fast Reports
  • 800-985-8986 (English, US)
  • +4930568373928 (German)
  • +55 19 98147-8148 (Portuguese)
  • info@fast-report.com
  • 66 Canal Center Plaza, Ste 505, Alexandria, VA 22314

© 1998-2024 Fast Reports Inc.