Forcing output to 2 decimal places in csv format

[Tsunami]

I've tried everything I can think of but no matter what I do, CSV will drop any zeros after the decimal place. 

I've tried casting as decimal, numeric etc.  Nothing is working.

Any ideas?

[adam_mc]

We are exporting to .csv and NOT experiencing the same issue as you.

Just a thought though, how are you viewing the exported results?

On most Windows machines, Excel is the default for opening .csv files.
Are you sure it is not just Excel re-formatting the cells to show no decimals?

Try opening the file with notepad or wordpad to see the actual data without any formatting.
Or reformat the cells in Excel to have 2 decimals and see if your data now appears correct.

Hope this helps,
Adam.

[Tsunami]

I use notepad as the default.  I was able to force the output by casting it as varchar(20) and then using the following expression:  substring([VarChar Data Item],1,charindex('.',[VarChar Data Item])+2)

That works beautifully....except I'm aggregating amounts to totals on a single row.  The aggregation doesn't work and I have multiple rows per person.  Changing the data item to varchar seems to create 'new' values so the Auto Grouping and Summary function keeps them separate rows.

I've tried creating my totals and then moving it over to a summary data item and THEN applying the formatting but I'm getting an error.

UDA-SQL-0219 The function "charindex" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.

Completely stumped.

[BigChris]

Does it drop the trailing zeroes in other formats as well, e.g. PDF?

[adam_mc]

Please ignore my previous posts, I didn't realize that it was only trailing zeros that weren't displaying.
I thought it was all your decimals!

We do have the same issue happening, but not the same business requirement I guess!

Sorry for any confusion I caused.
Adam.

[Tsunami]

Does it drop the trailing zeroes in other formats as well, e.g. PDF?

No, only CSV.

However, we were able to solve the problem.  We changed all the functions to NATIVE cognos functions (used 'position' instead of 'charindex') and were able to get the report to output properly.

Just another day in the world of Cognos :)

[ad1024]

page 404 of the IBM Cognos Report Studio Version 10.2.2, page 404 states:
"Data formats are not applied in delimited text (CSV) and XML report outputs."

The following technique seems to work for us to force outputting of zero decimal values:
1. Set the report column Data Format property to Text, with no additional formatting properties.
2. In the Expression Definition for the column enter something like:
     ltrim (str ([numeric column], 12, 2))
We used ltrim to eliminate the leading spaces padded by the str function, which looked odd in the final csv.

So, setting the Data Format property to Text and basically doing string formatting in Report Studio before sending to csv seemed to work for us. Of course, this does not address aggregation issues, etc., mentioned in another post.

We also had similar issues formatting date columns, which Cognos wanted to format as date/timestamps in the csv file. The solution was the same basic idea, i.e., setting column Data Format to Text, with additional string formatting. For example, to achieve mm/dd/yyyy date formatting in the csv, we did something like this in the Expression Definition:

replace(str(month([date column]),2,0),' ','0') + '/' +
replace(str(day([date column]),2,0),' ','0') + '/' +
str(year([date column]),4,0)

The month, day and year functions return integer values, and the str function returned 2-char values for the month and day, but with a leading space for the single-digit values, so we replaced with "0" where necessary to get 2-digit months and days.

[NiceCube]

The str function works a treat.  Thanks for posting.