Skip to main content

         This documentation site is for previous versions. Visit our new documentation site for current releases.      

This content has been archived and is no longer being updated.

Links may not function; however, this content may be relevant to outdated versions of the product.

How to chain together multiple joins in a report definition rule

Updated on September 20, 2019


When assembling data for a report, it is often necessary to to include information drawn from multiple, related data tables. To build such reports In V5.x, it is often necessary to write a custom "getContent" activity with complex SQL statements that establish the relationships ("joins") between the information in tables A, B, and C, and then to state what data to provide from the three joined tables.

With V6.1 SP2, in many cases it is no longer necessary to write complex SQL statements for querying the database. Instead, you can chain together multiple joins in a report definition rule.

The example for this article relates to a Project Management Framework installation that tracks product development. The development effort involves several teams using the Scrum methodology (for those who do not know Scrum, team tasks are written up as "stories", and stories not yet being worked on are stored in a "backlog"; stories being worked on are part of a "sprint", typically a two-week development effort). The manager wants a report that will show how many stories are in the backlog for each team, and the total number of stories in all backlogs for a specific product development effort.

The data is in a number of classes; each class is associated with a table in the PegaRULES database.

  • From PegaProjMgmt-WorkGroup, the report gets information about the users stories and backlogs they are in.
  • From PegaProjMgmt-Work-Project the report gets information about the development project.
  • From PegaProjMgmt-Work-Product the report gets information about products, and
  • From PegaProjMgmt-Work-Product-Version the report gets details about product versions.
  • The Link-TaskGroup class associates user stories to backlogs or sprints.

The report receives a statement about which product and version to search for, and assembles all the user stories that are in backlogs of projects associated with that product and version.

table structure

The diagram indicates that user stories are related to backlogs through a separate class (Link-TaskGroup), though both are instances of the PegaProjMgt-WorkGroup class. Backlogs are related to projects, which are in turn related to products and versions.

The lengthy SQL statement that would be needed to accomplish this complex calculation and five-way join looks like this:

SELECT COUNT (*) AS "pySummaryCount(1)" , "Product".pyLabel AS "pyLabel" , "ProductVersion".pyLabel AS "pyLabel" , "Backlog".pyLabel AS "pyLabel"
FROM {CLASS: PegaProjMgmt-Work-Group } "PC0" INNER JOIN {CLASS:Link-TaskGroup} "JoinTable" ON ( ( "PC0".PZINSKEY = "JoinTable".pxLinkedRefTo AND "PC0".PXOBJCLASS = {pyReportContentPage.pyPreparedValues(4) }
   AND "JoinTable".PXOBJCLASS = {pyReportContentPage.pyPreparedValues(5) } ) ) INNER JOIN
    {CLASS:PegaProjMgmt-Work-Group} "Backlog" ON ( ( "JoinTable".PXLINKEDREFFROM = "Backlog".pzInsKey
   AND "Backlog".PXOBJCLASS = {pyReportContentPage.pyPreparedValues(6) }
   AND "JoinTable".PXOBJCLASS = {pyReportContentPage.pyPreparedValues(7) } ) ) INNER JOIN
    {CLASS:PegaProjMgmt-Work-Project} "Project" ON ( ( "PC0".PROJECTID = "Project".pyID
   AND "PC0".PXOBJCLASS = {pyReportContentPage.pyPreparedValues(8) }
   AND "Project".PXOBJCLASS = {pyReportContentPage.pyPreparedValues(9) } ) ) INNER JOIN
    {CLASS:PegaProjMgmt-Work-Product} "Product" ON ( ( "Product".PYID = "Project".ProductID
   AND "Product".PXOBJCLASS LIKE {pyReportContentPage.pyPreparedValues(10)}
   AND "Project".PXOBJCLASS = {pyReportContentPage.pyPreparedValues(11) } ) ) INNER JOIN
    {CLASS:PegaProjMgmt-Work-Product-Version} "ProductVersion" ON ( ( "ProductVersion".PYID =     "Project".ProductVersionID
   AND "ProductVersion".PXOBJCLASS LIKE {pyReportContentPage.pyPreparedValues(12)}
   AND "Project".PXOBJCLASS = {pyReportContentPage.pyPreparedValues(13) } ) )
WHERE ( "PC0".CATEGORY = {pyReportContentPage.pyPreparedValues(1) }
  AND "Backlog".CATEGORY = {pyReportContentPage.pyPreparedValues(2) }
  AND "Product".PYID = {pyReportContentPage.pyPreparedValues(3) } )
GROUP BY "Product".pyLabel , "ProductVersion".pyLabel , "Backlog".pyLabel

Using the Data Access tab and Pages & Classes tab of the report defintion rule, you can define these cascaded join conditions individually. You do not need a custom activity or hand-crafted SQL.


Suggested Approach


1. Create a report definition rule.

For the Applies To field, select the class of the data object the report will explore.

2. Declare pages for the classes the report uses.

On the Pages & Classes tab, list pages for each class the report works with. That puts the properties of each class within the scope of the report, simplifying how you refer to them on the Data Access tab.

pages and classes tab

3. Specify the joins between the tables

On the Data Access tab, specify the joins between the tables. The joins begin with a value in the work object -- in this case, pzInsKey -- and each new row extends the connection to another data table.

class joins

For each join, click Edit Conditions to display a form in which to specify the filter conditions. For the five class joins for this report, the filter conditions are:

JoinTable.pzInsKey Is Equal JoinTable.pxLinkedRefTo
BacklogJoinTable.pxLinkedRefFrom Is Equal Backlog.pzInsKey
ProjectProjectID Is Equal Project.pyID
ProductProduct.pyID Is Equal Project.ProductID
ProductVersionProductVersion.pyID Is Equal Project.ProductVersionID

This image is of the filter condition for ProductVersion:

filter conditions for join

4. Specify the data

On the Design tab, specify the data columns to include:

columns to include

In the Rows to Include section, specify filters for the data. In this case, the report wants only work objects for project PRD-6 that are stories in the various team backlogs:

rows to include

5. Save and run the report

Save the report definition rule. The report displays the grand total of stories in team backlogs, and a breakdown of how many stories are in each backlog:

resulting report


Pega Platform 6.1 SP2 Reporting Financial Services Healthcare and Life Sciences Insurance Communications and Media Government Healthcare and Life Sciences Consumer Services Consumer Services Manufacturing Consumer Services

Have a question? Get answers now.

Visit the Support Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best. is not optimized for Internet Explorer. For the optimal experience, please use:

Close Deprecation Notice
Contact us