Crystal Reports sorting on alpha-numeric field

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: sorting on alpha-numeric field

<< Prev Page of 2

Author	Message
DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 30 May 2009 at 8:35pm
	JOhn, Can you add an if statement to this so if the last character is not numeric then put in 0 else put in your other formula Then do a primary sort on this formula field and a secondary sort on the field itself? All your items that end in a non numeric value will be listed first as a 0 as the primary sort, then these zero sorted items should sort correctly using the secondary sorting of the original field.
	IP Logged


johnwsun Senior Member Joined: 28 May 2008 Location: Australia Online Status: Offline Posts: 179	Posted: 30 May 2009 at 10:19pm
	Hi DBlank, I have borrowed a formula from Issue 57 of Crystal Clear( News and Hints for Cryatl Reports) by Chelsea Technologies: @sort_order_num local numbervar digit; local numbervar v; local stringvar s; local numbervar i; v :=0; for i := 1 to len({LIX.ID}) do ( s:= mid({LIX.ID},i,1); if isnumeric(s) then digit := val(s); v:= v*10 + digit; ); v This works for both prefix and suffix. John Edited by johnwsun - 30 May 2009 at 10:20pm
	IP Logged

Rasta Newbie Joined: 13 Jul 2009 Location: Australia Online Status: Offline Posts: 2	Posted: 13 Jul 2009 at 2:22am
	Hello Everyone. I am trying to sort data in a report and one of the problems I am facing is alphanumeric room numbers. eg 01A, 01B, 02A ... and 01.1, 01.2, 02.1, 02.2.... I was wondering if some one could help me because the normal ascending order of sorting a group does not help. Your help will be a big help and I would like to thank you in advance. Cheers
	SAR
	IP Logged

johnwsun Senior Member Joined: 28 May 2008 Location: Australia Online Status: Offline Posts: 179	Posted: 13 Jul 2009 at 5:15am
	Hi SAR, Have you tried the following lines: local numbervar digit; local numbervar v; local stringvar s; local numbervar i; v :=0; for i := 1 to len({LIX.ID}) do ( s:= mid({LIX.ID},i,1); if isnumeric(s) then digit := val(s); v:= v*10 + digit; ); v John
	IP Logged

Rasta Newbie Joined: 13 Jul 2009 Location: Australia Online Status: Offline Posts: 2	Posted: 14 Jul 2009 at 1:51pm
	Thank you for replying John. I am trying to use the formula that you have given. I needed the report as there was no other way of making work easier and so I added another group which was the room number and then sorted it according to specified list. BUT this means I have numerous reports, one for each facility. I was able to do that as the room numbers are fixed. I will try this and let you know.. If I get one report then I can replace the .rpt file the database application is using and I can use it through the software.. Now I have to run it through logicity application builder. Thanks John
	SAR
	IP Logged

<< Prev Page of 2

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum