[SOLVED] SSRS-Problem in Comparing rows under a group and show it Matched or Unmatched

SSRS-Problem in Comparing rows under a group and show it Matched or Unmatched

I have data looks like below, and am grouping at Column1. I need to compare column2 with in Group and highlight if the values for column2 is same or different in Column3-

Column1 Column2 Column3
123     111 
123     111 
1234    2222    
1234    2222    
1234    3333

I am using expression in Column3 as below. As you can at last "Column1" I have mentioned it considering it will group and then compare.

=IIF(Fields!Column2.Value = Previous(Fields!Column2.Value), "Same", IIF (Fields!Column2.Value <> Previous(Fields!Column2.Value), "Different")), "Column1"

My expectation is as below, i.e. column3 should populate if all value of Column2 is same under a group (column1 grouping) then Column3 should populare same else different for all rows under a group

Column1 Column2 Column3
123      111    Same
123      111    Same
1234    2222    Different
1234    2222    Different
1234    3333    Different

Solution

You can use CountDistinct for this.

If we assume your Column1 'RowGroup' is called grpCol1 then the expression in column 3 would look something liek this.

= IIF(CountDistinct(Fields!Column2.Value,"grpCol1") >1 , "Different", "Same")

Basically this says.. Count how many different Column2 values there are within the the row group grpCol1

So for the first two rows in your example it would return 1 as there is only 1 distinct value, for the next three rows, it would return 2 as there are 2 distinct values.

Note: grpCol1 or whatever your row group is called must be within qoutes and is case sensitive.