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
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.