number field turned out as text in excel , SSRS

Question

Hi,

My SSRS reports one of the field is using this expression , where the field is a number field. I want to see blank field is the value is 0.

=IIf(Fields!PERIOD02_VALUE.Value <> 0,Fields!PERIOD02_VALUE.Value ,"")

the excel exported version is having this field considered as text not number.

Is there any other way to do this from SSRS side.

Thanks, Suni

Answer 1

Excel is recognizing this field as text because you are outputting an empty string whenever the field is 0.

You could try this, to output a null value instead of an empty string:

=IIf(Fields!PERIOD02_VALUE.Value <> 0,Fields!PERIOD02_VALUE.Value,  Nothing)

This isn't guaranteed to work, however, as I seem to remember Excel assumes that the field is the type of whatever is in the first data row.

Answer 2

I had this problem. Some cells would be text and others numbers - all with the same formatting string. The resolution is to multiply your cells expression by 1.000 (1 doesn't work!) - this forces SSRS to format the cell as a number. eg; (your expression here) * 1.000