I need to sort a report in Crystal reports by number

I am very new to crystal reports so maybe this is an asked and answered question but how can i have a report sort by a text and number? I.E.

Right now say have these entry's;

p1
p2
p3
p4
p5
p6
p7
p8
p9
p10
p11
p12
p13
p14

Now the way they are shown above is how i want to show them,
But crystal reports sorts them like this;

p1
p10
p11
p12
p13
p14
p2
p3
p4
p5
p6
p7
p8
p9

Which is not working at all.  I have Crystal Reports 2008 on windows vista home premium 64bit.

The most info i can give you is that the field is sorted by a Group Header with the property;

tempproductioncontrolitems.PieceMarkDisplay-A

where tempproductioncontrolitems.PieceMarkDisplay-A=the value's shown above which is sucks in from Fabsuite (this part is working fine its just the sorting part that doesnt)

The properties under "change group" are;

common:

sorted by
in ascending order.

options:

none are selected

any help will be great, thanks.
MTLSOLAsked:
Who is Participating?
 
mlmccCommented:
You could use a formula like this.  I assume once a number is found, the rest is numeric

Local StringVar leadingtext;
Local NumberVar index;
Local STringVar mystring := "ppppp123";
index := 1;

while  Not isNumeric(mystring[index]) do
(
    leadingtext := leadingtext & mystring[index];
    index := index + 1;
);
leadingtext & Right("00000" & Mid(mystring,index),5)

You should be able to sort on that formula.

mlmcc
0
 
zephyr_hex (Megan)DeveloperCommented:
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.
0
 
MTLSOLAuthor Commented:
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
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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

mlmcc
0
 
MTLSOLAuthor Commented:
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.
0
 
mlmccCommented:
Try a formula liek this and use it to sort the records.

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

mlmcc
0
 
zephyr_hex (Megan)DeveloperCommented:
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
0
 
James0628Commented:
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
0
 
mlmccCommented:
Mid({SOmeString},2) will take all characters from the second character on

mlmcc
0
 
MTLSOLAuthor Commented:
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.
0
 
mlmccCommented:
Change myString to equal your database field.

mlmcc
0
 
MTLSOLAuthor Commented:
where in the report is the formula supposed to go?
0
 
mlmccCommented:
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
0
 
James0628Commented:
mlmcc,

 FWIW:

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

 You're right.  I'm not sure if I was mis-reading your Mid function, or thinking (incorrectly) that your Right function was going to take the leading 0 and one other digit (that would be Left, not Right).  Either way, I was wrong about the formula not handling 2 digit numbers.


 MTLSOL,

 Something like mlmcc's latest formula should work.  The only caveat is that it assumes that the number part will never be more than 5 digits long (up to 99999).  If it could be longer, you'd need to either increase the number of 0's and the count in the last line accordingly, or you could (as I suggested earlier), have one formula that pulled out the non-numeric leading characters, and a second formula that pulled out the numeric characters and converted them to a number, and sort (or group) by both of those formulas.  The only real advantage to doing them separately is that if you convert the numeric characters to a number, you don't have to worry about adding the correct number of leading 0's.

 As for how to sort on the formula(s), instead of group, create the formula(s) and then go to Report > "Record Sort Expert", and you should be able to tell CR to sort the report on the formula(s) there.

 James
0
 
gavsmithCommented:
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
0
 
James0628Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.