Press "Enter" to skip to content

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



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


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
  Create another report definition (sub report) on worklist class.
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.

 Hope you enjoyed this post 🙂

  1. B Jitendra B Jitendra

    Hi Prem, Nice Article

    You did an excellent job………………..

    • Premkumar G Premkumar G

      Hi Jitendra,
      Thank you so much for your appreciation. 🙂

  2. Raj Raj

    A bit confuse between the Filter condition we select by clicking Configure for Sub-Report,

    What exactly will it filter ? Records from Sub Report or some other table ?

    How it works ?

    Can you elaborate please

  3. Sinddhuu Sinddhuu

    Thank You for the useful articles….

    • Premkumar G Premkumar G

      You are welcome, Sinddhuu. 🙂

  4. Vihari Vihari

    Hi Prem, this article was really good.

    Could you please post debugging tools like clipboard, PAL & Tracer, Live UI

    • Premkumar G Premkumar G

      Hello Vihari,
      Thank you so much. 🙂
      Yeah, I’ll post about it soon. 🙂 Stay tuned.

  5. Badri Badri

    Very useful article, it provides step-by-step understanding on RD.
    Thanks for sharing


    • Premkumar G Premkumar G

      Hello Badri,

      You are most welcome. 🙂 Glad to hear that you found my article useful. 🙂

      Premkumar G

  6. Suman Suman

    Good article Prem…
    The way of explanation is also good 🙂

    • Premkumar G Premkumar G

      Thank you so much for your appreciation, Suman. 🙂

  7. Yogesh Yogesh

    Very Informative. Please keep posting more…

    • Premkumar G Premkumar G

      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. 🙂

  8. Shweta Bhatia Shweta Bhatia

    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…

    • Premkumar G Premkumar G

      Thank you so much for your heartfelt appreciation, Shweta. 🙂
      Yeah I’ll post about those things soon. Stay tuned. 🙂

  9. Prasu Raya Prasu Raya

    now I got clear idea on DATA Access Tab in RD. Thank you so much PREM

    • Premkumar G Premkumar G

      Nice to hear that you understood about data access tab. 🙂
      You are welcome, Prasu. 🙂

  10. Raghu K Raghu K

    Very nice explanation of the RD rule with Joins, Sub Reports and Associations

    • Premkumar G Premkumar G

      Thank you so much, Raghu. 🙂

  11. Raghu K Raghu K

    Hi Prem,

    Thanks for your explanation of this rule. As well explain the difference between Joins and Association in Report Definition?

  12. Acharya Acharya

    Nice Explanation Prem,
    i have a question , what is the effect of retrieving blob properties in RD
    Please let me know

  13. sanjeeb sanjeeb

    I have a requirement where i have many implementation layers and all imp layers are inheriting from one FMWK layer, i have to create a report in FMWRK layer which will gimme all WO of different imp layers. how i can achieve this??

  14. Ravi Kumar Voturi Ravi Kumar Voturi

    Absolutely Fantastic..!!!

  15. Mounika Sunku Mounika Sunku

    very nice explanation

  16. Haritha Haritha

    Hi prem,

    How to implement with spaces i mean for adhar card there are spaces for 4 digits right how to implement that,please suggest.

  17. Ricky Ricky

    Hi Prem,
    I am new to Pega and would like to implement a report definition for a following requirement:

    Show counts for created requests, resolved request, pending requests and Group by Work type.

    How do I implement using Report Definition?

  18. Sachin Sachin

    Cool explanation on subReports
    plz provide explanation on parent -child case

  19. larry larry

    can you please show how to create RD for all active user currently login pega system?

  20. Keerthi Keerthi

    Hi Harsha
    Nice explanation
    I have a doubt will you please clarify it

    Diff between split join and split for each with simple example?

    What is embedded properties in report definition?

    In Decision rules

    I have 4 different inputs like

    So when am seeing the o/p…
    I will get 4 different acreens for that i/p and i will enter the values and execute it

    In these case which decision rule is used and why?

    • Keerthi Keerthi

      Spelled name incorrectly

      Prem plz explain that doubts

  21. Keerthi Vennamaneni Keerthi Vennamaneni

    Hi prem,

    I have posted the query but some where i missed it..
    Nice explanation point to point
    What is the difference between split join and split for each with an example

  22. Vinay Sarupuru Vinay Sarupuru

    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

  23. krish krish

    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…

  24. Haritha Haritha

    Hi prem,

    I have one doubt in subreports example from worklist table we can get operators also then why to go for operatortable.

  25. Prabha Prabha

    Instead of Subreport in the above mentioned example, can we use class join to Worklist table and use same filter condition in filter criteria? What is the exact difference between subreport and class join? Can u please clarify it?

  26. Yamini Yamini

    Hi Prem,

    You are doing a very helpful job. The way you explain the concepts are also very interesting( with smilies and quotes) 🙂
    I have never found any website which has such a clear explanation on pega concepts. Your posts are really very useful for many beginners like me.
    Hope you continue sharing all the pega knowledge you have.
    “We rise by lifting others”
    Thank you

  27. Sai Pratyusha Nittala Sai Pratyusha Nittala


    I have a doubt. Can we join these two tables, where one is in PEGA Schema and the other is in External Schema.

  28. Rama Rama

    HI Prem,

    you have explained subreports with one example right…as per my knowledge that we can acheive using joints also ..what is the use of subreports over joints..

  29. KSK KSK

    Hi Prem,

    Got the clarify on usage of Associations which was a confusion from log back for me.
    I want to ask you two things.
    1. Can any scenario/requirement involving multiple tables be solved by both Joins and Sub-reports or the purpose of the each option is for a specific business use?
    In short, can the selection be our choice or recommended depends o the scenario).
    2. What is the flow of execution of the Report Definition among Filter Conditions, Joins, Sub-reports etc.

    Don’t mind if i am not clear on my questions.


  30. Vinesh Raja Vinesh Raja

    Hi is Really very nice can u Show the Activity Call the Flow Action and then Flow Process..

Leave a Reply

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

error: Content is protected !!