Hide duplicate row SSRS 2008 R2

Question

Duplicated data is coming in my report because source table has duplicate data. Without creating group, I want to hide duplicate data writing expression. So what I did: I select table row and put a expression for hidden property of table row.

The expression was like =(Previous(Fields!ID.Value) = Fields!ID.Value) but it did not work ... Still duplicate data is showing. So tell me how to suppress duplicate rows in ssrs writing expression not by grouping.

xm1994 · Accepted Answer · 2012-03-18T15:29:44.297

17

You probably should try these options first:

Try to clean the duplicate data at the source.
Change your source query so the duplicates don't appear in the dataset. (e.g. SELECT DISTINCT)

If not, on the row's Visibility Hidden property you can use the Previous function:

=iif(Fields!YourField.Value = Previous(Fields!YourField.Value), True, False)

You would have to sort on the YourField column for it to work.

edited Mar 18 '12 at 15:29

answered Mar 16 '12 at 17:33

xm1994

473
3
11

i want to hide row if textbox has duplicate value in any row. how to do it? – Thomas Mar 17 '12 at 10:25
1

The Previous function returns null for the first row, so your IIF statement will always exclude a non-null first row. You should add a check for that like =IIF(Fields!YourField.Value = Previous(Fields!YourField.Value) AND Not IsNothing(Previous(Fields!YourField.Value)), True, False) – erosebe Jun 12 '17 at 18:20
@erosebe is correct, I was having the issue he described with the first row until I added his code. It would be beneficial if you added that to your answer. – bubbajake00 Sep 13 '18 at 20:10
In fact the Hidden Statement is already boolean, so you don't need the IIF and end up with an easy statement like this: = Fields!YourField.Value = Previous(Fields!YourField.Value) And Not IsNothing(Previous(Fields!YourField.Value)) . This will also function for duplicate column group values/headers. – Marc_Sei Jan 26 '23 at 14:09

score 12 · Answer 2 · answered Jul 03 '13 at 18:46

12

I was putting the expression above also until I started using the "Hide Duplicates" line in the properties pane. You basically just select the row, in the dropdown choose your Dataset and that's it. any duplicates will be hidden. Also if you just want to hide certain textboxes duplicates you can do the same as i stated earlier except click on the textbox and not the row. Just another alternative, i'm aware you said using an expression.

answered Jul 03 '13 at 18:46

Rodney Maspoch

955
3
13
19

This is exactly what I was looking for. I have multiple nested groups with a column group on the lowest level and I was having issues getting previous vs current and row_number to work. Picked the parent group as the value of the "Hide Duplicates" property of my details group and it only shows the value on the first line of the group. – liebs19 Jan 28 '14 at 19:48
+1 this is exactly what I needed. This question is a top hit on Google and your answer was much more practical for my needs. – Mr. C Feb 09 '16 at 20:35

score 8 · Answer 3 · answered Mar 02 '16 at 08:43

8

You can do it using expression or "Hide Duplicates" options from cell or row properties.

Expressions :

=IIF(Fields!YourField.Value = Previous(Fields!YourField.Value), True, False)

Hide Duplicates Steps:

Select row or cell
Click on F4 key on your Keyboard
Look for "Hide Duplicates"
Choose your DataSet from the dropdownlist

Done, I hope that helps

answered Mar 02 '16 at 08:43

user2120121

665
1
6
15

that work for me...can we merge duplicate value cell in matrix – Jack Apr 14 '17 at 10:18

score 1 · Answer 4 · answered Apr 14 '16 at 10:07

As an alternative option, you can do it by setting row groups. In a report I was trying to create, the Hide Duplicates property wouldn't behave correctly because of using the same dataset multiple times in a list container.

All you need to do is set the row group properties for the default row group (rightclick the grey row header, and go to Row Group then Group Properties), and add Group expressions on the General tab. Add as many as you need for each field. It's like the Remove Duplicates tool in Microsoft Excel

score 0 · Answer 5 · answered Jul 13 '16 at 21:02

Sometimes the Hide Duplicates option does apply to the report content. If you add a =Sum(Field!Field_Name.Value) sum around field in the cell, it suppresses the copy from previous record. Of course, strictly speaking this is a solution where the incoming data set has NULL rows for the cells with the issue.

score 0 · Answer 6 · answered Apr 25 '19 at 03:58

Since the using of Previous function in SSRS compare to the only record previous to it, thus it might cause the duplicate of records still shown if the repeated records not next to each other.

Use the sorting on each table you apply the Previous function, it should resolve the "non next to each other" duplicate records as well.

Hide duplicate row SSRS 2008 R2

6 Answers6

Linked