SSRS Iif always true when false part contains expression

Question

I've got a report outputting to an Excel file, with some fields in a database being empty.

I'm trying to filter these null values out using the following Iif expression in a cell:

=Iif(
    IsNothing(Fields!START_DATETIME.Value),
    0,
    Fields!START_DATETIME.Value
)

This doesn't work, I get "#VALUE" in the cells where the data was null, and the time value of the cell where there was data.

If I use the same expression without an expression as the third parameter:

=Iif(
    IsNothing(Fields!START_DATETIME.Value),
    0,
    1
)

I get 1s where there is a date in the table and 0s where there is nothing.

I'm sorry if this is a duplicate, I couldn't find anything that worked for me...

Edit: I'm using VisualStudio Professional 2013

Update

What I've got for each entry in the database is: a datetime for authorization time, another for start time and another for stopped time. There is always an entry for authorization time, but not always for start and stopped.

I've split the date and time out from the various datetimes into columns like so:

Authorized date
Authorized time
Start time
Stop time

The idea is that if no start and stop time are present, just use the authorized time

The following expressions are put in the Start time column. Sorry, I don't have enough rep to post images or more than 2 links. I've put program output in this album:

http://imgur.com/a/zJSAY

Doing:

=Iif(Fields!START_DATETIME.Value is Nothing,
    DateAdd(DateInterval.Day,693594,TimeValue(Fields!AUTH_DATETIME.Value)),
    DateAdd(DateInterval.Day,693594,TimeValue(Fields!AUTH_DATETIME.Value))
)

Outputs the authorized time, so that works fine.

However, doing:

=Iif(Fields!START_DATETIME.Value is Nothing,
    DateAdd(DateInterval.Day,693594,TimeValue(Fields!AUTH_DATETIME.Value)),
    DateAdd(DateInterval.Day,693594,TimeValue(Fields!START_DATETIME.Value))
)

Outputs the start time where there is an entry, and #VALUE! (null or some such) when there isn't anything.

And doing:

=Iif(Fields!START_DATETIME.Value is Nothing,
    DateAdd(DateInterval.Day,693594,TimeValue(Fields!START_DATETIME.Value)),
    DateAdd(DateInterval.Day,693594,TimeValue(Fields!AUTH_DATETIME.Value))
)

Outputs the authorized time where there is an entry in start time, and #VALUE! when there isn't anything.

I can't figure this out. I understand (1) and (3), but what I need is for (2) to display the correct start time while there is one, and the authorized time when there isn't.

Update 2

It turns out that Iif() evaluates each conditions even if it is not selected, and any error encountered is passed on regardless. See comments to Why isn't my iif statement evaluating true when the condition is correct?

To fix this I did:

=DateAdd(
    DateInterval.Day,
    693594,
    TimeValue(
        Iif(
            Fields!START_DATETIME.Value is Nothing,
            Fields!AUTH_DATETIME.Value,
            Fields!START_DATETIME.Value
        )
    )
)

This seems to work for me, and should have been the common sense way to do this in the first place...

Thanks again for your help guys.

Why do you want "0" in a column with DATETIMEs? What would it represent? Don't you need either NULL, empty string, or some default datetime? — Jeroen
If you want to filter them out as in "don't show them", can't you do that in your query? — Stijn
@Jeroen Anything except #VALUE! would be nice, that was just to try it, I'd probably leave it blank. — user111395
@Tino I can try that, but tbh I haven't touched queries outside of the report designer yet. I'm wondering why the Iif wouldn't work. Thanks for you guys' input. — user111395

Jeroen Jeroen · Accepted Answer · 2014-11-10T10:49:54

Excel will guess the type of a column based on the first few cells. If there's typical DATETIME values in those, it'll guess the column is a date column, and it will error with "#VALUE! on values that don't match the type, such as "0".

You should ask yourself what you want to display in Excel for NULL values:

If you just want an empty cell, don't use an expression at all. Just Fields!START_DATETIME.Value will render a NULL value as an empty cell.
If you want a "baseline" datetime, make sure to use that instead of "0". Easiest is probably in your query with something like ISNULL(START_DATETIME, GETDATE()).
If you really want a "0", make sure it appears in one of the first few cells by ordering. Excel will see the "0" and not set the column type to date/time.

SSRS Iif always true when false part contains expression

1 Answers