none
IIF In SSRS With Opposite Of = For A List

    Question

  • I am trying to build a Expression using IIF with the opposite of this in a calculated field:

    =IIF((Fields!DEPARTMENT_CODE.Value = "22700") OR (Fields!DEPARTMENT_CODE.Value = "22800") OR (Fields!DEPARTMENT_CODE.Value = "22900") OR (Fields!DEPARTMENT_CODE.Value = "23000") OR (Fields!DEPARTMENT_CODE.Value = "2350") OR (Fields!DEPARTMENT_CODE.Value = "23800") OR (Fields!DEPARTMENT_CODE.Value = "23900") OR (Fields!DEPARTMENT_CODE.Value = "24000") OR (Fields!DEPARTMENT_CODE.Value = "24100") OR (Fields!DEPARTMENT_CODE.Value = "24200") OR (Fields!DEPARTMENT_CODE.Value = "24300"),1,0)

    I have tried <>, AND, AND NOT, and XOR which all seem to yield the same results.  Any suggestions would be greatly appreciated!

    Thanks in advance! Brett

    Monday, January 20, 2014 11:42 PM

Answers

  • The opposite of your expression can be accomplished by simply switching the order of the 0 and the 1, that is the nature of a Boolean:

    =IIF((Fields!DEPARTMENT_CODE.Value = "22700") OR (Fields!DEPARTMENT_CODE.Value = "22800") OR (Fields!DEPARTMENT_CODE.Value = "22900") OR (Fields!DEPARTMENT_CODE.Value = "23000") OR (Fields!DEPARTMENT_CODE.Value = "2350") OR (Fields!DEPARTMENT_CODE.Value = "23800") OR (Fields!DEPARTMENT_CODE.Value = "23900") OR (Fields!DEPARTMENT_CODE.Value = "24000") OR (Fields!DEPARTMENT_CODE.Value = "24100") OR (Fields!DEPARTMENT_CODE.Value = "24200") OR (Fields!DEPARTMENT_CODE.Value = "24300"),0,1)

    This is literally the opposite of your posted expression.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Tuesday, January 21, 2014 9:04 PM
  • Hi Brett,

    To sum it up, The IIF() statement has the following format:
    =IIF( Expression to evaluate, what-to-do when the expression is true, what-to-do when the expression is false )
    Parameter1: It should be a Boolean expression.
    Paremeter2: This value will return when Expression is true.
    Paremeter3: This value will return when Expression is false.

    So in the Calculated Field 1, we can use the expression below to achieve your requirement:
    =IIF((Fields!DEPARTMENT_CODE.Value = "22700") OR (Fields!DEPARTMENT_CODE.Value = "22800") OR (Fields!DEPARTMENT_CODE.Value = "22900") OR (Fields!DEPARTMENT_CODE.Value = "23000") OR (Fields!DEPARTMENT_CODE.Value = "23500") OR (Fields!DEPARTMENT_CODE.Value = "23800") OR (Fields!DEPARTMENT_CODE.Value = "23900") OR (Fields!DEPARTMENT_CODE.Value = "24000") OR (Fields!DEPARTMENT_CODE.Value = "24100") OR (Fields!DEPARTMENT_CODE.Value = "24200") OR (Fields!DEPARTMENT_CODE.Value = "24300"),1,0)

    In the Calculated Field 2, we can use the expression below to achieve your requirement:
    =IIF((Fields!DEPARTMENT_CODE.Value = "22700") OR (Fields!DEPARTMENT_CODE.Value = "22800") OR (Fields!DEPARTMENT_CODE.Value = "22900") OR (Fields!DEPARTMENT_CODE.Value = "23000") OR (Fields!DEPARTMENT_CODE.Value = "23500") OR (Fields!DEPARTMENT_CODE.Value = "23800") OR (Fields!DEPARTMENT_CODE.Value = "23900") OR (Fields!DEPARTMENT_CODE.Value = "24000") OR (Fields!DEPARTMENT_CODE.Value = "24100") OR (Fields!DEPARTMENT_CODE.Value = "24200") OR (Fields!DEPARTMENT_CODE.Value = "24300"),0,1)

    If you have any other questions, please feel free to let me know.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    • Marked as answer by coskier Wednesday, January 22, 2014 4:39 PM
    Wednesday, January 22, 2014 3:39 AM

