Sorting in cognos.

[rahulrepro]

Hi

It is about Cognos 10.1 sorting feature. Browser IE. Platform Windows.

1. If Sorting field Store# contain values such as ('427', '77', '537') then row sorted given following output. Field Store store all values as String.

Strore#
===============
427
537
77

Requirement is it should show

Store#
============
77
427
537

2. If Sorting field Product# contain values such as ('ABCD','abcd', 'XYX', 'LCD') then row sorted given following output. Field Product# store all values as String.

Product#
============
ABCD
LCD
XYX
abcd

Requirement is it should show like

Product#
============
ABCD
abcd
LCD
XYX

Please advice.

best regards
Rahul Ghosh

[Lynn]

1. Create a data item in the query that casts the store number string into an integer and use that data item as the sort field for your layout. This is only going to work if all the values for store numbers are actually numbers without any letters or other special characters that won't cast to integer.

2. Create a data item in the query that uses either the upper() or lower() function around the product number and use that data item as the sort field for your layout.

[rahulrepro]

Hi Lynn,

Thanks for solution.
Regarding solution 1 all values in Store field alphanumeric. Might have issue to convert as numeric.
Regring solution 2 I think it will work . I will implement this.

best regards
Rahul Ghosh

[Lynn]

Hi Lynn,

Thanks for solution.
Regarding solution 1 all values in Store field alphanumeric. Might have issue to convert as numeric.
Regring solution 2 I think it will work . I will implement this.

best regards
Rahul Ghosh

Sorry, I wasn't clear about my explanation for solution 1. I realize your store number field is alphanumeric, but you can covert a string that contains '123' to an integer. You CANNOT convert a string that contains '123A' or 'B-789' to an integer because there are letters and special characters in addition to numerals in the string.

The three examples you provided in your original post, '77', '427', and '537', will all convert to integers, 77, 427, and 537 using the cast function. If you look at EVERY value contained in your store number field you can see whether they all contain numerals or if any contain letters or special characters. Of course there is no guarantee about what future values might be entered that would fail to convert.

Another option would be to transform the store number to include blank left padding. You don't mention what your database is so the method for achieving this can vary depending on the platform. You'd also need to know the maximum possible length for your store number field and whether or not there is already blank padding that might need to be removed. Using your sample store numbers this method would replace '77' with ' 77' - notice the extra blank at the start of the string - and it will sort properly with the other store numbers that are three numerals.

Below is an example using a repeat function and assuming a 5 character maximum string size. We find the length of the trimmed store number (e.g., 1 to 5) and subtract that from the number six resulting in 1 blank for a 5 character store number, 2 blanks for a 4 character store number, etc. We then concatenate that with the trimmed store number to achieve the left padding. Of course if your database has an lpad function that would be significantly easier.

Code Select Expand


repeat ( ' ', 6 - length( trim ( [Store Number] ) ) )
||
trim ( [Store Number] )


[rahulrepro]

Hi Lynn,

Thanks for solution and effort. I will explain Customer as Store number is alphanumeric hence sorting showing by Cognos is quite correct.

best regards
Rahul Ghosh