IIF Statement Help Needed

Question

I have the following IIF statement in an expression for a report (a tablix cell).  It is basically interrogating a database field looking for a date.  The problem is, when there is no entry in the database for this date in a particular record, the IIF statement provides an error in the results and throws off the whole report.  I have logic in play that IIF(IsDate...) then proceed to the true section....otherwise return false.  For some reason, when there is no entry for the record, it does not give the false return if I still refer to this field in the true section.  It isn't ignoring the true part for some reason, even though it shouldn't even be looking through it after failing the initial IIF condition. 

Hear is the code:

=IIF(IsDate(Fields!Logoff.Value), (IIF(((Format(Fields!Logon.Value, "dd")) < (Format(Fields!Logoff.Value, "dd"))), (IIF(((Format(Fields!Logon.Value, "HH") = 0) OR ((0 <= (Format(Fields!Logoff.Value, "HH") + 24)) AND (0 >= Format(Fields!Logon.Value, "HH")))), 1, 0)), (IIF(((Format(Fields!Logon.Value, "HH") = 0) OR ((0 <= Format(Fields!Logoff.Value, "HH")) AND (0 >= Format(Fields!Logon.Value, "HH")))), 1, 0)))), 0)

Simplified:

=IIF(IsDate(Fields!Logoff.Value), "true", "false")

The true section:

(IIF(((Format(Fields!Logon.Value, "dd")) < (Format(Fields!Logoff.Value, "dd"))), (IIF(((Format(Fields!Logon.Value, "HH") = 0) OR ((0 <= (Format(Fields!Logoff.Value, "HH") + 24)) AND (0 >= Format(Fields!Logon.Value, "HH")))), 1, 0)), (IIF(((Format(Fields!Logon.Value, "HH") = 0) OR ((0 <= Format(Fields!Logoff.Value, "HH")) AND (0 >= Format(Fields!Logon.Value, "HH")))), 1, 0))))

If Fields!Logoff.Value is empty, it errors out.  Even though it should proceed to the False section of the block.  If I remove references to Logoff in the true section (change them to Logon or something), it works fine with the IsDate evaluation and does return false.

Why is it still trying to evaluate the true section when it should be false?

Hopefully this makes sense.

Thanks for any help!

Chris

Answer 1

See the thing is the LogOff could probably be a field that allows "null". So what happens when null comes in IsDate Function tries to cast it as a date but since its a null value ends up in nullreferenceexception to avoid this issue , this how you need to frame your expression :

iif(IsNothing(Fields!LogOff.value),"False",(iif(IsDate(Fields!LogOff.value),"true","false")))

This way you do a check for Null first , if its a null then return false else evaluate IsDate if that passes then return or a false ..

Hope this helps

Cheers

Anand

Answer 2

Thank you for the quick reply, your solution makes sense to me, but still does not work in my report. 

Here is what I entered:

=IIF(IsNothing(Fields!Logoff.Value), 0, (IIF(IsDate(Fields!Logoff.Value), (IIF(((Format(Fields!Logon.Value, "dd")) < (Format(Fields!Logoff.Value, "dd"))), (IIF(((Format(Fields!Logon.Value, "HH") = 0) OR ((0 <= (Format(Fields!Logoff.Value, "HH") + 24)) AND (0 >= Format(Fields!Logon.Value, "HH")))), 1, 0)), (IIF(((Format(Fields!Logon.Value, "HH") = 0) OR ((0 <= Format(Fields!Logoff.Value, "HH")) AND (0 >= Format(Fields!Logon.Value, "HH")))), 1, 0)))), 0)))

If I just enter the following:

=IIF(IsNothing(Fields!Logoff.Value), 0, 1)

It does return 0, but for some reason, still evaluates the other section when it exists in place of "1".

Thanks again,
Chris

Answer 3

Chris, 

  This the way IIF works, taking your previous example  : 

 Case 1 : If Fields!Logoff.Value equals to "null" then
 IIF(IsNothing(Fields!Logoff.Value), 0, 1) returns 0 as an integer

 Case 2 : If Fields!Logoff.Value not equals to "null" [that is when Fields!LogOff.value contains somevalue but not "null"] then 
 IIF(IsNothing(Fields!Logoff.Value), 0, 1) return 1 as an integer
 

Syntatically :

IIf( <Expression As Boolean>, TruePart as Object, FalsePart as Object)

Expression

