How to: Sort Data

• Jul 02, 2019
• 4 minutes to read

When performing a mail merge with the SpreadsheetControl, you can specify the sort order in which data entries will appear in the resulting document. This document provides an example on how to specify sort criteria for multi-level detail data.

1. Prepare the Application
2. Sort Top-Level Data
3. Sort Detail-Level Data
4. How Sort Criteria are Saved in a Template
5. Get the Result

Prepare the Application

To get started with this tutorial, run the application created in the How to: Create a Master-Detail Report example. A master-detail template is automatically loaded into the SpreadsheetControl. This template contains a two-level detail range (click the Mail Merge Design View button in the Ribbon to show template ranges):

• Detail - the detail range that displays top-level data from the Categories table of the sample Northwind database.
• DetailLevel0 - the nested-level detail range that displays data from the CategoriesProducts child table.

In each detail range, you can sort data by fields of the associated data member. In this example, sort top-level data by category name alphabetically, and specify two sort criteria for detail-level data - by product unit price in descending order (primary), and by product name in ascending order (secondary).

Sort Top-Level Data

1. Select any cell within the Detail range (but outside of the DetailLevel0 nested range), and click the Sort Fields button on the Mail Merge tab, in the Sort & Group group. This invokes the Sort Fields dialog.

2. In the dialog, click New and select the CategoryName data field to be used as a criterion for sorting categories.

3. Define the Ascending sort order.

Sort Detail-Level Data

1. Click anywhere within the DetailLevel0 range and invoke the Sort Fields dialog.
2. Click New and select the UnitPrice data field to be used as the primary criterion to sort the products in a category. Set the Descending sort order.
3. Click New and select the ProductName data field to be used as a secondary criterion to sort category products. Set the Ascending sort order.

How Sort Criteria are Saved in a Template

A sort field specified for a detail range in the mail merge template is a “SORTFIELD(n)” defined name, where (n) is the zero-based index of the sort field (sort fields are numbered consecutively throughout the template). The name of the data field specified as a sort criterion is assigned to the DefinedName.RefersTo property of the defined name, and the sort order is set to DefinedName.Comment.

A defined name that corresponds to a detail range holds all sort fields specified for this detail range in its DefinedName.Comment property (associated sort field names are divided by semicolon).

For example, after you have specified sort fields as described above, the following defined names are added…

Name	RefersTo	Comment
SORTFIELD0	“CategoryName”	Ascending
SORTFIELD1	“UnitPrice”	Descending
SORTFIELD2	“ProductName”	Ascending

… and these sort fields are listed in the DefinedName.Comment property value of the defined names that specify detail ranges.

Name	RefersTo	Comment
DETAILRANGE	Sheet1!$B$4:$D$10	SORTFIELD0;
DETAILLEVEL0	Sheet1!$B$9:$D$9	SORTFIELD1;SORTFIELD2;

Defined names specifying sort fields are added to the Worksheet.DefinedNames collection of the template worksheet.

Get the Result

Click the Mail Merge Preview button to review the result.

See Also

How to: Group Data

How to: Filter Data