[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1209
  • Last Modified:

Sort Alphanumeric Numbers

I have an SQL database that Crystal Reports 10 creates reports from.  It does not sort the Unit Numbers the way I want them to sort.  They are alphanumeric.  Crystal sorts like this:

10-D
100-B
20-F
231-C
40

I want to sort like this:

10-D
20-F
40
100-B
231-C

I found out how to do this in SQL and it is done like this:

ORDER BY CAST(LEFT(tblRenDet.RenDUnitNo, ISNULL(NULLIF(CHARINDEX('-', myfield) - 1, -1), "LEN(myfield))) AS INT), myfield

Does anybody know how to do this in Crystal Reports? Thanks.
0
molard
Asked:
molard
  • 6
  • 3
  • 2
  • +1
1 Solution
 
bdreed35Commented:
Create a formula that returns the numeric value of the field:

//@Alpha Sort
val({table.field})

Sort on this formula rather than the actual field.
0
 
peter57rCommented:
Hi molard,
Create a formula field which gets the numeric value and sort on the formula field:

@Mysort:

val({mytable.myfield})

Pete
0
 
molardAuthor Commented:
It sorts the ones that are only numeric correctly but the alpha numeric ones it still sorts as if it were text instead of the way I would like. If I sort ascending, it sorts all the alpha ones as text and it has all of those together.  It then sorts all the numeric ones correctly.  For example half of my report has the alpha ones sorted at the top and the rest of the report has the numeric ones sorted.

I created a formula called AlphaSort that consists of:

val({mytable.myfield})

I chose to sort by the formula AlphaSort

Great suggestions from the both of you.  Can you think of something else I could try?
0
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 
OutinCommented:
create a formula that does about the same:

tonumber(left({FIELD},InStr ({FIELD},"-")-1)

then order by this formula and then {FIELD}
0
 
OutinCommented:
and add a closing ) at the end of the formula :-)

hth,

Outin
0
 
bdreed35Commented:
How large can the number portion get?
This formula will work by adding leading zeroes to the number portion:

//@AlphaSort

totext(split({table.field},"-")[1],"00000") & "-" & split({table.field},"-")[2]
0
 
molardAuthor Commented:
Outin,

I tried that but I get an error that says:

String length is less than 0 or not an integer.

Thanks for the try.


bdreed35,

The number portion does not get higher than 900.  I will try your formula as well.

Thanks.
0
 
OutinCommented:
Before using my formula or the formula bdreed35 proposed, you should check on the presence of a "-" in the field, otherwise an error is generated:

If {FIELD} like "*-*" then
tonumber(left({FIELD},InStr ({FIELD},"-")-1))
else {FIELD}

--
Outin
0
 
OutinCommented:
Hmm thanks to slow network here you were quicker :-)

--
Outin
0
 
OutinCommented:
Tss it must be monday..

should be

If {FIELD} like "*-*" then
tonumber(left({FIELD},InStr ({FIELD},"-")-1))
else tonumber({FIELD})

I assume there is only data like
10-A
10
but no data like
A
10A
etc.
0
 
molardAuthor Commented:
You did it Outin!  Thanks!
0
 
OutinCommented:
Glad it worked.

Btw it's always nice to see how one problem can have different approaches to find a solution...

--
Outin
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now