Simple (?) IIF statement does not work properly

cindyfiller
cindyfiller used Ask the Experts™
on
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.Value = "No" ,  Fields!Address.Value," ")
example 2 works        = IIF (Fields!IsAnonymous.Value = "False",  Fields!Address.Value," ")

example 3 doesn't work        = IIF (Fields!RestrictedRecord.Value = "No" or Fields!IsAnonymous.Value = "False",  Fields!Address.Value," ")
example 4 doesn't work        = IIF (Fields!RestrictedRecord.Value = "No",  Fields!Address.Value, IIF (Fields!IsAnonymous.Value = "False",  Fields!Address.Value," "))
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Shaun KlineLead Software Engineer

Commented:
For example 3, change "or" to "and" to see if that gets the results you are looking for.
cindyfillerDirector of IT

Author

Commented:
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:
=IIF (	Trim(Fields!RestrictedRecord.Value) = "No" 
	OR	Trim(Fields!IsAnonymous.Value) = "False" 
	,	Fields!Address.Value 
	,	" " 
	)

Open in new window


Maybe IsAnonymous is boolean, try this:
=IIF (	Trim(Fields!RestrictedRecord.Value) = "No" 
	OR	Fields!IsAnonymous.Value = False 
	,	Fields!Address.Value 
	,	" " 
	)

Open in new window

PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Hamed NasrRetired IT Professional

Commented:
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.Value = "No") OR (Fields!IsAnonymous.Value = "False") ,  Fields!Address.Value," ")
cindyfillerDirector of IT

Author

Commented:
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!!
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.
cindyfillerDirector of IT

Author

Commented:
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.
Top Expert 2008

Commented:
If example 1 and 2 both work try:

= IIF(Fields!RestrictedRecord.Value = "No", Fields!Address.Value, IIF(Fields!IsAnonymous.Value = "False", Fields!Address.Value," "))

Open in new window


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" 
	)

Open in new window

Director of IT
Commented:
John, it works fine if I only do one option at a time.  It is only when I add the OR that it fails.  I've done quite a few IIF's with AND and those work fine, but the OR just doesn't.  Rather than waste any more time I went back to the program and did some code there so I could test for just the one field in the IIF statement.  That worked fine.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial