reporting-servicesexpressionhighlightssrs-2016

Highlight Alternating Color Rows SSRS 2016


I have a table below with transaction data with names and amounts. I created a flag column called "Related" which identifies if the transactions are related or not based on if the “ParentID” column is matching. If so, the first will start with 1 and increment from there for any other related transactions with the same “ParentID”.

Name                  Amount             ParentID          Related
-------------------------------------------------------------------
  
Jake                  200                 800                NULL  
John                  500                 500                1
John                  600                 500                1
Paul                  800                 100                2  
Joe                   1000                100                2
Tom                   700                 600                3
Tom                   1500                600                3
Troy                  1000                800                5
Nick                  500                 800                5
Phil                  2000                900                NULL

What I am trying to do is within SSRS, highlight these matching rows based on if they are related by using an alternating color. So far I have tried this expression for highlighting:

=IIF(
Fields!Related.Value >= 1, "Yellow", "Transparent"
)

This highlights the related rows yellow as shown below, but if used in the sample table I have above, it will make it look like rows 2 to row 7 are related when they all aren't related to each other.

enter image description here

So my desired result is to alternate between two colors (Yellow and turquoise for example) to be able to differentiate between the related rows for example shown below. So the next in the sequence for another set of related transactions will be turquoise in this case shown below:

enter image description here

Is this possible through expressions in SSRS or is there perhaps a better way of indicating within SSRS rows that are related?


Solution

  • There's no easy way to do this in SSRS but you could use some VB to make it work.

    This saves the Related value in iRowValue. If it's NULL (0), then it returns WHITE. Otherwise, it checks the current Related value with the previous. If it doesn't match, the color changes.

    Private bOddRow As Boolean 
    Private iRowValue As INTEGER 
    
    Function AlternateColor2(ByVal OddColor As String, ByVal EvenColor As String, ByVal Toggle As INTEGER) As String 
    
        IF Toggle = 0 THEN 
            iRowValue = Toggle
            Return "White"
            GOTO END_FUNCTION
        END IF
            
      If Toggle <> iRowValue Then bOddRow = Not bOddRow 
    
      iRowValue = Toggle
      
      If bOddRow Then 
                    Return OddColor 
      Else 
                    Return EvenColor 
      End If 
    
    END_FUNCTION:
    
    End Function
    

    It would be used in the BackgroundColor expression with

    =CODE.AlternateColor2("AliceBlue", "Ivory", IIF(ISNOTHING(Fields!Related.Value), 0, Fields!Related.Value))