I need to sort a report in Crystal reports by number

the problem is that your field is a text field... and it is sorting the text correctly.

one option would be to strip the initial character off the field, convert the result to number and sort.

to do this, you'd create a formula.  something like :
tonumber(split({fieldname},'p')[2])

and then sort on that formula.

of course, this will only work if the character proceeding the number is always a p.

the character can be one of the following letters,

a, b, c, f, g, hss, l, m, p, r or w, maybe a few others i'm forgeting.  And i can't drop the letters, i have to keep them and the full number

Agree.  Can you change the data collection to use P01, P02, etc for the data?

mlmcc

In theory I could possibly change the entries to all have a 0 as the start value for the numbers. In practice it would prove very difficult to implement.

Try a formula liek this and use it to sort the records.

Left({YourField},1) & Right('0'& mid(YourString},2),2)

mlmcc

or...
if the beginning characters are always alpha, you could test (using isnumeric()) and return the numeric portion, convert to numeric and sort.

this is similar to my earlier proposal... except it would work for non "p" characters.  you would use this formula for SORTING ONLY.  when you display the field in the report, use the actual field and not the one that's being used for sorting.

how to use isnumeric:
http://publib.boulder.ibm.com/infocenter/iadthelp/v7r0/index.jsp?topic=/com.businessobjects.integration.eclipse.designer.doc/designer/Functions121.html

mlmcc's formula will only work if the field has one letter followed by one number.  You showed one case with 3 letters (hss) and some 2 digit numbers in your examples.  That formula won't handle those.

 With a variable number of letters, I don't see an easy (one line) way to do this.  You could do it by separating the letters and numbers (probably using a loop to count the letters), padding the numbers with 0's, and then recombining them.  "p1" would become "p01".  "hss5" would become "hss05".  And so on.  But there is at least one potential problem with that approach.

 How big could the numbers be?  You need to add enough 0's to pad all of the numbers after the letters to the same length.  For example, if the largest number after "p" would be 14 (as in your example), you only need one 0, but if it could be up to 9999, you'd need 3 0's (so "p1" would become "p0001", etc.).


 It sounds like you're grouping by this field.  Do you really need to group by it (to get a group header or subtotals or whatever), or can you just sort by it instead?  If you really only need to sort by this field, I think I'd try breaking the field into separate pieces for the letters and numbers and grouping by them separately.  The outer group would be on the letters at the start of the field, and the inner group would be on the number that came after those letters (using a formula to convert the numeric characters to a number, so you don't have to worry about adding leading 0's).

 The potential problem is that you don't have the same grouping as if you were grouping by the field as a whole, although there might be ways around that.

 James

Mid({SOmeString},2) will take all characters from the second character on

mlmcc

James,

The second idea you gave me sounds like it might work. that is if there is a way to break up the field.

Currently the report generates from a temp file that is automatically created by a program called fabsuite. I have no string or anything like that for the letters or the numbers. The letter and number are both included in one string. So i would need a formula? i'm guessing to break the string up by letter and number?

I dont need to group by this field, i just need it sorted by this one field. The rest of the Fields dont really matter just this one.  But i'm not sure how to just tell it to sort the report by this field without using a group to see if that solves the problem.

Change myString to equal your database field.

mlmcc

where in the report is the formula supposed to go?

Since you want to sort or group on it, it doesn't need to really be in the report just use it for the sort or group field.

mlmcc

Why worry about leading 0's? now that you have stripped out the number from the text, simply convert it to a number to make the sort work correctly...

ToNumber(Mid({YourSortField},2))

gav

I'm not sure why you're posting a reply to a 2 month old question, but they couldn't just use the number.  They needed to use the characters and the number for the sort.  That's why we were trying to add the leading 0's (or split the field into one part for the characters and one for the number and sort/group by both parts).

 James