Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sorting Text items

Status
Not open for further replies.

elsolo21

Programmer
Apr 7, 2008
26
US
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
 
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

 
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.
 
Even though I couldn't reformat them, I used your suggestion to sort on length and that worked perfectly. Thanks for the help!
 
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

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top