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