Cognos – Formatting Output

In creating reports that are usable by people sometimes there is a necessity to change computer given output in order to make it more reader friendly.  In this accounting package there is limitations on the digits available for job numbers and it is up to a 6 digit field with no special characters so a job number might look like 14248. Unfortunately when creating reports most of the people in the organization would expect the format to be 14-248 instead. To achieve this output I used Cast to break apart the values and add hyphen symbol after the second character.

cast(substring([Job#],1,2)+'-'+substring([Job#],3,3),varchar(6))

Basically this takes the 5 digit codes, grabs two characters starting from the first (hence the 1,2) and adds the – symbol followed by grabbing the last there characters starting at item 3 ( 3,3) and then casts this into a varchar that is 6 digits long.

Leave a Reply

Your email address will not be published. Required fields are marked *