Sub-reports – its Configuration, Usage & Execution

Please go through our previous post to understand the basics configurations in report definition & the usage of the class joins, association rule joins in the report definition rule.

Sub-reports is one of the data access option available in a report definition rule in Pega. From the OSP dictionary, we would define sub-reports in PEGA as a sub-query in SQL terminology.

What is sub-query in SQL?

• A subquery is a SQL query within a query.
• A subquery can be used to return data that will be used in the main query as a condition to further restrict the data to be retrieved or to set a column value in the main query.
• A subquery can return individual values or a list of records.
• The subquery must always be enclosed within parenthesis.
• When a query has a subquery, subquery gets executed first and then the main query execution happens.

Syntax

Below shown is the syntax to write a sub-query in SQL,

Usage

Subqueries can be used in the following ways inside a query,

• A subquery can be used to fetch a set of results from one or more tables and to filter records in the main query based on its results.

SELECT column-names FROM table-name1
WHERE value IN (SELECT column-name FROM table-name2 WHERE condition)

• A subquery can be used to assign columns values for each record in the main query.

SELECT column1 = (SELECT column-name FROM table-name WHERE condition), 
column-names FROM table-name WEHRE condition

Example

Let’s consider the below EXAM application with students and marks table in the same database with common field StudentID.

Now let’s write a query to identify all students who got more marks than that of the student who’s StudentID is ‘V002‘. We will use a subquery to retrieve the required data.

SELECT a.studentid, a.name, b.total_marks FROM student a, marks b
WHERE a.studentid = b.studentid AND b.total_marks >
(SELECT total_marks FROM marks WHERE studentid = 'V002');

The query when executed produces the below shown output

Execution

When we have a SQL query with a subquery, the subquery will be executed first followed by the main query execution. Hence in our above example, the subquery to fetch the total_marks of student V002 will be executed first followed by the main query to filter the results based on the result of the subquery.

Subquery execution,

SELECT total_marks FROM marks WHERE studentid = 'V002';

main query execution,

SELECT a.studentid, a.name, b.total_marks FROM student a, marks b WHERE a.studentid = b.studentid AND b.total_marks >80;

Let’s now get into actual discussion on sub-reports in the report definition rule.

What is Sub-report in Pega?

• Sub-report is similar to a subquery in SQL. Sub-reports are used to invoke a report definition rule from another report definition rule.
• A sub-report is a Report Definition report that provides some or all of its results to another Report Definition report that invokes it.
• Report definition rule invoked as a sub-report can be used to filter results from the main report definition (filter criteria) or can be used to display values in the main report from the sub-report results.

• Sub-report ensures maximum re-usability as the report definition rule invoked as a sub-report can also be executed stand-alone whenever required.
• As we do for class join and association rule, each of the sub-report needs a prefix to be mapped that will be used in the main report to reference values.

Configuration of sub-report in Pega

Each Sub-report should be configured properly before we can refer them to our main report. Configuring a sub-report can be broadly classified into 5 sections,

• Sub-report result count
• Sub-report column inclusion
• Sub-report join condition
• Sub-report column alias
• Sub-report parameters

Sub-report Result Count

This section of the sub-report is used to specify if the sub-report will result in one row or more than one row during its execution.

For example,

• When we have a sub-report to fetch the customer information based on the customer id, the sub-report will result with only one row and this section should be configured as 1 row.
• When we have a sub-report to fetch the address details of the customer based on the customer id, the sub-report can result in more than one row since a customer can have more than one address, and hence this section should be configured as More than 1 row.

Sub-report Column Inclusion

This section of the sub-report is used to mention where exactly the columns from the sub-report can be used in the main report.

Sub-report Join Condition

When we join information from multiple source, it’s obvious that we use a join condition to join information as per the business requirement.

We mention join condition when we use class joins, association rule joins, and similarly, when we refer a sub-report, a join condition is mandatory to be configured. Each Sub-reports can be configured using any one of the below joins,

Do not match rows (Full Outer join) is applicable only when the column inclusion is configured as Right-hand side of the filter conditions. This acts as a IN condition in the where clause.

Sub-report Column Alias

Column alias is a common terminology in SQL. Using column alias, we can refer a column using an alias name.

SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;

