Group Data by Custom Criteria

• Mar 20, 2023
• 3 minutes to read

This tutorial shows you how to group a report based on custom criteria in Visual Studio Report Designer. In this example, products are grouped by the first letter of the product name, which is the value of the calculated field.

Prerequisites

Do the following to create a blank report bound to the Products data table:

1. Add a new blank report to the project. Review the following help topic for details: Create a Report in Visual Studio.

2. Click Add Report Data Source in the report’s smart tag menu to invoke the Data Source Wizard, as illustrated in the following image:

   

3. Create a data source bound to the Products table in the nwind.db SQLite database from the XtraReports installation (the default path is C:\Users\Public\Public Documents\DevExpress Demos 23.2\Components\Data\nwind.db). Review the following help topic for more information: Data Source Wizard.

4. Click Field List in the Visual Studio XtraReports menu to invoke the Field List panel. The panel appears as follows:

   

Create a Calculated Field

1. 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.

   

   The DataMember property for the created field is set to the Products table and the calculated field can access data fields from this table.

2. Right-click the created field and select Edit Calculated Fields:

   

3. In the invoked Calculated Field Collection Editor, specify the Name property. The DisplayName property is automatically set to the same value. Set the FieldType property to String:

   

4. Click the ellipsis button next to the Expression property to invoke the Expression Editor. Enter the following expression in the editor:

   Substring([ProductName],0,1)
   

   The expression extracts the initial letter from the ProductName data field. For more information on expressions, review the following help section: Use Expressions.

5. To close the editor and save the changes, click OK.

   

   To close the editor and save the changes, click OK.

Group by the Calculated Field

1. Use the Group and Sort panel to create a Group Header band associated with the calculated field. If the panel is hidden, click Group and Sort in the XtraReports menu:

   

   To create grouping criteria, click Add a Group and select the calculated field from the invoked drop-down menu:

   

   You can use this panel to show or hide the related Group Footer band. For more information, review the following help topic: Group and Sort Panel.

2. Switch to the Field List and drop the ProductName data field onto the report’s Detail band. Drop the FirstLetterProductName calculated field onto the Group Header area:

   

3. Switch to the Preview tab to see the result: