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 πŸ™‚

21 thoughts on “Report Definition – Class join, Declare Index join, Associations and Sub reports

    1. Hello Badri,

      You are most welcome. πŸ™‚ Glad to hear that you found my article useful. πŸ™‚

      Regards,
      Premkumar G

    1. Thank you so much for your appreciation, Yogesh. πŸ™‚
      Yeah I’ll keep on posting for sure. Since I’m having very hectic work in office, I’m unable to post very often like I used to do. But I’ll post more things soon. Stay tuned. πŸ™‚

  1. Best article that can explain joins, associations and sub reports. Am getting these concepts first time though have read them from multiple websites…

    Please can you help us with some article on parent child relationships between case types…

    1. Thank you so much for your heartfelt appreciation, Shweta. πŸ™‚
      Yeah I’ll post about those things soon. Stay tuned. πŸ™‚

  2. Hi PremKumar, i have a requirement of opening webcam on click of a button. Can you help me with that. Is there OOTB function to use webcam

  3. A is class 1, B is class 2
    Different combinations of joins possible and achievable in pega
    Below is the venn diagram representation of classes
    1. A intersection B—To achieve this we can use include matching rows
    2. A union A intersection B—To achieve this we have left outer join
    3. B union A intersection B—To achieve this we have right outer join
    4. A-B—To achieve this we don’t have direct option
    Use left outer join similar to step 2 and have a sub report which is another report formed using step 1 and use option do not include matching rows
    5.B-A—To achieve this we don’t have direct option
    Use Right outer join similar to step 3 and have a sub report which is another report formed using step 1 and use option do not include matching rows
    6.negation of a intersection b–To achieve this we can use sub reports and use do not include matching rows option
    7.A union B–To achieve this we don’t have direct option
    Full outer join in sql terminology
    Use step 1 to get a report i.e a intersection b and then use sub report which is formed using step 6 and use do not include matching rows.

    Prem could you please correct me if there are any easy ways to achieve this…

Leave a Reply

Your email address will not be published. Required fields are marked *