sqlexcelbusiness-intelligencecognos-8

Preserve trailing zero in excel


My field is of character type but displays a decimal. My cognos report output is in excel format and I am trying to retain the trailing zero in my sql. Any suggestions?

I use the following sql to maintain leading zeroes and it works :

'=' + '"' + [Value] + '"'

However, it does not seem to be effective for trailing zeroes.


Solution

  • Try the following:

    '=' + '"''' + [Value] + '"'
    

    which becomes ="'[Value]" (note the extra ' in there). Failing that, try

    '''' + [Value]
    

    which becomes '[Value]

    The extra apostrophe at the beginning tells Excel that the field should be treated as text rather than a number.