Join tab on the Summary View form

Use the Join tab to merge data from multiple tables into a single report. You can report on data from two tables using a join clause to concatenate the data from both tables using a common column. For example, you can join Work- objects with their associated assignments or lists of embedded properties of an object using Index- classes defined by a Rule-Declare-Index rule. A JOIN ‘expands’ the primary table to include duplicate entries of its data, having one additional row for each row that matches the key in the secondary table.

  • Joins can be done with additional classes by matching properties between the classes.

    This feature provides new reporting capabilities between classes with a common field.

  • These features are independent of each other.

For the Logic, Join and Property constraints, validation checks that properties used in the join exist and that the types of the properties match for the join. Validation occurs when you save this Summary View form, not on Apply in the Join Conditions window.

To enable the Join and Index capabilities, it is necessary to select the Read Only check box on the Content tab.

For an example of a Join-based report, see the Pega Community article How to join classes for reports.

Class join

Field Description
Prefix Enter a text string to be used as an alias for the joined class and all its properties. The Prefix is assigned to all the properties in the class and used to reference the properties from these fields for the Display Fields and Content tabs.
Class Name Select a class to be the primary class for the join. The Prefix plus this class name specify the joined class. You can specify multiple primary classes to be joined.

If this work type is derived from Work-, choose carefully whether you want to join to an implementation class, or in a framework class. At run time, list view rules with a framework class in this field can report on work items in the associated implementation class, if the Report on Descendant Class Instances option on the Content tab is selected.

Edit Conditions
Click to open the Join Conditions window and specify the columns to join in the two tables.
Field Description
Label Enter a text label that uniquely identifies this row. The label is used in the Logic field to compare rows.
Property Select a property from the SmartPrompt list. Note that the properties are all those associated with the class you specified in the Join tab. This must be an exposed column.
Condition Select Is Equal.
Value Select the value of the property that you want to use in the joined report.
Logic Optional. Using the labels entered in the Criteria array, enter a logical expression that defines how the system combines the criteria into an overall Boolean value at runtime. The expression can include parentheses and the operators AND and OR.

For example, if the table contains four rows labeled A, B, C, and D, you can enter:

(A OR B OR C) AND D

in this field. This notation is the same as for the logic statement in when condition rules.

If you leave the Logic field blank, the system selects only instances for which all criteria rows are true.

Index- instances created by Rule-Declare-Index rules can be specified in the rule and used to reference embedded properties for both display and criteria from the Applies To class. This facilitates using Rule-Declare-Index rules for performance by avoiding retrieving properties from the BLOB (Storage Stream) column. You can attach an alias to any declarative index that you have previously defined.

Declarative Index Join

Prefix Enter an alphanumeric text string to be used as an alias for the joined declarative index class and all its properties. The Prefix is assigned to all the properties in the class and used to reference the properties from these fields for the Display Fields and Content tabs.
Index Name Specify the Index- class created by the Rule-Declare-Index rule.

Note that if an application is designed such that concrete Work- classes are mapped to different DB tables, then reports defined on the work pool or on any abstract class containing those classes (like standard reports defined on Work- or joining from Assign-Worklist to Work-) will not include data for all of the classes, since the Pega Platform does not dynamically UNION the data from multiple tables by default.