Joins, associations, and subreports
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 parent and child case relationships to show child case 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 child case. 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 can combine data from different classes by configuring class joins, referencing association rules, or creating subreports.
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.
In the following image, click the + icons to view the details for how to configure this report.
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.
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. Doing so ensures that you are joining to the correct data set.
Class join settings
On the Data Access tab of 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 this 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 an outer join) |
| Select Include all rows in joined class | 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 an 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 child Benefits Enrollment class to the .pzInsKey property in the parent 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.
Association rules
You use association rules as another method 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 User 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.
When you add a column, you specify the association rule class name as a prefix, and 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.
Subreports
A subreport is a report that is called by another report to provide data. Subreports enable you to reference results from any report definition in the main report. You can run a report definition that is used as a subreport like any other report. You commonly use subreports to satisfy complex reporting requirements. Using subreports to filter results allows you to include or exclude data. You can also use subreports to display aggregate calculations on specific rows in the main report.
Note: Consider subreports as a way of combining data using IN, HAVING, and WITH clauses.
Manage reports that use complex logic by including aggregated data from other reports. In your main report, you can use aggregate functions, such as Is equal or Is less than, to include specific data from other reports. For example, in your main report about managers, you can reference a subreport that fetches the number of all employees who directly report to those managers. You can set a filter condition to display only the managers who have more than 10 subordinates.
Subreports can be defined in classes that are different from the main report using join filters. You can access data in different classes similar to how you use a class join or an association. For example, in a financial report of the company, you can include a subreport with a property that fetches the most recent expenses of employees. This approach provides a way to filter the contents of the main report to display how the recent expenses affect the company budget.
The following interaction uses a subreport to display the task information for each purchase request recently updated by each operator. In the center of the following image, slide the vertical line to view the subreport configuration on the left and subreport output on the right.
Check your knowledge with the following interaction.
If you are having problems with your training, please review the Pega Academy Support FAQs.
Want to help us improve this content?