Sometimes in crystal especially if the data source for the report is from a stored procedure you will have the situation where you need to sort the report on a particular field and the field has come into the report as a character type where as the data type should have been a number, so if you tried to sort on this field the sort would be a character and not a number sort.
Simple way to correct this without changing the data source is to add a formula field that converts the character field to a number, then we add the formula to the group section we want to do the sort on and then hide this new report field (unless of course you want to display it) and then lastly change the group sorting options to this new formula field.
Summarised bullet points,
- Create a new formula that we will sort on, set the formula syntax to be ToNumber(FieldName)
- Now add the formula to the report under the group that you want to sort on
- Change the sort field, under the report main menu select “record sort expert” find your group and then change or create the sorted field to be your new formula field
- Unless you want to display the formula field on report make it hidden