CODEWITHSUNDEEP
reporting-servicesssrs-grouping
Janny
Janny

Reputation: 25

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

Upvotes: 1

Views: 628

Answers (2)

Alan Schofield
Alan Schofield

Reputation: 21683

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.

Upvotes: 1

SuperSimmer 44
SuperSimmer 44

Reputation: 999

A simpler way would be:

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

Upvotes: 0

Related Questions