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
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 🙂
Hi Prem, Nice Article
You did an excellent job………………..
Hi Jitendra,
Thank you so much for your appreciation. 🙂
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
Thank You for the useful articles….
You are welcome, Sinddhuu. 🙂
Hi Prem, this article was really good.
Could you please post debugging tools like clipboard, PAL & Tracer, Live UI
Hello Vihari,
Thank you so much. 🙂
Yeah, I’ll post about it soon. 🙂 Stay tuned.
Hi,
Very useful article, it provides step-by-step understanding on RD.
Thanks for sharing
Regards
Hello Badri,
You are most welcome. 🙂 Glad to hear that you found my article useful. 🙂
Regards,
Premkumar G
Good article Prem…
The way of explanation is also good 🙂
Thank you so much for your appreciation, Suman. 🙂
Very Informative. Please keep posting more…
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. 🙂
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…
Thank you so much for your heartfelt appreciation, Shweta. 🙂
Yeah I’ll post about those things soon. Stay tuned. 🙂
now I got clear idea on DATA Access Tab in RD. Thank you so much PREM
Nice to hear that you understood about data access tab. 🙂
You are welcome, Prasu. 🙂
Very nice explanation of the RD rule with Joins, Sub Reports and Associations
Thank you so much, Raghu. 🙂
Hi Prem,
Thanks for your explanation of this rule. As well explain the difference between Joins and Association in Report Definition?
Nice Explanation Prem,
i have a question , what is the effect of retrieving blob properties in RD
Please let me know
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??
Absolutely Fantastic..!!!
very nice explanation
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.
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?
Cool explanation on subReports
plz provide explanation on parent -child case
can you please show how to create RD for all active user currently login pega system?
Thanks
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
A.approval
B.reject
C.save
D.delete
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?
Sorry…
Spelled name incorrectly
Prem plz explain that doubts
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
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
Hi Vinay,
Quite interesting requirement. I found your post in PDN too. I might have suggested the same ‘adding the code within auto generated section’. I would like to try it out.
Here is the link for others to follow up with PDN
https://pdn.pega.com/community/product-support/question/open-webcam-section-and-take-snapshot-webcam-and-link-it-work
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…
Hi prem,
I have one doubt in subreports example from worklist table we can get operators also then why to go for operatortable.
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?
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
Hi!
I have a doubt. Can we join these two tables, where one is in PEGA Schema and the other is in External Schema.
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..
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.
Thanks.
Hi is Really very nice can u Show the Activity Call the Flow Action and then Flow Process..