Here, ID is the alias name for CustomerID and Customer is the alias name for CustomerName.

In the same way, Pega assigns alias names for each of the columns in the sub-report if the column label has blank space in it.

Sub-report Parameters

Since sub-report is a stand-alone report definition it can be parameterized and parameter values can be defined using this section.

Parameters of the sub-report can be configured using constant values or property reference or using the parameters from the main report.

Guidelines to Prefer Sub-reports for Reporting

Many of us might have this question on why do we need sub-report when we have multiple joins (Class, Association, Declarative Index) in report definition rule to achieve reporting requirements.

Sub-report or sub-query can be used in the below scenarios,

• When we want to select rows from one data source using the IN operator match with another data source (Data Include).

SELECT * FROM Customers
WHERE CustomerID IN (SELECT distinct(CustomerID) FROM Claims);

Fetches the list of customer & their information who has applied for a claim.

• When we want to select rows from one data source using the NOT IN operator match with another data source (Data Exclude).

SELECT * FROM Customers
WHERE CustomerID NOT IN (SELECT distinct(CustomerID) FROM Claims);

Fetches the list of customer & their information who has never applied for a claim.

• When we want to select rows from one data source using the aggregate values from another data source. It can be any aggregate functions like SUM, AVG, COUNT, MIN, MAX, etc..

SELECT * FROM Customers
WHERE Net_Income > (SELECT avg(Net_Income) FROM Customers);

Fetches the list of customer & their information whose net income is greater than the average net income of all the customers in the database.

• When we want to select rows from one data source using the calculated values from another data source.

SELECT * FROM Customers WHERE (SELECT (sum(No_Of_Resolved_Claims)*100/(sum(No_Of_Claims)) FROM Claims) > 50;

Fetches the list of customer & their information who has more than 50% of successfully resolved claim cases

• Sub-reports can also be used to replace class joins provided if the sub-report used can be used as a stand-alone report definition to ensure re-usability.

Business Scenario

Let’s consider the scenario where OSP organization has a claims application, Customers can raise a claim anytime in the system. Each Claim request raised in Pega will have the customer identification number within it. Customer information namely Customer name, email will be available in Pega as a separate data type.

We are asked to create a report that shows the list of customers (Customer ID, Customer Name, Email, unresolved claim cases count) who has more than 5 unresolved claim requests in the system at any given time. Report should show the customers who have the maximum unresolved cases on top followed by the least.

Implementation

We have used v8.4 for implementing this POC

• Customer information (OSP-Data-Customer) are maintained in a separate data type in Pega.

• Claim request submitted by the customers is created as a work object in class (OSP-Claims-Work-Request).

• A stand-alone report definition is configured to fetch the count of unresolved claim cases for each customer.

• Now the above report definition is to be configured as a sub-report to display the customer information based on its results as mentioned in the requirement.

Execution

• Let’s now create 6 claim cases for Cus-1, 4 claim cases for Cus-2, and 7 claim cases for Cus-3 to test the above-created report definition.

• Then above report definition when executed should show the Cus-1 and Cus-3 since they have unresolved claim cases greater than 5.

• The generated SQL can be seen from the clipboard pyReportContentPage in the Standard thread.

Pointers to Remember

• Report Definitions are configurable versions of the Connect-SQL/SQL query in Pega that can be used to fetch records from the database.
• Sub-report is one of the data access options available in a report definition rule using which we can join information from multiple data sources.
• Sub-report columns can be used to display column values in the main report or as left-hand side (or) right-hand side of the filter conditions in the main report.
• Sub-reports can be used when we have requirements to display records based on the data inclusion (or) data exclusion (or) aggregate values (or) calculated values from another data source.
• As we configure class joins, association rule joins, declarative index joins, sub-report as well requires a join condition to be configured to pull the required information.
• Sub-report can be of only one level. (i.e) a report definition that has a sub-report cannot be mapped as a sub-report for another report (Since Pega is not a great reporting tool, it has its own limitations in handling complexities).
• Sub-report ensures maximum re-usability when compared to joins since the sub-report (report definition) can be invoked stand-alone.

We are now at the end of the post. We hope now the basics of configuring and using sub-reports to access data from multiple data source is clear.

Stay tuned for lot more posts on Reports & Case Management 😎