Use the optional 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 items 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. (Validation occurs when you save the rule form, not when you click Apply in the Join Conditions window.)
To enable the Join and Index capabilities, check the Read Only check box on the Content tab. The Edit in Excel option (on the Organize tab) must be disabled.
For an example of a Join-based report, see the Pega Community article How to join classes for reports.
|Prefix||Enter a short 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.|
Select a class to be the primary class for the join. The
plus this class name specify the joined class. You can specify multiple primary classes to be joined.
Click to open the
Edit Join Conditions
window and specify properties of the class, join conditions, and values. Complete these fields in the window:
For example, if the table contains four rows labeled A, B, C, and D, you can enter: in this field. This notation is the same as for the logic statement in when condition rules. If you leave the
field blank, the system selects only instances for which all criteria rows are true.
For example, if the table contains four rows labeled A, B, C, and D, you can enter:
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.
Declarative Index Join
Indexes created by Rule-Declare-Index rules can be specified here 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.
|Prefix||Enter a 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 Declare Index rule.|
Note that if an application is designed such that concrete Work- classes are mapped to different database 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.