SSRS conditional expression for background color

Using SQL Server 2008 R2, BIDS (VS 2008). Using a SSRS Expression for a BackgroundColor of a Textbox (in a Tablix).

Unable to get a expression working that evaluates two conditions:

=iif((DateDiff("D",Now(),Fields!FundingAuthorityDate.Value)>56),"Green",
       (iif(DateDiff("D",Now(),Fields!FundingAuthorityDate.Value)<=56,"Red",    
        iif(Fields!CurrentStage.Value = "Project Close", "White", "Red"))))

Funding Authority - Date field "dd/mm/yyyy"
Current Stage - Text field

What I am trying to do is to get a Red color if the remaining funding authority is less than 8 weeks AND if the current stage of the project is not in "Project Close", otherwise it's Green.

Please can you help advise the best way to write the expression.

Many thanks in advance for your help!
ihatelagAsked:
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.

ihatelagAuthor Commented:
Solution was to use a switch statement!

=Switch((DateDiff("D",Now(),Fields!FundingAuthorityDate.Value)>56),"Green",
                            Fields!CurrentStage.Value = "Project Close", "White",
         DateDiff("D",Now(),Fields!FundingAuthorityDate.Value)<=56, "Red", True, "Red" )
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
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

I think you are not clear with the conditions you want.
because it will only check the two conditions
DateDiff("D",Now(),Fields!FundingAuthorityDate.Value)>56
and
DateDiff("D",Now(),Fields!FundingAuthorityDate.Value)<=56

so it will never go in the other iif clause.

So please fix the condition

I have tested with this

=iif((DateDiff("D",Now(),ReportItems!Textbox4.Value)>56),"Green",
       (iif(DateDiff("D",Now(),ReportItems!Textbox4.Value)<=56,"Red",    
        iif(ReportItems!Textbox6.Value = "Project Close", "White", "Red"))))

and it gives red and green and does not go into other loop.

Please recheck and it will work with the iif clause also
0
ihatelagAuthor Commented:
Hi Vikas,

That iif statement doesn't work, it still turns the "Project Close" Red.

My switch statement seems to do it correctly.
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.