Required. Boolean. The expression you want to evaluate.

TruePart

Required. Object. Returned if Expression evaluates to True.

FalsePart

Required. Object. Returned if Expression evaluates to False.

I guess you need to inspect the value that comes in each and every row , if you feel its doing something that you don't want it to.

Cheers
Anand

Answer 4

Here is what it is doing when Fields!Logoff.Value is nothing (no entry):

=IIF(IsNothing(Fields!Logoff.Value), 0, (IIF(IsDate(Fields!Logoff.Value), (IIF(((Format(Fields!Logon.Value, "dd")) < (Format(Fields!Logoff.Value, "dd"))), (IIF(((Format(Fields!Logon.Value, "HH") = 0) OR ((0 <= (Format(Fields!Logoff.Value, "HH") + 24)) AND (0 >= Format(Fields!Logon.Value, "HH")))), 1, 0)), (IIF(((Format(Fields!Logon.Value, "HH") = 0) OR ((0 <= Format(Fields!Logoff.Value, "HH")) AND (0 >= Format(Fields!Logon.Value, "HH")))), 1, 0)))), 0)))

Returns "#Error".

=IIF(IsNothing(Fields!Logoff.Value), 0, 1)

Returns "0".

If I use first example and replace "Logoff" with "Logon" for every reference in the IsDate TRUE section:

=IIF(IsNothing(Fields!Logoff.Value), 0, (IIF(IsDate(Fields!Logoff.Value), (IIF(((Format(Fields!Logon.Value, "dd")) < (Format(Fields!Logon.Value, "dd"))), (IIF(((Format(Fields!Logon.Value, "HH") = 0) OR ((0 <= (Format(Fields!Logon.Value, "HH") + 24)) AND (0 >= Format(Fields!Logon.Value, "HH")))), 1, 0)), (IIF(((Format(Fields!Logon.Value, "HH") = 0) OR ((0 <= Format(Fields!Logon.Value, "HH")) AND (0 >= Format(Fields!Logon.Value, "HH")))), 1, 0)))), 0)))

It will return "0" as expected from the IsNothing evaluation (I made IsDate and IsNothing different once and it is the IsNothing that is returning the "0").  For whatever reason, leaving "Logoff" in the IsDate IIF section does not make it fail, only if in the TRUE part of that section.

Hope this helps explain better.

Thanks again for your responses!

Chris

Answer 5

try this:

IIf((Fields!Logoff.Value Is DBNull.Value), 0, 1)

---

Balaji Baskar

Answer 6

Thanks for the suggestion, but it does not like the syntax for "DBNull.Value"

Answer 7

What I would do is - if it is null then replace it with some date, example - '01/01/1900'. Like -

=iif(format(cdate(iif(fields!Logoff.value="","01/01/1900",fields!Logoff.value)),"dd/MM/yyyy")="01/01/1900",0,1)

OR

=iif(format(cdate(iif(fields!Logoff.value is null ,"01/01/1900",fields!Logoff.value)),"dd/MM/yyyy")="01/01/1900",0,1)


to add to that - if you are bringing back the results in a SQL query then you can replace null with a blank ("") and then the expression should work fine.

---------------
Ashish

Answer 8

This reminds me of the issue of checking to make sure you aren't dividing by 0.  So if you were going to divide by 0 this will fail:

=iif(Field!A.Value = 0, Nothing, Field!B.Value/Field!A.Value)

This is how you have to get it to work:

=iif(Field!A.Value = 0 or isnothing(Field!A.Value), Nothing, Field!B.Value / iif(Field!A.Value = 0 or isnothing(Field!A.Value), 1, Field!A.Value))

So where I am going with this is, you have to always make sure that your expression will always work even if you don't expect that statement to run.  I don't understand why SSRS is developed this way (I see this as a bug), but it is something that we all have to live with.

I know this doesn't directly answer your question, but it should hopefully let you rethink what you are trying to do.

Hope this helps,
--Josh

---

Josh George LiveLogic, LLC

Answer 9

Hi Chris,

Just add to Josh's response, in this case, we can use the following expression in the True and False section to solve the issue:
IIF(IsDate(Fields!Logon.Value),Format(Fields!Logon.Value, "dd"), nothing)

That is becuase of the SQL Server Reporting Services will evaluate all the arguments before the report is processing. That is used to improve the performance.

Thanks,
Jin Chen

---

Jin Chen - MSFT