SSRS expression field help - date field returns #Error

Question

Hi, I'm having an issue with an SSRS expression date field.

This date field is currently returning NULL values which is expected, but I'm trying to create an expression for it and I can't figure out why it keeps returning #Error.

Here is what I am trying to achieve logically:

If the datefield is NULL, then return Nothing,
If (parameter value is X OR parameter value is Y), then format(datefield,"MM/dd/yyyy"),
Otherwise just return default datefield. (default date field is formatted differently)

Here is my SSRS expression, which returns the #Error:

=IIF(Fields!DateField.Value is nothing, ""
,IIF( (Parameters!Name.Value = "X" OR Parameters!Name.Value = "Y"), Format(Fields!DateField.Value, "MM/dd/yyyy"),
Fields!DateField.Value

I've tried many different things now and most times I get #Error.

Answer 1

Hi @LG111
Is this your complete expression? It looks like you're missing some closing brackets? I added two closing parentheses to the end of your expression and it worked.


Best regards,
Aniya

Answer 2

You are close, but there is a small mistake in your SSRS expression. The issue is that the IIF() function does not work the way you are using it. When the first expression (Fields!DateField.Value is nothing) is true, it returns "" which is a string and the second expression is not executed, so the Format function is trying to format a string value and it is giving the error.

You can use the IIF() function correctly as follows:

=IIF(Fields!DateField.Value is nothing, Nothing ,IIF( (Parameters!Name.Value = "X" OR Parameters!Name.Value = "Y"), Format(Fields!DateField.Value, "MM/dd/yyyy"), Fields!DateField.Value))

This way, if the first expression is true, it returns 'Nothing' which is the correct value and the second expression is not executed.

Please make sure that you are using the correct parameter name, and also, you might want to check the date format of the datefield, it should be a date data type and not a string data type.

You can also use a SWITCH function instead of IIF function:

=SWITCH( Fields!DateField.Value is nothing, Nothing,

(Parameters!Name.Value = "X" OR Parameters!Name.Value = "Y"),

Format(Fields!DateField.Value, "MM/dd/yyyy"),

true, Fields!DateField.Value )

This way, you can avoid the #Error when the first expression is true.