Sorting strings numerically
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21136
Printed Date: 04 May 2024 at 10:28am
Topic: Sorting strings numerically
Posted By: crystalnewbie33
Subject: Sorting strings numerically
Date Posted: 27 Oct 2014 at 5:39am
|
I have a string field that needs to be sorted numerically instead of alpha-numeric. Only numbers will be entered into the field. Alpha-numeric shows this way:
1 10 11 2 23 37 8 I found a formula that works by creating a formula field with the following: Right("0000" & {table.field},4) However, I don't particularly like this solution as it results in this: 0001 0002 0008 0010 0011 0023 0037 I prefer to not have the zeros. Is there a way I can hide the zeros or is there another solution for sorting string fields numerically that would work better (and hopefully be just as simple)? |
Replies:
Posted By: crystalnewbie33
Date Posted: 27 Oct 2014 at 5:55am
|
I tried
ToNumber(Right("0000" & {table.field},4)) and it seems to work. Are there any concerns with using this formula? |
Posted By: z9962
Date Posted: 27 Oct 2014 at 6:11am
| why not just use and sort on this?ToNumber({table.field}) |
Posted By: crystalnewbie33
Date Posted: 27 Oct 2014 at 6:27am
| Thank you for the suggestion. That works just as well and is a simpler formula. What if the user enters a letter into the field? They should not do it intentionally but is possible. ToNumber will return an error. I guess that is why the original formula I used was suggested to me. Do you know how I could hide the zeros using my original formula? |
Posted By: DBlank
Date Posted: 27 Oct 2014 at 6:47am
|
your original formula would also fail on a non numeric value as it is also using the tonumber() function.
You have to decied what you want done with a non-numeric value. If you want to force tehm to the top of the sort then somethine like
if isnumeric({table.field}) then ToNumber({table.field}) else -1
You can use that for sorting (in the sort expert) and then use the original field for display on the report canvas.
if it is used in a grouping then suppress the group field and drop the original field on the group header for display
|
Posted By: crystalnewbie33
Date Posted: 27 Oct 2014 at 8:08am
Thanks DBlank, I didn't think to sort by the converted field but simply display the original field. That was the palm to the forehead I needed. 
|