reporting services - IsNothing error

Question

I have the following error which I can't understand from my expression.

=IIF(IsNothing(Fields!month.Value),"6mr",LEFT(Format(DateValue(MonthName(Right(Fields!month.Value, 2))
 + "," + 
 Left(Fields!month.Value,4)), "Y"),3) + " '" + RIGHT(Format(DateValue(MonthName(Right(Fields!month.Value, 2))
 + "," + 
 Left(Fields!month.Value,4)), "Y"),2))

This works perfectly without the IsNothing element.

I have tested the IsNothing element and that works in the following case:

=IIF(IsNothing(Fields!month.Value),"6mr",0).

Help to correct much appreciated.

Answer 1

You are experiencing this "issue" because Reporting Services evaluates both sides of the Iif.

This is not really an issue, it is by design, see this technet article for more details:

• SSRS - IIF function evaluates both True & False

Yes , By design the SSRS evaluates both True & False statements even though the condition is not satisfied and through error if something is not right.

Now, to get your expression working, you have to consider that Fields!month.Value could be nothing even in the false part of your expression.

So in your expression, you could just replace

Fields!month.Value

by

Iif(IsNothing(Fields!month.Value), "1", Fields!month.Value)

Here is an expression with this fix:

=Iif(IsNothing(Fields!month.Value),"6mr",Left(Format(DateValue(MonthName(Right(Iif(IsNothing(Fields!month.Value), "1", Fields!month.Value), 2))
 + "," + 
 Left(Iif(IsNothing(Fields!month.Value), "1", Fields!month.Value),4)), "Y"),3) + " '" + Right(Format(DateValue(MonthName(Right(Iif(IsNothing(Fields!month.Value), "1", Fields!month.Value), 2))
 + "," + 
 Left(Iif(IsNothing(Fields!month.Value), "1", Fields!month.Value),4)), "Y"),2))

You could also create a custom function or use a variable if you want to reduce the expression length.