Joins, associations, and subreports
Class combination using joins and associations
You can relate properties in multiple database tables or classes to combine data in a single report. The following examples show you how to use properties to make relationships between classes or tables.
- Use case and subcase relationships to show subcase data along with the parent case data. For example, assume you want to create a report that lists the purchase orders in a purchase request. You match the case identifier in the parent purchase order class to the case identifier in the child purchase request class.
- Use case and assignment relationships to show how the system processes assignments for a specific case or a subcase. For example, assume you want to show the operators working on specific cases. You match the operator identifier in the case database table with the operator ID column in the assignment table.
- Use case and history relationships to monitor performance. For example, assume you want to show the total amount of time required to resolve specific cases. You match the case identifier in the case data table with the case identifier in the history table.
You create class or database table relationships in a report definition. You do not specify database tables to define joins. You can either configure class joins or you can reference association rules.
Class joins
When you build a class relationship in a report definition, you configure a class join. For example, assume you want a report that identifies the current assignment and operator working on each candidate case. The candidate information is in the Candidate case type. The assignment information is in a workbasket class. You would create a report definition in the candidate class and configure a class join to the workbasket class. In the report definition, you would first specify the workbasket class as the class you want to join to your report. You would then specify a filter that matches key values in records for both classes. For example, you would match the candidate case key value pzInsKey to the workbasket key value pzRefObjectKey. In this way, the report can correctly match, for each case, the records in both classes.
You follow these basic steps to create a class join:
- Determine the class to which you are joining.
- Create a prefix that in combination with the class name serves as an alias for the joined class.
- Decide whether you want to include or exclude instances that do not match.
- Create a filter that describes how you relate the classes.
Note: In the report definition form, you specify the class as the primary join. If this work type is derived from Work-, determine whether the join is to an implementation class or to a framework class. This ensures that you are joining to the correct data set.
Class join settings
On the Data Access tab on a report definition form, in the Class name field you specify the class you are joining. For each class, in the Prefix field you enter a text prefix. The prefix combined with the class name serves as an alias for the joined class and its properties. For example, to join to a work class that describes benefits enrollment cases, you might use the prefix BE as shown in the following image.
When you add properties to columns in your report, the prefix helps you identify the properties in the joined class.
In the Type field, you specify how you want the system to join the data by selecting one of the following options:
| Type | Description |
|---|---|
| Only include matching rows |
To only include instances in each class that have a matching instance in the other class (referred to in database terms as an inner join) |
| Select Include all rows in <class> |
To include all qualifying instances of the Applies To class of the rule, even if there is no match found in the joined (prefix) class (referred to in database terms as a left outer join) |
| Select Include all rows in <prefix> | To include all qualifying instances of the joined (prefix) class, even if there is no match found in the Applies To class (referred to in database terms as a right outer join) |
Filter conditions
You create a filter condition that defines the relationship between the classes. The filter uses one or more properties to establish the relationship. Consider the Benefits Enrollment class join to the Candidate class. You would create a filter that matches the .pxCoverInsKey property in the Benefits Enrollment class to the .pzInsKey property in the Candidate class.
Note: You cannot join to a class in a different database than the Applies To class of the report. The Column property must be an exposed column. You can use the Optimization tool to expose columns. For more information about the tool, see the Help topic Exposing properties and populating database columns.
For descriptions of the fields you use when defining a class join, see the Help topic Report definition access tab.
Association rules
You use association rules to join multiple classes. Unlike a class join (unique to each report), associations can be reused in any report. Managers can also use associations when they create reports in the Case Manager portal.
For example, assume you want to combine records in the Assign-WorkBasket class with records in work classes. You use the standard WorkBasket Assignment (pxWorkbasketAssignments) association rule to join the classes.
Note: Pega provides a set of standard association rules. You can use these rules for many class joins. For example, standard association rules allow you to join work to assignment classes or to history classes. For a list of standard association rules, see the Help topic Standard Association rules.
When you add a column, you specify the association rule class name as a prefix, then select the properties in the class.
When you add the association rule prefix, it appears on the Data Access tab in the Associations section.
For information about creating your own association rules, see the Community article When and how to create an association rule to support reporting.