1

I am new to using SSRS. I have looked around Stack Overflow for answers to this question but haven't found it.

I am producing a simple report but wish it to be filtered to not display the result if it is a duplicate set of results.

Basic report Basic report

A lot of forum posts mentioned using code similar to the following to be set in the visibility of the Table Properties.

=IIf(Previous(Fields!Country.Value) = Fields!Country.Value, True, False)

However this didn't work. I then tried going to the properties box of the country to hide duplicates and this kind of worked. It removed duplicates if they were only in the next row and unfortunately it just made the cell blank.

Attempt at hiding the duplicate rows Attempt at hiding the duplicate rows

What I wanted is to just display the list of unique values where the reference and the country are never repeated. This way I will get a list of countries for my reference.

Many thanks in advance to anyone who is able to help a new learner like myself.

I am adding in the edit suggested by Alejandro below,

It sort of works, the suggestion did remove the blank rows (which became blank when I applied the HideDuplicates from the properties of the country. The trouble is the report is only hiding duplicates if they come from the previous line. I tried grouping etc but it did not work.

aduguid
  • 3,099
  • 6
  • 18
  • 37
Pete
  • 23
  • 2
  • 8

2 Answers2

2

Try selecting and setting the hidden property to the whole row.

enter image description here

UPDATE:

Using the expression works only if the Country column is sorted, so repeated countries will be in contiguous rows.

Go to tablix property and add a sort by Country property. Don't use HideDuplicates property but use =IIf(Fields!Country.Value = Previous(Fields!Country.Value), True, False) expression in the whole row.

It should work.

Let me know if you need further help.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • Alejandro, Let me thank you for taking the time to help me. I tried your suggestion and it did seem to work a little but not quite fully there. What happened is that the rows did vanish if they were blank which is just fantastic. Somehow though I am still getting a few duplicates. Setting the Country property of HideDuplicates seems to only hide a duplicate if it comes from the previous row. Filtering the table by country didn't really seem to work. This is probably because the code I am using in the Row Visibility is: '=IIf(Previous(Fields!Country.Value) = Fields!Country.Value, True, False)' – Pete Jul 14 '16 at 15:12
2

It sounds like you just need to group the dataset, no?

If you open the row group properties, set it to group on Reference and Country.

enter image description here

Here are two tables... the first has no grouping and so there are repeats. The second is grouping by Reference and Country.

enter image description here

Bostaevski
  • 307
  • 2
  • 11
  • Thanks Bostaevski, That worked perfectly, I had not seen that before. Quite simple really. As with Alejandro I really appreciate you making the effort to assist a beginner. - Many thanks, Pete – Pete Jul 15 '16 at 12:04