If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

sort alphanumeric field like numeric fields (f.i. 1,2,3,4,10,11,100,101)

Started by andi7171, 18 Nov 2011 08:51:50 AM

Previous topic - Next topic

andi7171

Hello,
I am looking for the best way to do a "numerical" sort on an alphanumeric field in Cognos 8.3 - report studio.

There is a field which is declared as alphanumeric for the reference-number. It is a combination of a numeric field - and the document-type - and so we have 4045I (for Invoice), and a 4045CR (for a Credit-Note).

Because I have reference-numbers in a range from 1 to 100000 I can not use the referencenr as a sort field - because the reference 10000 is before the reference 2 (alphanumeric sort).

Is there ANY other possibility to prevent a workaround with an extra field .... for instance to tell the report studio to do a sort on the referenceNr as an "alphanumeric-number" - sort (like excel does???). Or is there an option in the frame-work manager ?

Thank you in advance
kind regards
andi

HalfBloodPrince

Try This and let me know.
(Assuming Invoice case)
Create a query calculation->Sort_col
cast(replace(Invoice_code],'I',''),Integer)  and sort in ascending  order.

then go to advance sort add->sort_col under it add  Invoice_code column.