Report Design: sorting on alpha-numeric field

sorting on alpha-numeric field

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6497
Printed Date: 19 Apr 2024 at 8:28am

Topic: sorting on alpha-numeric field

Posted By: johnwsun
Subject: sorting on alpha-numeric field
Date Posted: 28 May 2009 at 5:55am

Hi all,

I have a report where there are order numbers in alpha-numeric pattern. This report is run against a number of institutions where there are inconsistent coding of order number being adopted:

WB123

NBIT234

I cannot use normal ascending order from Recort Sort Expert

because WB13 will be after WB123.

I created a formula @sort_order_number where:

val(replace({LIX.ID},left({LIX.ID},2),""))

{LIX.ID}is order number field. WB, NBIT are institution prefix.

But it only handles order number with two prefix. Could someone advise if it's possible to make the formula flexible to handle >= 2 prefix? or it can also sort order number properly without prefix?

Thanks in advance.

John

Replies:

Posted By: rahulwalawalkar
Date Posted: 28 May 2009 at 6:31am

What is the pattern i.e. will the numbers always fall on right hand side?,can you post few more sample records to play with....

cheers

Rahul

Posted By: lockwelle
Date Posted: 28 May 2009 at 6:35am

I would use a loop something like:

local numbervar i;

for i := 1 to len({LIX.ID}) Do

( if isNumeric(mid({LIX.ID}, i)) then

exit for

)

then continue with your formula, this should strip the leading alpha characters. If they use alpha later on, this will always fail.

HTH

Posted By: rahulwalawalkar
Date Posted: 28 May 2009 at 6:41am

use the code below in your SQL

Left(SubString(LIX.ID, PatIndex('%[0-9.-]%', LIX.ID), 8000), PatIndex('%[^0-9.-]%', SubString(LIX.ID, PatIndex('%[0-9.-]%', LIX.ID), 8000) + 'X')-1) AS 'Sort on LX ID'

the above code will extract just the number part from the character string,once done you can use the above field to sort the records.

Cheers

Rahul

Posted By: johnwsun
Date Posted: 28 May 2009 at 6:50am

Thank you all,

I would like to use lockwelle's advice to begin with.

Hi Lockwelle,

How do I continue use my fromula from your for loop? Please advise...

John

Posted By: lockwelle
Date Posted: 28 May 2009 at 7:40am

Rahul,

Hadn't thought of regular expressions in Crystal...I will have to keep that in mind. Thanks again.

John

local numbervar i;

for i := 1 to len({LIX.ID}) Do

( if isNumeric(mid({LIX.ID}, i)) then

exit for

);

mid({LIX.ID}, i)

having not actually done this, but the above should be all you need to update you existing formula. I would probably comment out the existing code and add this. Hopefully it will work as desired.

If not try Rahul's suggestion. Should just need to drop it in the same formula as his is returning the numeric portion as well, just using a different technique.

Posted By: johnwsun
Date Posted: 28 May 2009 at 7:56am

Thanks, lockwelle,

It doens't work.

Hi Rahul, is your approach applied on SQL database on in the Crysatal Report itself?

Posted By: johnwsun
Date Posted: 28 May 2009 at 8:04am

Hi lockwelle,

It works now after I put Val in front of

mid({LIX.ID}, i)

so Val(mid(({LIX.ID}, i))

Thanks a lot!

Posted By: rahulwalawalkar
Date Posted: 29 May 2009 at 4:47am

Hi John

My approach is in SQL SELECT STATEMENT.....then using view or stored procedure to get the results and use in the report.

Cheers

Rahul

Posted By: johnwsun
Date Posted: 30 May 2009 at 6:41am

I would like to revisit this sort problem

with the below:

WB123

WB234

WB33

the formula @sort_orderID

for i := 1 to len({LIX.ID}) Do

( if isNumeric(mid({LIX.ID}, i)) then

exit for

);

val(mid({LIX.ID}, i))

can handle the sort properly.

However, when the ID has suffix, such as 134WBIT, 135WBIT, 23WBIT (institutions have order id entered inconsistently), the above formula fails. If I changed sort by using CR's normal record sort instead of formula, then it works for ID with suffix.

Lockwelle, could you please advise some improvement for this formula.

Rahul, when I tried to create a view with your SQL, there is error on SQL database.

Select Left(SubString(LIX.ID, PatIndex('%[0-9.-]%', LIX.ID), 8000), PatIndex('%[^0-9.-]%', SubString(LIX.ID, PatIndex('%[0-9.-]%', LIX.ID), 8000) + 'X')-1) AS 'Sort on LX ID'

Please advise, thank you all.

John

Posted By: DBlank
Date 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.

Posted By: johnwsun
Date 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;

);

This works for both prefix and suffix.

John

Posted By: Rasta
Date 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

Posted By: johnwsun
Date 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;

);

John

Posted By: Rasta
Date 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