All replies

  • Hi,

    So you are checking for the dept_code, if any of the value exists in code you'll be returning 1 else 0 right?

    Can you please post what actual result is required?

    Thanks, Madhu

    Tuesday, January 21, 2014 9:51 AM
  • Hi Brett,

    If I understand correctly, you want to write an expression with the opposite of the posted IIF expression in a calculated field. If in this scenario, we can try to use the expression below to achieve your requirement:

    =IIF((trim(Fields!DEPARTMENT_CODE.Value) = "22700") OR (trim(Fields!DEPARTMENT_CODE.Value) = "22800") OR (trim(Fields!DEPARTMENT_CODE.Value) = "22900") OR (trim(Fields!DEPARTMENT_CODE.Value) = "23000") OR (trim(Fields!DEPARTMENT_CODE.Value) = "2350") OR (trim(Fields!DEPARTMENT_CODE.Value) = "23800") OR (trim(Fields!DEPARTMENT_CODE.Value) = "23900") OR (trim(Fields!DEPARTMENT_CODE.Value) = "24000") OR (trim(Fields!DEPARTMENT_CODE.Value)= "24100") OR (trim(Fields!DEPARTMENT_CODE.Value) = "24200") OR (trim(Fields!DEPARTMENT_CODE.Value) = "24300"),0,1)

    If there are any misunderstanding, please elaborate the issue for further investigation.

    Thanks,
    Katherine Xiong

    Katherine Xiong
    TechNet Community Support

    Tuesday, January 21, 2014 1:00 PM
  • Yes correct, if it was not one of the values above for dept_code it would return a 1.
    Tuesday, January 21, 2014 2:13 PM
  • Hi Katherine:

    I tried your suggestion and it did not work.  I am trying to create two calculated fields using Expression Builder in SSRS and I would like them to yield a number 1 using the following criteria:

    Calculated Field 1

    select * from claim_table where department_code IN("22700","22800","22900","23000","23500","23800","23900","24000","24100","24200","24300")

    Calculated Field 2

    select * from claim_table where department_code NOT IN("22700","22800","22900","23000","23500","23800","23900","24000","24100","24200","24300")

    Any suggestions or recommendations would greatly be appreciated! :)

    Thanks, Brett

    Tuesday, January 21, 2014 2:30 PM
  • The opposite of your expression can be accomplished by simply switching the order of the 0 and the 1, that is the nature of a Boolean:

    =IIF((Fields!DEPARTMENT_CODE.Value = "22700") OR (Fields!DEPARTMENT_CODE.Value = "22800") OR (Fields!DEPARTMENT_CODE.Value = "22900") OR (Fields!DEPARTMENT_CODE.Value = "23000") OR (Fields!DEPARTMENT_CODE.Value = "2350") OR (Fields!DEPARTMENT_CODE.Value = "23800") OR (Fields!DEPARTMENT_CODE.Value = "23900") OR (Fields!DEPARTMENT_CODE.Value = "24000") OR (Fields!DEPARTMENT_CODE.Value = "24100") OR (Fields!DEPARTMENT_CODE.Value = "24200") OR (Fields!DEPARTMENT_CODE.Value = "24300"),0,1)

    This is literally the opposite of your posted expression.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Tuesday, January 21, 2014 9:04 PM
  • Hi Brett,

    To sum it up, The IIF() statement has the following format:
    =IIF( Expression to evaluate, what-to-do when the expression is true, what-to-do when the expression is false )
    Parameter1: It should be a Boolean expression.
    Paremeter2: This value will return when Expression is true.
    Paremeter3: This value will return when Expression is false.

    So in the Calculated Field 1, we can use the expression below to achieve your requirement:
    =IIF((Fields!DEPARTMENT_CODE.Value = "22700") OR (Fields!DEPARTMENT_CODE.Value = "22800") OR (Fields!DEPARTMENT_CODE.Value = "22900") OR (Fields!DEPARTMENT_CODE.Value = "23000") OR (Fields!DEPARTMENT_CODE.Value = "23500") OR (Fields!DEPARTMENT_CODE.Value = "23800") OR (Fields!DEPARTMENT_CODE.Value = "23900") OR (Fields!DEPARTMENT_CODE.Value = "24000") OR (Fields!DEPARTMENT_CODE.Value = "24100") OR (Fields!DEPARTMENT_CODE.Value = "24200") OR (Fields!DEPARTMENT_CODE.Value = "24300"),1,0)

    In the Calculated Field 2, we can use the expression below to achieve your requirement:
    =IIF((Fields!DEPARTMENT_CODE.Value = "22700") OR (Fields!DEPARTMENT_CODE.Value = "22800") OR (Fields!DEPARTMENT_CODE.Value = "22900") OR (Fields!DEPARTMENT_CODE.Value = "23000") OR (Fields!DEPARTMENT_CODE.Value = "23500") OR (Fields!DEPARTMENT_CODE.Value = "23800") OR (Fields!DEPARTMENT_CODE.Value = "23900") OR (Fields!DEPARTMENT_CODE.Value = "24000") OR (Fields!DEPARTMENT_CODE.Value = "24100") OR (Fields!DEPARTMENT_CODE.Value = "24200") OR (Fields!DEPARTMENT_CODE.Value = "24300"),0,1)

    If you have any other questions, please feel free to let me know.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    • Marked as answer by coskier Wednesday, January 22, 2014 4:39 PM
    Wednesday, January 22, 2014 3:39 AM