Sort Data by Custom Criteria

• Mar 20, 2023
• 3 minutes to read

This topic shows you how to implement custom logic to sort report data. To perform this task, create a calculated field, handle its CalculatedField.GetValue event to calculate the value, and sort the report data by that field.

How to Set the Sort Order by Custom Array

In this example, the index in the array of category names determines the order in which the categories are sorted in the report.

The array definition is as follows:

C#

using System.Collections;
// ...
    ArrayList sortRule = new ArrayList() {
        "Produce", "Meat/Poultry", "Dairy Products",
        "Grains/Cereals", "Seafood", "Confections",
        "Condiments", "Beverages" };

VB.NET

Imports System.Collections
'...
    Dim sortRule As ArrayList = New ArrayList() From {
        "Produce", "Meat/Poultry", "Dairy Products",
        "Grains/Cereals", "Seafood", "Confections",
        "Condiments", "Beverages"}

The resulting report is shown in the image below:

The report created in this example uses the sample Northwind database.

Do the following to create a report that sorts the categories in custom order:

1. Create a blank report and bind it to the Categories table.

2. Click Field List in the Visual Studio’s XtraReports menu to invoke the Field List panel. In the Field List panel, right-click any data field and select Add Calculated Field in the context menu. Review the following help topic for more information: Calculated Fields.

   

3. Select the newly created calculatedField1 and press F4 to invoke the Properties Window. In the Properties Window, switch to the Events tab and specify the CalculatedField.GetValue event handler:

   

   Add the code that calculates the index of the current category in the sortRule array and assigns it to the value of the calculatedField1 field:

   C#

   using DevExpress.XtraReports.UI;
   // ...
   private void calculatedField1_GetValue(object sender, GetValueEventArgs e)
       {
           object category = e.GetColumnValue("CategoryName");
           e.Value = sortRule.IndexOf(category);
       }
   

   VB.NET

   Imports DevExpress.XtraReports.UI
   ' ...
   Private Sub CalculatedField1_GetValue(sender As Object, e As GetValueEventArgs) Handles CalculatedField1.GetValue
   Dim category As Object = e.GetColumnValue("CategoryName")
   e.Value = sortRule.IndexOf(category)
   End Sub 
   

4. In the Detail band’s smart tag click the ellipsis next to the Sort Fields editor:

   

5. In the invoked Group Field Collection Editor specify the calculated field as the field by which to sort the data:

   

6. Drop the fields from the Field List window to the Detail band:

   

7. Specify format strings to display data:

   

8. Load the report in the Document Viewer or End-User Report Designer (the Visual Studio Designer Preview does not raise the CalculatedField.GetValue event) and preview the result.

How to Sort Data by Alphanumeric Combinations

This example demonstrates how to use a calculated field to sort alphanumeric combinations - the names that consist of aphabetical and numeric strings. The resulting report appears as follows:

To perform this task, use the approach described in the previous section. The CalculatedField.GetValue event handler contains the following code:

C#

using DevExpress.XtraReports.UI;
// ..
private void calculatedField1_GetValue(object sender, GetValueEventArgs e)
{
    string originalValue = e.GetColumnValue("Item").ToString();
    string[] values = originalValue.Split('-');
    int symbolCode = 0;
    string symbol = values[0][0].ToString().ToLower();
    if (IsAlpha(symbol))
    {
        // The numbers presumably can range from 0...1000. 
        symbolCode = (int)values[0][0] * 1000;  
    }
    int numericCode = Convert.ToInt32(values[1]) + symbolCode;
    e.Value = numericCode;
}
private static bool IsAlpha(string str)
{
    return !System.Text.RegularExpressions.Regex.IsMatch(str, "[^a-zA-Z]");
}

VB.NET

Imports DevExpress.XtraReports.UI;
' ...
Private Sub CalculatedField1_GetValue(sender As Object, e As GetValueEventArgs) Handles CalculatedField1.GetValue
    Dim originalValue As String = e.GetColumnValue("Item").ToString()
    Dim values() As String = originalValue.Split("-"c)
    Dim symbolCode As Integer = 0
    Dim symbol As String = values(0).Chars(0).ToString().ToLower()
    If IsAlpha(symbol) Then
        ' The numbers presumably can range from 0...1000. 
        symbolCode = AscW(values(0).Chars(0)) * 1000
    End If
    Dim numericCode As Integer = Convert.ToInt32(values(1)) + symbolCode
    e.Value = numericCode
End Sub
Private Shared Function IsAlpha(ByVal str As String) As Boolean
    Return Not System.Text.RegularExpressions.Regex.IsMatch(str, "[^a-zA-Z]")
End Function