Making sure values are treated as Numeric when SQL Reporting 2008 renders a report as Excel

Question

When I save a SQL Report in Excel, a column containing numbers is treated as text in the resulting .xls file. On each row the cell has a green right triangle and a yellow diamond exclamation mark saying "The number in this cell is formatted as text or preceded by an apostrophe."

Can someone explain to me how to make it so the rendered report has no error for this column? My users wish to treat these cells as numeric; i.e. to perform calculations on these values, but they are text.

Answer 1

You can get around this by casting the field within the report.

=CDbl(Fields!mycolumn.Value)

Edit The above example is the work around that I have always used for this issue, however, I just finished reading a post what the user noted that this was not needed, if you used the following code.

=IIF(Fields!FieldName.Value=0,Nothing,Fields!FieldName)

This seems to fix the issue and allow the use of FormatNumber.

The original post can be found here.