Advanced Sorting In Reports

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.

When using ORDERBY with WHERE clauses, ORDERBY must be the last command on the loop initiator line.

A progress message is displayed twice a second as the batch sort fetching proceeds. The user can press ESC to cancel and abort the report. Any results already processed will be displayed or printed.

When processing the Orderby command, Collect! does not process fields that are not on the record. Collect! ignores the command to "orderby" any virtual field that is not actually on the records being sorted.

Orderby

The most basic way to use ORDERBY is to place the ORDERBY clause after the loop initiator.

Values to use for sorting are separated by commas.

Example:

@de orderby @de.na, @de.ow
@de.na< @de.ow<
@de

This example will sort the output by Debtor Name and then by Owing in ascending order.

Results:

Orderby Ascending Or Descending

By default, ORDERBY sorts in ASCENDING order. You can specify ASCENDING or DESCENDING as needed.

Use asc to specify ASCENDING.
Use desc to specify DESCENDING.

Example:

@de orderby @de.na asc, @de.ow desc
@de.na< @de.ow<
@de

This example will sort the output by Debtor Name (ascending) and then by Owing (descending).

Results:

Orderby With Where Clause

ORDERBY may be used with one or more WHERE clauses for further defining your report criteria.

When using ORDERBY with WHERE clauses, ORDERBY must be the last command on the loop initiator line.

Example:

@de no total where (de.ow > 100) orderby @de.na, @de.ow
@de.na< @de.ow<
@de

This example will display all debtors with Owing greater than 100 and will sort them by Debtor Name and Owing, both ascending.

Results:

Orderby In Nested Loops

ORDERBY may be used at any level of nested looping in advanced reports. Any combination of fields in any combinations of sort orders may be used.

Example:

@cl orderby @cl.cl asc
@cl.cl< @cl.na<
@cl.de orderby @de.ow
@de.na< @de.ow<
@cl.de
@cl

This example will sort all the clients by Client Number (ascending) and each client's debtors by Owing (ascending).

Results:

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.

Please be aware that switching OFF batch fetching will result in very slow report writing when you are looping through large lists of accounts or transactions.

Troubleshooting Orderby

To perform the sorting functions with ORDERBY, Collect! fetches records in batches. This is usually very fast. If it is taking time, Collect! will display a progress message.

You can press the ESC key at any time to stop the process and abort the report. Any data already processed will be printed or displayed in Print Preview.

In order to sort, the entire result set must be in RAM. If the entire result set won't fit, the result set will be incomplete. If the report is too large for Collect! to place in RAM for sorting, an error message will be displayed.

Orderby Sorting Error on Space Limitations

Summary

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 exact results for complex reports.

See Also

- How to Sort Records in Reports