The i-net Designer enables you to filter, sort and group the records fetched from the database. The Sort Records Wizard can be used to define the sort fields for your report. You can sort in ascending, descending and original order. Furthermore you may define a formula to determine the sort order at runtime. This formula has to return a positive integer value. Possible values are: 0 for ascending, 1 for descending and 2 for original (or you may use the constants "ascending", "descending" and "original" instead of integer values). This formula is only allowed to refer to static content like prompts or formula fields with execution time 'before reading'. Database fields, SQL fields or summaries are not allowed here.

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 "Ok" button applies your changes.

If you have set a formula field as a sort criterion, i-net Clear Reports tries to translate it into SQL syntax to insert it into the ORDER BY clause. It basically depends on the formula of the field, whether the translation is successful and whether the statement is runnable on the database. So, the formula should be as simple as possible. The sort order depends on the actual database or on i-net Clear Reports in case the formula was not executable on database. In general, running the formula on database side is much more efficient than the internal sorting of i-net Clear Reports.

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 whether a formula was runnable on database side, you'll have to take a look into the log file.
Seek 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 appended to the warning.

By default, string fields are sorted alphabetically. This kind of sort order is not intuitive for a human reader when it comes to string that contain numeric values. For example the string 'text11' will be sorted to before 'test2'. To overcome this issue, you may enable 'alpha-numeric sorting' for ascending and descending order of string fields. This sort algorithm will sort like normal with the exception, that numeric values are sorted not as a chunk of characters but by their numeric values.

Note: Alpha numeric sorting cannot be performed by any database itself. It requires i-net Clear Reports to fetch all records and sort locally. This may have a high performance any memory impact depending on the amount of records.

Some examples for the difference of normal ascending and ascending alpha-numeric order:

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.

• 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

• Sorting Constants