Different color for sorting in SSRS

Question

I have a customer who would like some color formatting in an SSRS report. I cannot utilize grouping due to the customer requirements. I have the report sorting by Main Tank and would like the sorts to alternate in color. So it would have several rows of Tank A all be grey then several rows of Tank B all be white. I'm using this currently but it only works on the first row of each sort

=iif( Fields!Tank.Value=previous(Fields!Tank.Value), "Transparent", "Red" )

Any help would be great.

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-02-03T21:40:46

You can use custom code to get the required logic. Using a custom function in your code that returns the color based on the the Tank value.

Go to Report menu, Report Properties / Code tab and in the text area put this code:

Dim prevColor As String = "Transparent"
Public Function GetColor(ByVal flag As Integer) As String
  If flag = 1 Then
    If prevColor = "Transparent" Then
      prevColor = "Gray"
    Else
      prevColor = "Transparent"
    End If
  End If
  Return prevColor
End Function

Now in the cell background-color property use this expression:

=Code.GetColor(iif(Fields!Tank.Value=previous(Fields!Tank.Value),0,1))

It will color the background as below:

UPDATE: The above logic colors correctly only the first, third, fifth columns and so on. It is caused by multiple calls to the GetColor function and overwritting of the global variable prevColor.

I managed to solve that issue by passing the row number to the function. This is the updated function:

Dim prevColor As String = "Transparent"
Dim rowNum As Integer = 0
Public Function GetColor(ByVal flag As Integer, ByVal nRow As Integer) As String
  If flag = 1 And rowNum <> nRow Then
    If prevColor = "Transparent" Then
      prevColor = "Gray"
    Else
      prevColor = "Transparent"
    End If
  End If
  rowNum = nRow
  Return prevColor
End Function

Note the function receives a new argument so the expression for calling has changed:

=Code.GetColor(iif(Fields!Tank.Value=previous(Fields!Tank.Value),0,1),
RowNumber("DataSet3"))

Replace "DataSet3" by the actual name of your dataset.

It should generate the following tablix:

Let me know if this helps.

Different color for sorting in SSRS

2 Answers