Simple (?) IIF statement does not work properly
This seems like a simple IIF statement in SSRS but it doesn't work. Either part works if I only include one test (example 1 and 2), but neither works if I try and combine them (example 3 & 4). Essentially if the anonymous code is true or the restricted record is "restricted" then I want a blank otherwise I want the address line to print.
example 1 works = IIF (Fields!RestrictedRecord.V
example 2 works = IIF (Fields!IsAnonymous.Value = "False", Fields!Address.Value," ")
example 3 doesn't work = IIF (Fields!RestrictedRecord.V
example 4 doesn't work = IIF (Fields!RestrictedRecord.V
example 1 works = IIF (Fields!RestrictedRecord.V
example 2 works = IIF (Fields!IsAnonymous.Value = "False", Fields!Address.Value," ")
example 3 doesn't work = IIF (Fields!RestrictedRecord.V
example 4 doesn't work = IIF (Fields!RestrictedRecord.V
Shaun Kline
For example 3, change "or" to "and" to see if that gets the results you are looking for.
ASKER
It would be rare that it would meet both those cases... it really needs to be an or for it to work properly.
Maybe Null/Nothing is causing havoc, try this:
Maybe IsAnonymous is boolean, try this:
=IIF ( Trim(Fields!RestrictedRecord.Value) = "No"
OR Trim(Fields!IsAnonymous.Value) = "False"
, Fields!Address.Value
, " "
)Maybe IsAnonymous is boolean, try this:
=IIF ( Trim(Fields!RestrictedRecord.Value) = "No"
OR Fields!IsAnonymous.Value = False
, Fields!Address.Value
, " "
)
example 1 works = IIF (Fields!RestrictedRecord.Value = "No" , Fields!Address.Value," ")
example 2 works = IIF (Fields!IsAnonymous.Value = "False", Fields!Address.Value," ")
Try:
= IIF ((Fields!RestrictedRecord.
ASKER
Hamed, I did try your suggestion but it also didn't work. I know I've used "and" before in IIF statements and it works fine - assumed it would work the same way "or".
John, I also thought that the null might be an issue so I created a case statement that would put either No or Restricted in the field to eliminate that potential.
This should be so simple - drives me crazy that it won't work!!
John, I also thought that the null might be an issue so I created a case statement that would put either No or Restricted in the field to eliminate that potential.
This should be so simple - drives me crazy that it won't work!!
Would be nice to see the case-expression(s), your wording sounds like you are replacing NULL with No, or Restricted; but Restricted is not being used in the IIF-function, I see No, and False used in the equality-comparison.
ASKER
Here is the statement I'm using in the program itself:
CASE WHEN Res.ConstituentCodeShort IN ('RL1', 'RL2') THEN 'Restricted' ELSE 'No' END AS RestrictedRecord
I figured I could have created the IIF statement using either the No or the Restricted. It already was testing the anonymous for 'False' so I just tested the 'No' option.
CASE WHEN Res.ConstituentCodeShort IN ('RL1', 'RL2') THEN 'Restricted' ELSE 'No' END AS RestrictedRecord
I figured I could have created the IIF statement using either the No or the Restricted. It already was testing the anonymous for 'False' so I just tested the 'No' option.
If example 1 and 2 both work try:
I mean using the OR condition should works so that you wouldn't have to do the above, but give it a try to see if it makes a difference.
= IIF(Fields!RestrictedRecord.Value = "No", Fields!Address.Value, IIF(Fields!IsAnonymous.Value = "False", Fields!Address.Value," "))I mean using the OR condition should works so that you wouldn't have to do the above, but give it a try to see if it makes a difference.
The TextBox that holds your expression, is the hidden-property set to False? Perhaps Address is Null/Nothing, try the following:
=IIF ( Trim(Fields!RestrictedRecord.Value) = "No"
OR Trim(Fields!IsAnonymous.Value) = "False"
, IIF ( Trim(Fields!Address.Value) = ""
, "I expect address, but address is NULL/empty"
, "I expect an address here"
)
, "I expect blank here"
)ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.