//@Alpha Sort

val({table.field})

Sort on this formula rather than the actual field.

Solved

Posted on 2005-04-18

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.

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.RenDUn

Does anybody know how to do this in Crystal Reports? Thanks.

12 Comments

//@Alpha Sort

val({table.field})

Sort on this formula rather than the actual field.

Create a formula field which gets the numeric value and sort on the formula field:

@Mysort:

val({mytable.myfield})

Pete

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?

tonumber(left({FIELD},InSt

then order by this formula and then {FIELD}

This formula will work by adding leading zeroes to the number portion:

//@AlphaSort

totext(split({table.field}

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.

If {FIELD} like "*-*" then

tonumber(left({FIELD},InSt

else {FIELD}

--

Outin

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Tool to convert Crystal Report (rpt) to Jasper Report | 2 | 113 | |

Converting int to currency | 5 | 20 | |

cross tab report accumulative amount | 9 | 31 | |

Rounding duration within 20 min intervals | 3 | 14 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**13** Experts available now in Live!