Highlight Alternating Color Rows SSRS 2016

Question

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.

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:

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

score 1 · Answer 1 · answered Aug 03 '21 at 20:57

You could use a SWITCH function to evaluate the Related row and return the color:

=SWITCH(IsNothing(Fields!Related.Value), "White", 
    Fields!Related.Value MOD 2 = 0, "Ivory", 
    Fields!Related.Value MOD 2 = 1, "AliceBlue", 
    1 = 1, NOTHING)

The MOD function will return the remainder of dividing the Related number by 2 so it will alternate between the yellow and blue. You could also increase the MOD number to have more color result lines (i.e. Fields!Related.Value MOD 5 = {0 - 4} ) but then you have might end up with too much color.

Also note that while Visual Studio will give "Transparent" in the GUI, it is not a valid value. The message will show in the ERROR tab and result in the color being NOTHING. Use NOTHING or White instead.

I prefer more subtle shades rather than the bright yellow.

Will this work if there's a sequence where the related column has two different pairs of related rows and both has an odd number value for the Related column? Due to the way I may order the results for the report, the related column won't always be in chronological order, I forgot to mention this. — jwalls91, Aug 03 '21 at 21:03
I've edited the sample table in my question to reflect that the Related column values are not always in chronological order, I had forgotten to add this detail. As shown, after the set of related rows with value of 3 in Related column, following that is the value 5 for the next pair. — jwalls91, Aug 03 '21 at 21:12

Hannover Fist · Accepted Answer · 2021-08-05T16:20:09.387

1

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))

edited Aug 05 '21 at 16:20

answered Aug 04 '21 at 22:37

Hannover Fist

10,393
1
18
39

This sounds like it would work, I am not familiar with VB though, is this something that I'd implement within my existing TSQL code for the report? Or is the VB to be stored somewhere else to reference? – jwalls91 Aug 05 '21 at 14:29
@jwalls91 - The VB code goes in the SSRS report properties' CODE tab. Right click in the design window but out of the report and click Report Properties or in the Properties window for **Report**. Paste the VB code in the Custom Code window. – Hannover Fist Aug 05 '21 at 15:51
I found it, after adding the custom code there it's giving a message "There is an error on line 11 of custom code. Expression is not an array or a method, and cannot have an argument list." – jwalls91 Aug 05 '21 at 16:16
@jwalls91 - ugh, I fixed the issue. I was reusing some code but missed the bOddRow array. – Hannover Fist Aug 05 '21 at 16:22
This seems to work great for what I'm looking for, thanks for explaining everything! I had never used VB custom code within an SSRS report before. – jwalls91 Aug 05 '21 at 16:36

Highlight Alternating Color Rows SSRS 2016

2 Answers2