RDLC report doesn't detect NULL values correctly

Question

I have a problem with generating .rdlc report. One of the columns has this expression:

IIF(CInt(Fields!MyColumn.Value) = 0 or Fields!MyColumn.Value is nothing,"Unknown",Fields!MyColumn.Value)

I've also tried to use that field as a string:

=IIF(IsNothing(Fields!MyColumn.Value) or Fields!MyColumn.Value is nothing,"Unknown",Fields!MyColumn.Value.ToString())

When the value of MyColumn is not NULL, the report displays the value properly, but when it is NULL (or 0 when it's converted into int type) the report returns #Error. The weird thing is that when I remove the if function and display only the value of that field, the report displays 0 or blank (it doesn't return the error). How can I fix this?

Solution 1

You could try validating without a comparison:

=IIf(Fields!YourColumn.Value
    , Fields!YourColumn.Value
    , "Unknown")

Or reversing your check (check if it exists, instead of checking if it doesn't exist):

=IIf(Fields!YourColumn.Value > 0
    , Fields!YourColumn.Value
    , "Unknown")

Also, I'm not sure, but it may have something to do with using different value types in the same column. Try using the same value type for an entire column. For example, only output strings, or only output ints.

If nothing works you can also check for NULL values in your code and then set the value to 0 or -1 (or so). Then in your RDLC report you can check on that.

Solution 2

I think that you have to check BEFORE if your field is null or not, otherwise you will get the error in the comparison (NULL greater then 0? -> error!)

So your formula must be:

=IIF(IsNothing(Fields!MyColumn.Value) or CInt(Fields!MyColumn.Value) = 0,"Unknown",Fields!MyColumn.Value)

I would try also the Switch function:

=Switch(
    IsNothing(Fields!MyColumn.Value), "Unknown", 
    Fields!MyColumn.Value = 0, "Unknown", 
    Fields!MyColumn.Value > 0, Fields!MyColumn.Value, 
    )

Also, if your field is already a number you don't have to use CInt(""), otherwise the formula will be:

=Switch(
    IsNothing(Fields!MyColumn.Value), "Unknown", 
    CInt(Fields!MyColumn.Value) = 0, "Unknown", 
    CInt(Fields!MyColumn.Value) > 0, Fields!MyColumn.Value, 
    )

Comments

[NDraskovic]

I've tried this approach and I still get the error. I tried to get the type of that column - when there is value the type is System.String but when the value is null it returns the #Error

[NDraskovic]

I've tried the switch function, it didn't help. IsNothing doesn't work either, and I've tried by length (Len(Fields!MyColumn.Value)=0) but I still get the #Error.

[Gianni B.]

version of VS? Btw you can also use is nothing but you have to check that before the comparison with an int!!

[Deruijter]

Could you try adding a String.Format to both the false and the true output? =IIf(Fields!YourColumn.Value , String.Format("{0}", Fields!YourColumn.Value) , String.Format("{0}", "Unknown") Alternatively, you could run a NULL check in your code and set the value to 0 or -1 if so, then check on that.

[NDraskovic]

String.Format didn't work either. Is there a way to tell the rdlc what to display in case of an error? Something like try-catch in ordinary code

[NDraskovic]

Hey, I managed to solve it by changing the data in the DataTable. Can you please post that as an answer so I can mark it as correct.

[Deruijter]

Could you elaborate? You checked for NULLs in code and then changed the value to a 0 (or so) and checked on that?

[NDraskovic]

I looped trough my DataTable rows with foreach loop and if the value of the specific column was "" I changed it to "-1". This is the code: foreach (DataRow dr in drMyTable.Rows) { if (dr.ItemArray.GetValue(15).ToString() == "") { dr[15] = "-1"; } else { dr[15] = dr.ItemArray.GetValue(15).ToString(); } }

[Simas Joneliunas]

Why would it help?