vbavisual-studio-2012reporting-servicesreportbuilder3.0ssrs-expression

Conditional Formatting a textbox

I'm using ReportBuilder

Below is an expression to change the background color based on a value in a cell. I would like to add a new condition to the existing expression to change textbox22 if it has 'N/A' in it to Light Green 

=iif(isnothing(reportitems!Textbox22.value),Nothing, switch(reportitems!Textbox22.value >1 and reportitems!Textbox22.value <=50,"#fd6767",reportitems!Textbox22.value >=51 and reportitems!Textbox22.value <=79,"#f8d3a9",reportitems!Textbox22.value >=80 and reportitems!Textbox22.value <=100,"LightGreen",reportitems!Textbox22.value = 0, "#fd6767"))

This is the design view

enter image description here

This is the expression in the cell which brings back "N/A" if there is no value for that cell.

=iif(isnothing(lookup(Fields!StatID.value,Fields!statID.value,Fields!pass_rate.value,"Res2nd")),"N/A",lookup(Fields!StatID.value,Fields!statID.value,Fields!pass_rate.value,"Res2nd"))

This is the output

enter image description here

I've tried the below but this makes all the cells white

 =switch(reportitems!Textbox22.value >1 and reportitems!Textbox22.value <=50,"#fd6767",reportitems!Textbox22.value >=51 and reportitems!Textbox22.value <=79,"#f8d3a9",reportitems!Textbox22.value >=80 and reportitems!Textbox22.value <=100,"LightGreen",reportitems!Textbox22.value = 0, "#fd6767",reportitems!Textbox22.value = "N/A","LightGreen")
Solution

  • Managed to get this working eventually by using the below

    =iif(reportitems!Textbox22.value >=0 and reportitems!Textbox22.value <=50 ,"#f1b1b1",iif(reportitems!Textbox22.value >50 and reportitems!Textbox22.value <=79,"#f8d3a9","LightGreen"))