Associations, class joins, and subreports

Reports are created and used by developers and business users. The reports required by developers are primarily for use as input for business logic or for presenting information to end users' screens. In comparison, the reports required by business users are for analysis of data to help with further business decisions. So the report browser allows business users to create reports from their portal. Most of the time, the necessary data for a report is not sourced from a single class or table, and so you must join the different classes and tables using sets of properties. Building these relationships is a requirement when creating analytical reports, so Pega Platform™ provides associations, joins, and subreports to help do so.

Associations are rules that define the relationship between classes and can be used in report definition rules. You can extend the scope of a report by establishing a relationship between multiple classes based on matching values in pairs of properties. You can use association rules by referencing specific data from other classes to make your reports more comprehensive.

Pega Platform provides users with both simple and advanced associations. Use simple associations to join two classes and advanced associations when you want to specify multiple join conditions. You can reuse each association rule in multiple reports.

Joins and subreports are not available to business users on the end-user portal. Only associations are available and are relatively easy to use. So lead system architects (LSAs) should encourage the creation of associations wherever possible while designing a solution to increase reusability.

Once an association is created in a class, it is available for use anywhere in the class hierarchy. As a best practice, create associations in the Enterprise layer or a reusable layer for use across the organization and top-level application layers.

The report editor and reporting rules also use associations. The LSA should carefully consider the reporting requirements and pre-build association rules, enabling the businessperson to construct the reports to access all the required data. The associations negates the need for the businessperson to know anything about class relationships.

For example, for a Front Stage Group (FSG) application, you need a report to get Hotel information and the Contact at the Hotel for each Rooms Request. Managers in the Hotel application require this report. If you define the required association between the Rooms Request, Hotel, and Contact tables, then the Managers can use this association directly from the Report Browser and drag and drop the required fields into the report. Along with application-specific associations, several out-of-the-box associations in Pega Platform can be reused as well, depending on reporting needs.

Managers can create a new report from the Report Browser and drag and drop the required columns from the available associations. Managers must rely on the development team to pull the data from multiple source tables if the associations are not available.

By default, report definitions include data only from the class to which the report applies. By using the class join functionality, you can make your reports more comprehensive. The class joins help to join two classes only when both classes have the same columns.

A class join contains three types of joins based on the type that is configured on the Data access tab:

• Only include matching rows = INNER JOIN
• Include all rows in this class = LEFT OUTER JOIN
• Include all rows in joined class = RIGHT OUTER JOIN

Compared with association rules, joins are not reusable across multiple report definitions and apply only to the report you edit.

For example, in an FSG application, you want a report that identifies the current assignment and operator working on each Book Event case. The Book Event information is in the Book Event case type. The assignment information is in a workbasket class. You create a report definition in the Book Event class and then configure a class join to the workbasket class. In the report definition, you first specify the workbasket class as the class that you want to join in your report. Then, specify a filter that matches key values in the records for both classes (for example, matching the pzInsKey key value of the candidate case to the pzRefObjectKey key value of the workbasket).

Depending on the reporting requirement, the LSA should decide when to use an association and use a class join.

Subreports join two different classes using specific join conditions. The results from the second report can then be used to filter the first report or be displayed as part of the first report. Subreports in Pega Platform are used as WHERE clause subqueries. There are also FROM clause subqueries (“table subqueries”) and SELECT clause subqueries.

You commonly use subreports to satisfy complex reporting requirements. For instance, you can use subreports to filter results so that you can include or exclude data. You can also use subreports to display aggregate calculations in specific rows in the main report. You can use two different methods to create a subreport: join filters or aggregation.

For example, in an FSG application, you want to display a report that shows a list of all Book Event cases and includes:

• Columns for when the Book Event case is created.
• The operator who created the Book Event.
• The case ID.

All these columns come from the Book Event work class. You then need to include aggregate calculations on subcases of the Book Event: the date when the first Hotel Rooms Request was resolved, the date when the last Hotel Rooms Request was resolved, and the Hotel Rooms Request count. To include these calculations requires you to use a subreport.

LSAs should be aware that subreports can produce the same result set as a class join. If this happens, you should use a class join instead because class joins are a simpler and more direct way for the database to perform the operation.

For more information about associations, joins, and subreports, see associations, joins and subreports.