Skip to main content

Sort Data by Custom Criteria

  • 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:

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

The resulting report is shown in the image below:

Sort Order Defined in the Custom Array

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.

    Add Calculated Field

  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:

    Sort Data Calculated Field

    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:

    using DevExpress.XtraReports.UI;
    // ...
    private void calculatedField1_GetValue(object sender, GetValueEventArgs e)
            object category = e.GetColumnValue("CategoryName");
            e.Value = sortRule.IndexOf(category);
  4. In the Detail band’s smart tag click the ellipsis next to the Sort Fields editor:

    Detal Band Sort Fields

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

    Group Field Collection Editor

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

    Drop Fields

  7. Specify format strings to display data:

    Format Strings

  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:

Sort Alphanumeric Combinations

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

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]");