How to: Sort Data

• Jan 23, 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.

   Note

   Note that the sorting feature is not available if a mail merge template is not bound to a data source or if the currently selected cell in the template is outside the detail range.

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

   Note

   Note that the Sort Fields dialog invoked for the selected detail range (for a top-level or nested-level detail range) allows you to only choose the fields of the data member associated with this detail range.

   

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.

   

   You can adjust the priority of the sort criteria, if necessary, using the Up and Down buttons in the dialog.

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.