How To Sort Records In Reports

This topic discusses a report writer commands that can be used to control the order in which records are sorted when printing reports.

Normally, Collect! optimizes key selection automatically when you use loops and where clauses in your reports. This means that items in a list will be displayed in order, perhaps by file number or date or debtor name. The order depends on the data you are pulling into your report. This "default" order may not suit you. In that case, there are several commands that you can use to set the way the items in a list are ordered.

Firstkey

Normally, Collect! optimizes key selection automatically and this setting overrides the default behavior to allow you to explicitly set the report sort order.

SYNTAX: @firstkey On a line by itself before the beginning of the loop you want to sort through.

CASE: insensitive (capital or small letters are acceptable)

USAGE: Use this command to force the system to search and sort by the first WHERE condition in a loop specifier.

EXAMPLE:

@firstkey
@de no total where (@de.fi = 1000 .. 1500) where (@de.na = A .. M)
@de.fi @de.na
@de

SAMPLE OUTPUT:

1009 Kelley, Frank
1037 Drummond, Leonard
1090 Bernardo, Richard
1327 Christie, Laura
1328 Heinberg, Klaus
1424 Bronte, Raveena
1437 Moise, Steven

Optional Keys

The @firstkey command also works will with queries on optional key fields. If the primary key you are using is smaller than the optional key, then you should use the @firstkey command as follows.

This example, using optional key @de.em, will display all debtors who have no email address. In the example, using @firstkey forces Collect! to query on Debtor File Number first so that the query returns its correct results.

@firstkey
@de where (@de.fi > 0) where (@de.em < !)
@de.na @de.em
@de

Please see How To Filter Data With The Where Clause for more details.

Bestkey

Collect! will automatically choose an index for a report to maximize speed.

In a loop with multiple WHERE clauses, sometimes more than one database index fits the complex search criteria. Collect! uses a query optimizer to select the best index in order to minimize database accesses.

This makes the report run as fast as possible, but arbitrarily sets the reporting sort order based on the automatically selected index.

SYNTAX: @bestkey On a line by itself before the beginning of the loop you want to sort through.

CASE: insensitive (capital or small letters are acceptable)

USAGE: Use this command to force the system to search and sort by what Collect! considers as the best key found in your WHERE conditions in the loop specifier.

EXAMPLE:

@bestkey
@de no total where (@de.na = A .. M) where (@de.fi = 1000 .. 1500)
@de.na @de.fi
@de

SAMPLE OUTPUT:

1090 Bernardo, Richard
1424 Bronte, Raveena
1327 Christie, Laura
1037 Drummond, Leonard
1328 Heinberg, Klaus
1009 Kelley, Frank
1437 Moise, Steven

If you need to explicitly report records in a certain order, please refer to the Firstkey command.

Reverse

Collect! automatically displays a list starting with the lowest number. This command reverses the order that results printed in the report. It can also be used to reverse sorting in lists of names.

SYNTAX: reverse This has no @ symbol and is placed right after the loop specifier. If there are multiple WHERE clauses in your loop, Collect! will reverse the order based on an automatically selected key.

This command can be used with the @firstkey and @bestkey commands for further customization.

CASE: all lower case -- does NOT start with the @ symbol

USAGE: Reverse the order of the list in a report.

EXAMPLE:

@de reverse no total where (@de.fi = 1000 .. 1500)
@de.na @de.fi
@de

SAMPLE OUTPUT:

1437 Moise, Steven
1424 Bronte, Raveena
1328 Heinberg, Klaus
1327 Christie, Laura
1090 Bernardo, Richard
1037 Drummond, Leonard
1009 Kelley, Frank

The reverse command can be used with @firstkey or @bestkey.

EXAMPLES:

@firstkey
@de reverse no total where (@de.fi = 1000 .. 1500) where (@de.na = A .. M)
@de.fi @de.na
@de

SAMPLE OUTPUT:

1437 Moise, Steven
1424 Bronte, Raveena
1328 Heinberg, Klaus
1327 Christie, Laura
1090 Bernardo, Richard
1037 Drummond, Leonard
1009 Kelley, Frank

@bestkey
@de reverse no total where (@de.fi = 1000 .. 1500) where (@de.na = A .. M)
@de.fi @de.na
@de

SAMPLE OUTPUT:

1437 Moise, Steven
1009 Kelley, Frank
1328 Heinberg, Klaus
1037 Drummond, Leonard
1327 Christie, Laura
1424 Bronte, Raveena
1090 Bernardo, Richard

Orderby

Collect!'s ORDERBY clause enables you to apply advanced sorting to loops and nested loops in reports. You can specify ascending or descending order for displaying the results. Any combination of fields in any combinations of sort orders can be used. ORDERBY can be used alone or in conjunction with WHERE clauses to give you the exact results for complex reports.

Please refer to Help topic, Advanced Sorting in Reports for details.

Batchoff/Batchon

Nested loop conditions might fail in certain reports due to batch fetching interference, especially when outer/inner loops share the same record type. To prevent this, batch fetching may be temporarily disabled with @batchoff. The command @batchoff switches OFF batch fetching and @batchon switches it back ON. This is only necessary in very particular cases where nested loops are causing the Report Writer to fail.

Batch fetching will automatically reset to its default position, ON, whenever a new report is printed.

Summary

For additional examples of lists, loops and where clauses, please check the sample reports and letters that ship with Collect! and visit our web site.