How to remove duplicates in report definition in pega

A report that shows the same row several times looks broken, and users lose trust in the numbers. Two settings on the Query tab of a Report Definition control this: removing duplicates, and limiting the result to the top few rows.

Remove duplicate rows

Duplicates appear when a join produces more than one match, or when you select only some columns of a table and several records look the same. To fix it, open the Report Definition, go to the Query tab, and tick Remove duplicate rows. Pega adds a DISTINCT to the generated SQL, so the database returns each combination once.

Limit the number of rows

A Report Definition has a maximum number of rows it will return, which is 500 by default. That protects the system from huge results. If you need only the best few, do not raise or lower that maximum. Use the Top/Bottom rank section on the same Query tab.

A worked example: top 10 loans

Suppose the loan table has 500 open loans and the manager wants the 10 largest.

1. Open the Report Definition and go to the Query tab.
2. In the Top/Bottom rank section, choose Display top ranked, enter 10, and choose rows overall.
3. Set the ranking property to .LoanAmount.
4. Save and run. The report now shows only the 10 highest values, no matter how many records match.

When duplicates keep coming

• Check the joins on the Data Access tab. A one-to-many join repeats the parent row for every child.
• Select only the columns you need. An extra column with different values makes rows look unique to the database.
• Group by the key and use an aggregate such as Count or Max when you need one row per customer.

Interview tip

Mention both: tick Remove duplicate rows for duplicates, and use Top/Bottom rank for top N. Then explain the join issue that usually causes duplicates. Related: Join types in a Report Definition and the Report Definition label.