SSRS Report Builder 3.0: Conditional Formatting if value in multiple cells

Question

I'm asking this for a colleague who does not have an account. We both know little about Report Builder, so pardon any errors in terminology. He is using SSRS Report Builder 3.0 on Windows 7.

Report Builder Screenshot
He is trying to add a formula to the HUB Name ID cell which says that if Design Status, Material Status, etc. (all of the fields except HUB Name ID) say 'Complete' then make the Hub Name ID cell green.

He tried...:

=IIf(First(Fields!Design_Status.Value,           
"NodeAudit")First(Fields!Material_Status.Value, 
"NodeAudit")First(Fields!RF_Balance_Status.Value, 
"NodeAudit")First(Fields!FiberAudit_Status.Value, 
"NodeAudit")First(Fields!CMTS_Wiring_Status.Value, 
"NodeAudit")First(Fields!FieldAudit_Status.Value, 
"NodeAudit")=Complete,"Green","Red")

...but it did not work.

I saw another posting using a Switch (I've never heard of it): Ex:

=Switch(
  Fields!ID__to_Budget.Value < 0.30, "Red",
  Fields!ID__to_Budget.Value < 0.35, "Yellow",
  Fields!ID__to_Budget.Value >= 0.35, "Green"
)

Would something like this be helpful? The internet says that Switch "Specifies the value or expression that the item should be replaced with, if the condition is True." but I saw it in this forum's posting Similar Stackoverflow Posting where someone used Switch the above to make a background a certain color.

Question#1 If so, would you put this in the Hub Name ID cell?:

=Switch(
  Fields!ID__to_Design_Status.Value = "Complete", "Green",
  Fields!ID__to_Material_Status.Value = "Complete", "Green",
  Etc.....Etc...
)

Question# 2 Do you put it in the cell like you would Excel or as the other posting says, 'put in the background color properties of the cell the expression'?

Thank you in advance!

My colleague found the answer and I will post it in the original post for others to see, if needed. — sturdy267
@pnuts, The answer is in the post. "UPDATE 8/5/2016/ANSWER - my colleague figured it out..." Re: Excel, the title says SSRS Report Builder 3.0. Question#2 where it asks if I put the formula in Report Builder LIKE you do in Excel. Conditional formatting is not exclusive to Excel, and I read the tag. All Report Builder questions related to mine that I saw were posted on Stackoverflow, not Super User. You are 'strict' with a first time poster (I posted 3 questions around the same time) when new users need reputations for access to more features. Would you consider warning instead? — sturdy267
@pnuts, yes, it is hardly attractive. I see what you mean. I'll use the proper tag and a separate box for the answer. — sturdy267

sturdy267 sturdy267 · Accepted Answer · 2016-08-16T19:16:14

My colleague figured it out and he rewrote the formula below (he took out First and "NodeAudit" and added AND statements)

=IIf(Fields!Design_Status.Value AND Fields!Material_Status.Value AND  
Fields!RF_Balance_Status.Value AND Fields!FiberAudit_Status.Value AND 
Fields!CMTS_Wiring_Status.Value AND Fields!FieldAudit_Status.Value = 
"complete", "Green", "No color")

SSRS Report Builder 3.0: Conditional Formatting if value in multiple cells

1 Answers