sort order

[ridhirao22]

Hi, Using CR 9 version with SQL 2000

I have a report where I need to sort the job postions high to low within each positions:
EX:
Dir 4+
Dir 4
Dir 3
Dir <3
Dir 2
Dir 1
Dir
Max numbers in some positions are 11

There are different positon like VP,SVP,Assco ect...
How do I sort them. I used a formula to do this but doesn't work as I need it. Any help is apperciated.

Thanks,
RR

 

[Madawc]

Assuming each detail record has just one position, do a Formula field that assigns them a rank. E.g.

if {Position} = "SVP" then "01) SVP"
else if {Position} = "DIR 4" then "05) DIR 4"
...

You can then use Report > Record Sort Expert to order them.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

If the numbers are part of the position field, create a formula like this to use for sorting ONLY:

split({table.position}," ")[1]+" "+totext(val(split({table.position}," "),2)),"00000000000")

-LB

 

[lbass]

My formula should have been:

if split({table.field}," ") > 1 then
split({table.position}," ")[1]+" "+totext(val(split({table.position}," "),2)),"00000000000") else
{table.position}

This would work for all cases EXCEPT if there really are fields that contain "<" and "+". Are the above samples instances of your actual fields? Please show actual data for the field.

-LB

 

[ridhirao22]

Ibass, Thanks for the reply. I tried the your formula and get error something like "Not part of the formula" around the "00000000000").

"<" and "+" part of the field data. The Ex: I showed is the actual data.

Thanks,
RR

 

[lbass]

And do you mean that some positions look like:

Dir 12345678934

?

Are there any other non-numeric characters in the data?

-LB

 

[ridhirao22]

No, just as I showed in Ex: the highest position I found in a single desgination like Dir, SVP,VP,Assoc etc is 11 and could be more going futher...

so...order is like

Dir 11
Dir 10
Dir 9
Dir 8+
Dir 7
Dir <6
Dir 5
Dir 4..so on until 0 and last one as just Dir

This number is followed for other desgination like Dir, SVP,VP,Assoc

Current I am using a formula use if else then for each occurance. Want something more dynamic.

Thanks,
RR

 

[lbass]

Use two formulas in the record sort area then, in this order:

//{@sort1}:
stringvar array x := split(replace(replace({table.position},"<",""),"+","")," ");
if ubound(x) > 1 then
x[1]+" "+totext(val(x[2]),"0000")

//{@sort2}:
if instr({table.position},"<") > 1 then
-1 else
if instr({table.position},"+") > 1 then
1 else
0

-LB