Summary

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.

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
ORDER BY 2 ASC,3 ASC,4 ASC

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.

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.

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:

This image is of the filter condition for ProductVersion:

4. Specify the data

On the Design tab, specify the data 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:

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: