Be judicious when adding data objects. If you find that your report has unexpected duplicate values or empty rows, the cause is most likely that you have a one-to-many join to an object that you are not using.
For example, say we would like to build a report that displays a list of the professors at a school, and the students that they advise. This would require that we add the Categories "Professors" and "Students", which have a one-to-many relationship as one professor may advise several students. Adding fields from both of these categories to a report will result in duplicate data from the "Professors" category, as a single professor may be paired with multiple students.
Report Design:
Output:
As you can see, the Professor's first names are repeated each time they are paired with a student.
There are a few ways to handle situations like this.
- Utilize Group Headers
- Apply Suppress Duplicates to the Categories
- Apply Suppress Duplicates to the cell
1. Utilize Group Headers
Group Header sections can be used to display data from the "one" side of a "one-to-many" relationship. For example, we can apply a Group Header to the above report to display the Professor name once at the top of the list of Student names.
NoteYou must have a sort applied to a field in order to group by it.
2. Apply "Suppress Duplicates" to a Category
When adding Categories to a report, a column displays the option to "Suppress Duplicates" for that Category. This option will suppress any repeated records from that Category (for example, if two records with the same Key value is displayed, it will be suppressed).
NoteThis will cause the entire record to be suppressed; not only the duplicate values.
As you can see, the repeated Professor names have been suppressed.
3. Apply "Suppress Duplicates" to a Cell
There is a "Suppress Duplicates" icon within the Advanced Report Toolbar, which can be enabled/disabled for any cell on the report. Enabling this feature will cause the report to check whether the above value is the same as the current value. If it is, the value will be suppressed.
NoteThis setting will only apply to the selected cell.
As you can see, since the "Suppress Duplicates" option was only enabled for the "Professor First Name" cell, the duplicates still displayed for the "Professor Last Name" cell.
If you are still seeing duplicates on your report after trying the above options, it is likely a result of Cartesian Processing.