Number field turned out as text in Excel, SSRS

Question

In my SSRS reports one of the fields is using this expression, where the field is a number field. I want to see blank field if 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?

Neodymium

Answer 1

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

Directrix

Answer 2

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.

Dissymmetry

Answer 3

I had this problem in the excel export and solved it with the CLng for integers and CDbl function for decimals.

=CLng(Fields!Qty.Value)

This guarantees that the excel cell will be numeric and is cleaner than Fields!Qty.Value * 1.000

Decided

This should be marked as the correct answer. Thanks for your help. – Chlori

Yes, I know this is old but worked for me also -- finally a solution. Thanks – Fermentative

Answer 4

The problem is that your IIF statement ends with an empty string, "". If you change that to a zero, it will work as expected:

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

Instead of...

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

Weakkneed

Answer 5

Below are 3 different solutions for 3 different glitches :
Value 1

1234
blank cell
1234

Solution 2

=IIF(IsNumeric(Fields!TEST.Value.ToString()), CDBL(IIF(IsNumeric(Fields!TEST.Value.ToString()), Fields!TEST.Value.ToString(), "0")), " ")

Value 2

1234
Null
345
null

Solution 2

=IIF(IsNumeric(Fields!TEST.Value.ToString()), CDBL(IIF(IsNumeric(Fields!TEST.Value.ToString()), Fields!TEST.Value.ToString(), "0")), "NULL")

Value 3

1234
Null
345
N/A
550
TBA
440
599
NOT APPLICABLE

Solution 3

=IIF(IsNumeric(Fields!TEST.Value.ToString()), CDBL(IIF(IsNumeric(Fields!TEST.Value.ToString()), Fields!TEST.Value.ToString(), "0")), Fields!TEST.Value.ToString()),

Beaming

It's not clear how these "Values" answer the question. – Praedial

you can assume "Value" AS Column names ,rest are values in columns – Beaming