help requested with sorting by a nvarchar field in CR8

[cheerfulskeptic]

Hello,
I have a CR8 connected to an SQL server database.. I have a table called SPECS with the primary key being SPECID (nvarchar 10) and a field SPECDESC (nvarchar 255). the SPECID contains a number incrementing for each item inserted. the problem is that we have to sort the data in the crystal report based on SPECID, but because of the nvarchar type of the field, the order is getting messed up. for example, if we have SPECID in 5 fields of 9905,9906, 9907, 9908 and 10035, the 10035 is showing up before the 9905.
any ideas how to convert the nvarchar to INT and sort it int he crystal report?
thanks

 

[glalsop]

Try creating a formula:

tonumber({SPECID});

then sort on the formula.

-Gary

 

[synapsevampire]

Anotehr means is to offload the work to the database by using a SQL Expression:

Insert->Field Objects-Right click SQL Expressions and select New

Name it and place something like:

CAST(table.SPECID as NUMERIC)

Now you can use this field like any other field returned from the database, and the performance will be improved.

-k

 

[cheerfulskeptic]

Thanks a lot, but i'm really sorry - I totally forgot to mention that i have a grouping on table.SPECID in Ascending order. I added an SQL expression called %sqlconvert and specified a convert to int, and grouped on that, but still no luck...

 

[synapsevampire]

Still no luck isn't very descriptive, can you state what the data is, and what's being returned?

The Group should be on the SQL Expression instead of the table.field.

This works fine, so it's oemthing procedurally that you're not getting.

You can also use Gary's formula or:

val({table.field})

and group on that instead.

-k

 

[cheerfulskeptic]

still no luck means, the data is still being displayed with the ID's being sorted by the group headings (the value of SPECID) as in
10035
9010
9062
9063
9099

whereas the 10035 should be after the 9099...
Thanks, i'll try this one now...
-

 

[cheerfulskeptic]

that did the trick. thanks!