Sorting Text items

[elsolo21]

I have a data element that is formatted as text. The are actually dollar values. I need to find a way to transform them in Cognos to be able to sort in value order, not text order so instead of:

$1,000,000
$5,000
$70,000

I need it to be:

$5,000
$70,000
$1,000,000

If I knew the list of values would always be static, I would create an item to number these values and sort them that way. However, a new one could pop up at any time. I know the easiest way is to go back and change the format in the model but that's not an option right now.

Any tips?

thanks

 

[blom0344]

Why not reformat them in the report?

SQL server:

cast_integer(replace(substring([dataitem],1,10),',',''))

A rough sort would be by checking the length of the string:

datalength([data_item])

Ties Blom

 

[elsolo21]

When I used your SQL (and other methods to reformat) I get:

An error occurred while performing operation 'sqlOpenResult' status='-28'.

I'm not sure what that's about. Any ideas?

thanks for the help.

 

[elsolo21]

Even though I couldn't reformat them, I used your suggestion to sort on length and that worked perfectly. Thanks for the help!

 

[blom0344]

Congrats!
If the package is published without enabling native functions (like for SQL server) then cognos will not be able to compile/read them, which will result in an SQL error..

Ties Blom