What is Pega Sub Report in Report Definition?

A sub report is a small report that lives inside another report. The outer report uses its result as a condition, for example "customers who have at least one overdue loan" or "customers who have no loan at all". In SQL terms, a sub report becomes a subquery.

Why use one?

Some questions cannot be answered by joining two classes without also changing the rows you see. A join repeats parent rows, and it cannot easily express "has none". A sub report answers a yes or no question about each row while leaving the main report's rows and columns alone.

How to set it up

1. Open the main Report Definition.
2. On the Data Access tab, add a sub report and choose the class it reads, for example Loans. Give it its own filter, for example Status = Overdue.
3. Link it to the main report with a matching property, for example Loan.CustomerID = Customer.CustomerID.
4. On the Query tab, add a filter condition on the main report that uses the sub report, with an operator such as is in, is not in or exists.
5. Run the report and check the SQL in the Tracer to confirm the subquery.

Menu names vary a little between Pega versions, but the idea is always the same: define the inner report, then use it in a condition of the outer one.

A worked example

A bank wants a list of customers who have no loans, so it can send them an offer.

• Main report: Customers with columns Name and Email.
• Sub report: Loans, returning the CustomerID of every loan.
• Condition: CustomerID is not in the sub report.

The generated SQL looks like this:

SELECT Name, Email
FROM   Customer c
WHERE  c.CustomerID NOT IN (SELECT l.CustomerID FROM Loan l)

Each customer appears once, and only those without a loan are listed.

Sub report or join?

Need	Use
Columns from both classes on screen	Class join
Filter by whether related rows exist, or do not	Sub report
Avoid repeated parent rows	Sub report

Tips

• Index the columns used to link the two reports, or the subquery will be slow on large tables.
• Keep the sub report's filter tight so the inner query returns few rows.
• Read the SQL in the Tracer whenever a report is slow.

Related: Join types in a Report Definition and the Report Definition label.