SSRS IIF for converting data to integer

Question

I have matrix report which dynamically generates my columns. Now one of the column has all integer data so I want to convert that to Integer type. Now I tried the below expression:

=IIF(IsNumeric(Fields!Answer.Value),CInt(Fields!Answer.Value),Fields!Answer.Value)

But this gave me error for some of the columns where some of the row data contains integer only while other rows contains mix. So I tried the below expression but still no luck!

=IIF(Fields!Question.Value = "Amount:", CInt(Fields!Answer.Value), Fields!Answer.Value)

So basically only the Amount: column has true integer values, while all other columns have either all text or mix of text and integer data. So for all the columns with data as text and integer both, I get the error.

Sample Data:

+------+--------------+-----------+
|Name: |Phone Number: |Emp Number |
+------+--------------+-----------+
|Alice |123-456-7890  | 123       |
|Bob   |2345678901    | 12345     |
+------+--------------+-----------+

In above sample data, Amount: contains all integer values whereas Name is all text and phone number is text and integer. Using the IIF(...) expression I get the below result.

Result:

+--------+--------------+-----------+
|Name:   |Phone Number: |Emp Number |
+--------+--------------+-----------+
|#Error  |#Error        | 123       |
|#Error  |2345678901    | 12345     |
+--------+--------------+-----------+

I looked at these references here and here for the solution but no luck. Any help on what I might be missing.

Instead of trying to convert the data, have you tried just formatting it using the same logic? Set the format property to something like =IIF(Fields!Question.Value = "Amount:", "f0", "") — Alan Schofield
Yes I tried that but the report came out with empty column data. So I looked for other options. — Naphstor
You probably need to post some sample data from your dataset including the datatypes so we can reproduce your scenario — Alan Schofield
Not sure what you're trying to accomplish by converting the numbers, but if you just want to format them you could use FORMATNUMBER since the result is also a string. =IIF(Fields!Question.Value = "Amount:", FORMATNUMBER(Fields!Answer.Value, 0), Fields!Answer.Value) — Hannover Fist
You could also try using the VAL function instead of CInt. Just like VAL(Fields!Answer.Value). Check chris's answer here for more details on why this works better in this case. — Steve-o169

Naphstor Naphstor · Accepted Answer · 2019-07-01T18:06:39

Figured out the solution from this reference post.

=IIF(IsNumeric(Fields!Answer_Question.Value), CLng(Val(Fields!Answer_Text.Value)), Fields!Answer_Text.Value)

I have used CLng() instead of CInt() as the Phone Number column field had long values.

SSRS IIF for converting data to integer

1 Answers