SSRS compare rows by text

Question

I have table like this:

+---------------------------------+
| Group1                          | 
+-------+--------+----------------+ 
| Item1 | Value1 | Compare        |
+-------+--------+----------------+
| Item2 | Value2 | Compare        |
+-------+--------+----------------+
| Item3 | Value3 | Do not compare |
+-------+--------+----------------+
| Group2                          | 
+-------+--------+----------------+ 
| Item1 | Value1 | Compare        |
+-------+--------+----------------+
| Item2 | Value2 | Compare        |
+-------+--------+----------------+
| Item3 | Value3 | Do not compare |
+-------+--------+----------------+

In a table like this i can have multiple groups with multiple items. I need to compare only items, where "Compare" is written. For example, i need to change the color of Value1 in Group2, if that value is grater thant Value1 in Group1. How can i do that? If there would be only one line to compare in each group, it would be easy, but i don't know what to do when there are multiple lines.

Are you should to compare only neighboring rows, or which logic of comparing? — Roman Badiornyi
I should compare Value1 from Group2 with Value1 from Group1. Then Value1 from Group3 with Value1 from Group1 and so on. — JNM
It's not impossible in SSRS, you can't iterate through rows, can you say by which column you are grouping? Maybe I'll halp to write SQL which help to solve this problem — Roman Badiornyi
But i know the name of Group1. So i can SUM all values from whole table in Group1. That would be ok, if i could tell ssrs to take only item with the same name as the item it is working with at the moment. — JNM

Chris Latta Chris Latta · Accepted Answer · 2013-02-22T11:01:39

So you need to compare new groups with an old group that you know the name of? This query will give you any new group and compare any items named the same where they have been flagged to be compared with the original group, Group1, and give you the old and new values:

SELECT New.GroupName AS NewGroup, New.ItemName AS NewItem, 
    Old.Value AS OldValue, New.Value AS NewValue
FROM MyTable New
INNER JOIN MyTable Old ON 
    Old.GroupName = 'Group1' AND 
    Old.ItemName = New.ItemName
    Old.CompareField = 'Compare' AND
    New.CompareField = 'Compare'

SSRS compare rows by text

1 Answers