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.