Different sort order

[HML]

Our reports access a SQL Anywhere 17 database via ODBC. Now I noticed a strange behavior with Crystal Reports 2016/2020 when sorting.

To reproduce the problem, I created a table with two columns, a sequential number as primary key and a character field, which contains the characters with ANSI code 32 to 255 row by row.

The first report is sorted using the character field:

The sorting is passed directly to the database:

And this is the top of the result:

Due to business requirements, I often work with conditional sorting, i.e. for sorting I use formula fields that return different values depending on parameters. In this example, I have simply wrapped the character field in a formula field @SortFomula

and used this as sort criteria

This forces Crystal Reports to execute the SQL query without the sorting and to perform the sorting internally.

And this is the result:

The sorting is completely different.

Is there any way to control the internal sorting? All tests were done with Crystal Reports Designer 2016, but the same effect also occurs with the current Runtime 13.0.36.

[DonWilliams · Accepted solution]

Here's what I got from Support Rep:

As per the following SAP Knowledge Base you created in 2013, Crystal Reports is developed using Microsoft's C++ compiler, and database sort can differ depending on their collation.

In Crystal Reports sorting is based on the Alphabet and not on the ASCII values. This is why they are seeing a difference in the sort.

     1843282 - Case sensitive sort issue - ASCII compared to Alphabetical values

FYI - info in KBA:

1843282 - Case sensitive sort issue - ASCII compared to Alphabetical values
Symptom

The expected sort is - special characters,  0-9, A-Z, a-z. ASCII sort dependent on the values of the characters

Environment

Crystal Reports

Reproducing the Issue

Sorting a row of data returns these results:

Pre-Sort Order	Ascending Order	Case-Sensitive Order
c	A	A
D	a	B
a	B	C
d	b	D
B	C	a
C	c	b
A	D	c
b	d	d

Cause

Crystal Reports Design and related products are built using Microsoft's C++ compiler and related API set.

Sorting is based on the Alphabet an not on the ASCII values.

Header Data

Released On	06.05.2015 17:44:39 By Don Williams (I819003)
Release Status	Released to Customer
Component	BI-RA-CR Crystal Reports designer or Business View Manager
Priority	Normal
Responsible	Don Williams ( I819003 )
Processor	Don Williams ( I819003 )
Category	Problem
Expires on
Relevant for Translation	No

[HML · Accepted solution]

Thank you Don!