none
Using OR in IIF function in Reporting Services

    Question

  • I would like to use OR statement within the IIF function in an expression box in reporting services. The goal is to use a different background color based on the true-false returned by IIF. The statement i used is like this :

    IIF((fields!field1.value > field2.value or len(field3.value)>0 and field4.value > today),"lightcyan","lightyellow")

    It seems that this is not parsed succesfully because i see only lightcyan as background even when the condition isn't matched.
    Is there a way for the IIF to parse OR - AND between numerous fields? Ofcourse in SQL statement that works and my question is how do i transfer the same logic in an expression in Reporting Services.

    Please advise, thanks.
    Thursday, October 01, 2009 12:48 PM

Answers

  • Hello ChrisK_DBA,

    in common the OR logic works, also in IIF function. Example with AdventureWorks - Address

    = IIF( (Fields!AddressID.Value

    MOD 2) = 0 OR Today() > Today(), "LightCyan", "LightYellow")

    The second expression always returns FALSE, the first returns TRUE for every even AddressID; so I get an alternating background color.

    Are you sure your condition works right? May you use additional brakets in you expression like

    = IIF((  ( fields!field1.value > field2.value or len(field3.value)>0 ) and field4.value > today),"lightcyan","lightyellow")

    if its fits for your logic.



    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Thursday, October 01, 2009 1:27 PM

All replies

  • Hello ChrisK_DBA,

    in common the OR logic works, also in IIF function. Example with AdventureWorks - Address

    = IIF( (Fields!AddressID.Value

    MOD 2) = 0 OR Today() > Today(), "LightCyan", "LightYellow")

    The second expression always returns FALSE, the first returns TRUE for every even AddressID; so I get an alternating background color.

    Are you sure your condition works right? May you use additional brakets in you expression like

    = IIF((  ( fields!field1.value > field2.value or len(field3.value)>0 ) and field4.value > today),"lightcyan","lightyellow")

    if its fits for your logic.



    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Thursday, October 01, 2009 1:27 PM
  • Thanks for the response, the condition i want to return TRUE is this :


    (

    (field1.value > field2.value)

    or

    (len(field3.value)>0 AND field4.value > today)

    )

    and that isn't working in my IIF statement.
    Thursday, October 01, 2009 1:39 PM
  • I don't have your data for validating your logic.

    May you solve it step by step.

    First only use
     = IIF( (field1.value > field2.value), ...
    in the expression and review the result. Are all fields in backcolor LightCyan.
    Next use only
     = IIF( (len(field3.value)>0 AND field4.value > today)
    Same here; are there some LightYellow?

    Now combine the result by OR logic in mind; what's the result?


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Thursday, October 01, 2009 7:11 PM
  • Don't you need an extra  bracket based on the logic you specified ? :

    IIF((fields!field1.value > field2.value or (len(field3.value)>0 and field4.value > today)),"lightcyan","lightyellow")

    Otherwise the logical operators are evaluated from left to right .

    If it still not working , then perhaps field3.value , field4.value are not filled correctly .
    Thursday, October 01, 2009 8:07 PM