Sort Records

i-net Designer allows you to filter, sort and group the records fetched from the database. The Sort Records Wizard is for adding sort fields to your report. You can sort in ascending, descending and original order. Also you can define sort direction over formula. This formula should return a positive integer value. Allowed values: 0 for ascending, 1 for descending and 2 for original (also you can use the constants "ascending", "descending" and "original" instead of integer values). All of other formula will be ignored. There are only fields in this formula permitted, which are constant for the whole report (e.g. database fields are not allowed).

To open the Sort Records dialog, click on the Report dialog and select the "Sort Records" entry.

To add a sort criterion select one of the database or formula fields in the fields browser and add it to the list of sort fields on the right side. To remove a sort criterion, select the appropriate field in the list of sort fields on the right side and move it to the left.

Clicking the "Okay" button applies your changes.

Hints for sorting on formula fields

If you have set a formula as sort criterion, i-net Clear Reports tries to translate it into SQL syntax to insert it into the ORDER BY clause. It is depending basically on the used formula if the translation is successful and if the statement is runnable on the database. The formula should be as simple as possible. The sort order depends on the exact database or from i-net Clear Reports if the formula was not executable on database. It is clear that running the formula on database side is much more faster as the sorting on i-net Clear Reports side.

Things to avoid in the sort formula:

• Use of variables
• control structures (loops, switch, if then else)
• Any kind of programming - try to build one expression.
• convert functions with format pattern
• use of aggregate functions

To find out if a formula was runnable on database side, you have to take a look into the log file.
Search for the entry "[CC,INFO] Evaluate sort formula.". If that entry is followed by "[CC,WARN] Report Warning [-150] ...", it was not possible to translate the formula to SQL. The reason is after the warning.

Note:

• The order of the data can be a different if the formula is executed on database or from i-net Clear Reports.
• i-net Clear Reports also tries to optimize the statement as good as possible. If an used function in the formula can be directly evaluated, the function will be replaced by its result.

Functions that are not executable on database:

• all Summary functions (aggregate functions)
• all financial functions
• all date ranges
• all array functions
• all print state functions
• all additional functions except DatetimeToTime and DatetimeToDate
• String functions: Join, Filter, Split, NumericText, StrReverse, ToWords, ToText(with more than one parameter), Replace(with more than three parameter)
• Date/Time functions: DateSerial, IsDate, IsTime, IsDatetime, TimeSerial, DateDiff(with firstDayOfWeek parameter), DatePart(with firstDayOfWeek parameter), Monthname(with abbreviate parameter)
• Programming shortcuts: Switch, Choose

See also:

• Sorting Constants

i-net software strives to provide accurate product documentation. Please give us your feedback using the form below.
NOTE: This form is for documentation feedback only. For technical assistance, please send an email to crystalclear@inetsoftware.de.