Another Sample For Conditionally Formatting text.

Article
05/31/2007

There are several exmaples out there showing how to conditionally format text. Some are hard to find so I thought I would post another since I had recently created it for another reason. By 'conditionally' I am refering to change the text color or back ground color based on some field value or comparison.

The basic (and quickest to implement) is covered in SQL Sever Books Online (BOL) at Adding Conditional Formatting. The method covered uses the iff() function to check a condiation and then pass back an appropriate color based. This is a great approach for simple logic. However if your comparision logic is complex or you need multiple checks then nesting iif() statements can get complicated.

The other approach, which is the same in inconcept, is to write a bit of custom code, in VB that, lives with the report and you then call the code in an expression, passing it whats needed (e.g. field values). A great advantage of this is that as you need to change your logic, your changing the one function, not multiple expressions in various places.

For a jump start on where to enter the code, please see this BOL topic. How to: Add Code to a Report (Report Designer)

I use the functions below to format all the cells in a table row. Don't forget you can select the whole row and enter an expression once and it will be applied to all of the cells in that row. As you can see there are two functions. One for changing the text color and one the background color.
There are various reasons (fields > 0) why I want the row red and shaded (gainsboro). I could likely write the code logic 'tighter' since they are result in red but I keep it seperate as I do want different colors now and then.

Called from and expression in the Backgroundcolor property.
=code.DetermineBackgroundColor(Fields!pri0.Value,Fields!pri1.Value,reportitems!textbox17.value,Parameters!totalallowednumber.Value)

Called from an expression in the color property.
=code.DetermineTextColor(Fields!pri0.Value,Fields!pri1.Value,reportitems!textbox17.value,Parameters!totaljailnumber.Value)

Function DetermineTextColor(pri0 as integer,pri1 as integer, mytotal as integer,allowedtotal as integer)
if pri0> 0
  return "RED"
else If pri1> 0
  Return "RED"
else if mytotal>=allowedtotal
  return "RED"
Else
  Return "BLACK"
End IF
End Function

Function DetermineBackgroundColor(pri0 as integer,pri1 as integer, mytotal as integer,allowedtotal as integer)
if pri0> 0
  return "gainsboro"
else If pri1> 0
  Return "gainsboro"
else if mytotal>=allowedtotal
  return "gainsboro"
End IF
End Function

For more information on calling the code from and expression, see the Books Online Topic Using Custom Code References in Expressions (Reporting Services)

Another Sample For Conditionally Formatting text.

Additional resources