Report Definition – Class join, Declare Index join, Associations and Sub reports

Introduction

In the previous post, we saw a simple example to fetch the data from a single table.

For more details, please visit the below link

Report Definition in Pega

However there may be situations, where we need data from more than one table.

Can Report definition support this?  Yes it is 🙂

• In SQL, you might have come across the terms – INNER join, Left OUTER Join, Right OUTER Join.

Pega supports the same joins in the report definition rule.

• In facebook, you know a term ‘Friend of a Friend’. A friend of friend can see some private details this, that!!Here you need some common friend, to view few details.

Like the same, if you need details from 2 tables, then you need some common column – This is where the join takes place 🙂

What is Inner join, Left outer join, Right outer join?

I have two tables – States table and Countries table

• Here, you can see Country code is a friend of friend to both the tables.
• I need to fetch 4 columns – StateID, State Name, Country Code (States table) and Country Name (Countries table)

Left table – States; Right table – Countries

INNER Join – Also called as  simple join.

The below picture shows you the result of Inner join

It takes out rows which are common(intersection)  in both the tables by Country Code. Leaving out Country code 04 and 05

Left Outer Join – Includes all the rows in the left table and uses only data from the right table.

You can see all 5 entries are in the results table. Right tables are just used to get the data.

Right Outer Join – Includes all the rows in the right table and use only data in the left table.

You can see all 4 countries are listed.

Let’ Jump to our Pega.

Requirement – I got a requirement to display the sales details and the assignment details.

Here I need to join two tables – Work table and Assign-Worklist table.

Let’s see the records in both the table.

Sales table – You can see 4 entries.

Worklist table – You can see 2 entries

Here pzInsKey in work table and pxRefObjectKey in worklist table, both points to case Key and can be considered friend of friend. You can join using those columns

Step 1: Open the Report definition rule – Data access tab

Step 2: Fill out the class joins block.

Already the report definition is reporting on sales class (applies to).

Prefix – Specify a prefix for the joining class

Class name – Join class name.

Type –

a) Only include matching rows – Inner join

b) Include all rows in this class (Applies to class)  – Left outer join

c) Include all rows in joined class (worklist) – Right outer join

Edit conditions –

Column refers to – Sales work table column

Value refers to – Assign-Worklist table column

You can add one or more rows as filtering conditions. Prefix A is used to identify the joined class columns.

Note: You can also add more than one class joins in a report definition rule.

Step 3: Go to Query tab and add the A.pxAssignedOperatorID column

Step 4: Run the rule and test it.

You can see the inner join results J

Left Outer join

Step 1: update the class join to type

Step 2: save and run the rule.

• You can see all rows are included from the Sales class with data only copied from the joined class.
• There are no entries for S-1 and S-3, so no Operator ID.

Right Outer join

Step 1: Update type to ‘include all rows in joined class’

Step 2: save and run the rule.

You can see all rows (2) in the joined class are included and the other columns are just filled from the left table.

Based on your requirement, use it wisely 🙂

Declarative index Joins

Declarative index joins is similar to class join with the exception that instead of class name, we specify declare index name J

Associations

What is Association rule?

• It is very simple. If you find, you join two class instances frequently in report definition, you can create an association rule.
• Association rule explicitly defines the Join condition between two classes.
• Pega provides many standard association rules. You can also create one.

How to create a new association rule?

Step 1: Records ->Sysadmin -> New

Step 2: Specify the configuration points.

Note: Prefix should be same as the association rule name.

Step 3: Refresh the report definition

Still no association rule?????? Cool.

You need to refer a property in the query tab.

Step 4: Add a column using association prefix.

Step 5: Now check the data access tab.

You can see the association rule added.

Step 6: run the report and check the Inner JOIN results.

Association supports reusability in class joins 🙂

Sub reports

What is a Sub report?

• Sub report is a name we refer to report definition rule, when it is used in another report definition rule.
• To satisfy complex requirements and use the results of the existing report definition, we use sub reports.

Let me explain you with an example.

Requirement – I have a simple requirement, to get the list of operators who don’t have any assignments ( Assign-Worklist) entry.

My design choice is

1.    Create a main report definition rule on operators class
2.   Create another report definition (sub report) on worklist class.
3.   Use the worklist results to filter out the operators

Step 1: create a new report definition rule in ‘Data-Admin-Operator-ID’ class.

Note: I used organization to filter out default pega operators.

Step 2: run the report definition and check the operators list.

Step 3: Create another report definition rule in Assign-Worklist class (sub report)

Step 4: run the report definition to get the list of operators who have assignment in their worklist.

There are 2 assignments and both are in my name J

Note: This is the ideal candidate to use remove duplicate rows option.

Step 5: Now go to ‘GetOperatorsList’ report definition and add the sub report.

Step 6: configuration is the key part here. Click on configure option

You can select number of rows returned from the sub report – More than 1 row

Where will you use this sub report?

I selected right side of filter.

• You can specify filter conditions for the sub report results.
• You have an addition type option – Do not match rows.

Note: If you don’t use any join condition, you can choose – Do not match rows (I choose this)

Ignore filter conditions in sub report – We can also ignore the filter condition in sub reports.

Local names for sub-report columns

• You can specify some local names for sub report columns.
• You can also specify the parameters for the sub reports. If you use the same parameter value in both reports, you can check the Auto-populate option.

Step 7: Now add the filter condition in the main report.

Step 8: save and run the rule

My Name is gone 😀

Please follow the below link to know more about when and how to use sub reports.

https://pdn.pega.com/when-and-how-use-sub-reports-report-definition-reports

 Hope you enjoyed this post 🙂