James James - 1 year ago 147
SQL Question

SSRS comparing Current Row to Previous Row

I have the following Expression in a field in SSRS:

=iif(Fields!Score.Value > Previous(Fields!Score.Value),"Greater","Less Than")


I have the following Scores in my scenario across 4 rows:

3
3
4
5


It results in:

3 - Greater
3 - Less Than
4 - Greater
5 - Greater


The issue is with the first row always being Greater. It should be blank because there isn't a previous row to compare against. I would expect the results to be as follows:

3 -
3 - Same
4 - Greater
5 - Greater


How could I change the above formula to produce the above result?

The above is an example of only one row group. There are then further row groups with different Scores, so the different results of Same/Greater/Less Than need to work across the different row groups.

Answer Source

Try following:

=IIF(RowNumber(NOTHING)=1,"",IIF(Fields!Score.Value = Previous(Fields!Score.Value),"Same",IIF(Fields!Score.Value > Previous(Fields!Score.Value),"Greater","Less Than")))

modified. try now.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download