Another Sample For Conditionally Formatting text.

  • Article

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) 

Comments

  • Anonymous
    August 09, 2007
    Here's an example of how you can do it using the hex value of the color, which can give you a more continuous shading.  In this example, my input range is about 0 - 100, but can vary significantly from one report to the next, lower numbers are less significant so they are closer to white, higher are closer to black. '--Contains information on ratio of largest number of connections to 255 Private shared colorReference As Integer '--Pass this function the maximum value in your dataset so that you it will be the darkest Public Function SetReference(ByVal largestInput As Integer) As Integer colorReference = (255 / largestInput) Return colorReference End Function '--Returns a color based on the number of connections Public Function GetColor(ByVal colorIndex As Integer) As String Dim Color As String = "Gray" colorIndex = (colorIndex * colorReference) If (colorIndex > 255) colorIndex = 255 End If colorIndex = (255 - colorIndex) If (colorIndex <= 15) Color = ("#0" & Hex(colorIndex) & "0" & Hex(colorIndex) & "0" & Hex(colorIndex)) Else Color = ("#" & Hex(colorIndex) & Hex(colorIndex) & Hex(colorIndex)) End If Return Color End Function

  • Anonymous
    September 11, 2007
    Is it possible to add inline styles to text so one word is BOLD (for example)?

  • Anonymous
    February 26, 2008
    sorry but i dont where ask this question i have report with parameter and he can have a null in parameter ther is null word can i change it to another word like all or any thing else

  • Anonymous
    October 02, 2008
    I have been following advise from various site on conditional formatting, but I have been having problems when it comes to more "complex" formatting. More the fact that I am not getting the code correct any help? Public Function GetColor(ByVal dblActual As Double) As String         Select Case CInt(dblActual - 0.00)       Case > 0.30 Return "Blue" Else if between 0.30 AND -0.30 Return "Green" Case < -0.30 Return "Red" End Select     End Function