Sorting Issue: Dash in sort value is skewing sort

[nlaliberte]

I'm using Crystal XI and an OLE DB connection to a SQL server.

We have having problems with sorting a group. The values we are sorting all have a dash somewhere in the title, however it seems that the sort function in crystal is not functioning correctly. Below is a list of the groups in the order which Crystal is sorting them. It is supposed to be sorted Ascending.

Group VI-Buy
Group VII-Buy
Group VIII-Buy
Group VIII-Mez
Group VII-Ven
Group VI-Partner
ZZ-Unknown


As you can see 'Group VII-Ven' and 'Group VI-Partner' are not in the correct order. I realize that inserting a space before and after the '-' will eliminate the problem, however I see this as more of a band-aid than a solution.

Has anyone ever experienced an issue like this that could lend some insight as to what exactly is happening here?

 

[lbass]

Did you create the group field by formula? If so, you would be better off using numbers (converted to text, and with leading zeros to make the format consistent) instead of Roman numerals, at least for the grouping. You could then convert them back to Roman Numerals for display purposes if you like.

-LB

 

[Madawc]

Is this your report, or someone else's? I ask because I can see no logic in the sequence, unless the Group has been given this as a 'Specified Order'.

As lbass says, you can get round it by using regular numbers. Or defining your own 'Specified Order'. But I'd still like to know how such a thing could be happening.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[jdemmi]

Also --- Couldn't report be GROUPED ON an ID field or similar but not the field that is displayed and then the GROUP NAME is customized via the customize group name field option?

If this is the case I could see where the displayed group names dd not appear to be sorted correctly.


-- Jason
"It's Just Ones and Zeros

 

[nlaliberte]

I guess you guys didn't really understand the issue. The field names are pulled directly from the database so there is no control over the naming structure. Also, The order I listed them in is the order that Crystal is displaying them, the reason you can't find any logic to the order is because it's wrong, thus the issue.

The cause is the hyphen is ignored by most sorting functionality. I finally found this in the Excel help files. It caused the above groups to be sorted as though they were actually:

Group VIBuy
Group VIIBuy
Group VIIIBuy
Group VIIIMez
Group VIIVen
Group VIPartner
ZZUnknown

The only solution is adding a space in before or after the hyphen. Also lbass's recomendation of grouping on a formula based off the name and using the name for the display also is a good workaround.

 

[jdemmi]

I understand the problem and was only suggesting that it is possible to group the report by one field yet display a different value for the group name. This outcome would/could cause the sorting to appear off.

Also -- in regards to the hyphen -- Oracle handles the hyphen properly when sorting -- I am suprised to find that MSSQL , apparently, does not -- But keep in mind Excels notes on sorting apply only to Excel. Besides...if the hyphen were ignored....you wouldn't have a problem.

(In regards to my Oracle test...if I run the follwing query in my test db, select * from test order by myvalue it sorts as expected (as you desire)

MYVALUE

VI-Buy
VI-Partner
VII-Buy
VII-Ven
VIII-Buy
VIII-Mez
ZZ-Unknown



-- Jason
"It's Just Ones and Zeros

 

[lbass]

Do you have a solution or do you need more help--I cannot tell.

-LB

 

[nlaliberte]

Yes I have a solution, we added a space in after the hyphen. Because it was ignored crystal was technically sorting it correctly, I just wanted to share with everyone, in case they didn't know, that they hyphens will be ignored when sorting, and will thus cause a problem if there is no break in the string around the hyphen.

Thanks for the help.

 

[jdemmi]

I am using Crystal 11 and Oracle 10g.....I have hundreds of thousands of records with hyphens in the value. I do not experience the same sorting behavior you do. I am curious as to what else is at play her.

-- Jason
"It's Just Ones and Zeros