Conditional Formatting Iif Statement - Help

I created an expression that changed the background fill of a cell based on other fields.  
I have 2 statements working and when I add the 3rd condition, I get errors and the report won't run.  

Here is the working expression:

=Iif(Fields!ReviewType.Value="ConditionsReview",Iif((Lookup(Fields!LoanPriority.Value,Fields!Loan_Priority.Value, Fields!Date.Value, "UnderwritingConditions"))>Fields!StatusDate.Value,"Salmon","PaleGreen",
Iif(Fields!ReviewType.Value="Rework Queue",Iif((Lookup(Fields!LoanPriority.Value,Fields!Loan_Priority.Value, Fields!Date.Value, "UnderwritingConditions"))>Fields!StatusDate.Value,"Salmon","PaleGreen"),""))

Here is the expression that does not work:
=Iif(Fields!ReviewType.Value="ConditionsReview",Iif((Lookup(Fields!LoanPriority.Value,Fields!Loan_Priority.Value, Fields!Date.Value, "UnderwritingConditions"))>Fields!StatusDate.Value,"Salmon","PaleGreen"),
Iif(Fields!ReviewType.Value="Rework Queue",Iif((Lookup(Fields!LoanPriority.Value,Fields!Loan_Priority.Value, Fields!Date.Value, "UnderwritingConditions"))>Fields!StatusDate.Value,"Salmon","PaleGreen"),
Iif(Fields!ReviewType.Value NOT IN("Rework Queue","ConditionsReview"),Iif(Fields!StatusDate.Value<Today(),"Salmon","PaleGreen"),"")))

Any help would be greatly appreciated!
Scott WilliamsData AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nico BontenbalCommented:
What error message are you getting. I don't think 'NOT IN' is supported by SSRS. So you need to rewrite that as:
Fields!ReviewType.Value <> "Rework Queue" and Fields!ReviewType.Value <> "ConditionsReview"


And in general:
When you are nesting iifs do it like this:
start with:
=iif(a=b,"x","y")
then change the true or false part into another iif like this:
=iif(a=b,"x",iif(c=d,"y","z"))
test every step. Not only by checking if the report runs, but also by checking if you are getting the correct result. Keep replacing a true or false part (in your example "Salmon" or "PaleGreen") with another iif(a=b,"Salmon","PaleGreen") statement. That way you should get the right result. If might also help to put the expression on multiple lines like this:
=iif(a=b,
   "x",
   iif(c=d,
      "y",
      "z"
   )
 )
That might make it easier to see where you made a mistake. And also make it easier to update the expression later.

I don't understand how the first expression can be correct. It has the part
,"Salmon","PaleGreen",
in it. That would mean there is another parameter after the true and false parts of the iif.

And it might help to describe the desired result. Looks like the iif statement is the same for ConditionsReview and Rework Queue. So you could make your expression a lot simpler by using
Fields!ReviewType.Value="ConditionsReview" or Fields!ReviewType.Value="Rework Queue"
for the condition. When you make a table with all the possible combinations and outcome it's easier to see what the most simple combination of criteria is.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott WilliamsData AnalystAuthor Commented:
Nicobo,

Thanks for your help.  I have broken down the three statements.  

=Iif(Fields!ReviewType.Value="ConditionsReview",Iif((Lookup(Fields!LoanPriority.Value,Fields!Loan_Priority.Value, Fields!Date.Value, "UnderwritingConditions"))>Fields!StatusDate.Value,"Salmon","PaleGreen"),""),

=Iif(Fields!ReviewType.Value="Rework Queue",Iif((Lookup(Fields!LoanPriority.Value,Fields!Loan_Priority.Value, Fields!Date.Value, "UnderwritingConditions"))>Fields!StatusDate.Value,"Salmon","PaleGreen"),"")

=Iif((Fields!ReviewType.Value <>"Rework Queue" and Fields!ReviewType.Value <>"ConditionsReview"),Iif(Fields!StatusDate.Value<Today(),"Salmon","PaleGreen"),"")


These three work.  How do I join them into one?
0
Scott WilliamsData AnalystAuthor Commented:
I see now.  The last false part is only needed once when all three statements are joined.

Here is my final working statement.
=Iif(Fields!ReviewType.Value="ConditionsReview",Iif((Lookup(Fields!LoanPriority.Value,Fields!Loan_Priority.Value, Fields!Date.Value, "UnderwritingConditions"))>Fields!StatusDate.Value,"Salmon","PaleGreen"),
Iif(Fields!ReviewType.Value="Rework Queue",Iif((Lookup(Fields!LoanPriority.Value,Fields!Loan_Priority.Value, Fields!Date.Value, "UnderwritingConditions"))>Fields!StatusDate.Value,"Salmon","PaleGreen"),
Iif((Fields!ReviewType.Value <>"Rework Queue" and Fields!ReviewType.Value <>"ConditionsReview"),Iif(Fields!StatusDate.Value<Today(),"Salmon","PaleGreen"),"")))
0
Scott WilliamsData AnalystAuthor Commented:
Thanks for setting me in the right direction!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.