10.1 Report Definitions: Querying, Columns, Summaries & Joins

10.1 Report Definitions: Querying, Columns, Summaries & Joins

CSA Exam Focus: In Pega applications, extracting operational metrics, tracking service levels, and presenting tabular data across case lifecycles relies fundamentally on Report Definitions (Rule-Obj-Report-Definition). The Certified Pega System Architect exam heavily evaluates candidate mastery of report configuration: differentiating between List and Summary reports, selecting and calculating columns, navigating the class-to-table mappings of Work versus Data classes, and configuring relational joins through Class Joins, Declarative Index Joins, and low-code Association Rules.

---

Report Definitions (Rule-Obj-Report-Definition): The Primary Query Engine

In Pega Platform, the Report Definition rule type (Rule-Obj-Report-Definition) is the primary object-relational mapping and query rule used to retrieve data from the underlying relational database. It replaces deprecated legacy reporting rules (such as List View and Summary View rules) and generates optimized Structured Query Language (SQL) queries executed against the Pega database schema (PegaDATA or PegaRULES) or external customer databases.

Report Definitions serve multiple critical purposes across the platform architecture:

1. Portal & Dashboard Reporting: Driving interactive business charts, work queue summaries, and operational dashboards.
2. User Interface Controls: Populating UI selection lists, autocomplete dropdowns, and dynamic tables.
3. Data Page Sources: Supplying tabular records to read-only List structure Data Pages (D_CaseList, D_CustomerSummary).
4. Explore Data & Insights: Providing the underlying query infrastructure for App Studio self-service analytics.

+-----------------------------------------------------------------------------------+
|                       REPORT DEFINITION RULE FORM ARCHITECTURE                    |
+-----------------------------------------------------------------------------------+
| [Query Tab]           | [Data Access Tab]         | [Parameters Tab]              |
| - Columns to Include  | - Class Joins             | - Dynamic Runtime Parameters  |
| - Summaries & Calcs   | - Declarative Index Joins |   (e.g., param.Department)    |
| - Sorting & Grouping  | - Associations            | - Data Types & Prompt Options |
| - Filter Conditions   | - Sub-reports             |                               |
+-----------------------------------------------------------------------------------+

---

Core Structure: Columns, Calculations, Summaries & Grouping

The Query Tab of the Report Definition rule form governs what data is selected, how it is calculated, and how results are organized.

1. Columns to Include

Every column in a Report Definition represents an exposed property or a calculated value derived from the primary class or joined classes:

• Column Name: The dot-prefixed property reference (e.g., .pyID, .pyStatusWork, .pxCreateDateTime).
• Caption: The user-facing localized column header displayed in the report grid (e.g., "Case ID", "Current Status", "Creation Date").
• Column Formatting: Predefined presentation formats (e.g., currency formatting $1,250.00, date patterns MM/DD/YYYY, or boolean indicators).

2. Calculation Columns & SQL Function Aliases

Report Definitions do not merely display raw stored values; they can compute derived values at query execution time directly within the database engine. System architects can insert SQL Function Aliases into column calculations:

• Arithmetic Calculations: Computing gross margins or tax totals (e.g., .SubTotal * 1.08).
• String Manipulations: Concatenating first and last names (e.g., @concat(.FirstName, ' ', .LastName)).
• Date Computations: Calculating elapsed days between creation and resolution (e.g., @DateDifference(.pxCreateDateTime, .pyResolvedTimestamp, 'DAYS')).

3. List Reports vs. Summary Reports

A Report Definition operates in one of two structural modes depending on whether aggregating functions are applied:

Summary Functions

A report becomes a Summary Report as soon as an architect selects a summarizing function for one or more numerical or categorical columns:

• COUNT: Tallies the total number of records matching each grouping category (e.g., count of cases per .pyStatusWork).
• SUM: Computes the mathematical total of a numeric field (e.g., total claim payouts per region).
• AVG (Average): Calculates the arithmetic mean (e.g., average resolution days per operator).
• MIN / MAX: Returns the lowest or highest value within the grouped partition (e.g., minimum credit score, maximum order value).
• COUNT DISTINCT: Counts unique occurrences of a property value, eliminating duplicates.

Grouping and Drill-Down Capabilities

When summary functions are applied, non-aggregated columns automatically become Grouping Columns:

• Hierarchical Grouping: Cases can be grouped by primary category (e.g., .Department) and sub-category (e.g., .pyStatusWork).
• Drill-Down: Summary reports support interactive drill-downs. When a business user clicks on an aggregated number (e.g., clicking on the number "42" representing open fraud claims), Pega dynamically opens a detailed list report displaying the exact 42 underlying case records that formed that aggregate count.

Sorting Rules

• Columns can be sorted in Ascending (Lowest to Highest) or Descending (Highest to Lowest) order.
• In Summary Reports, sorting can be applied to categorical groupings (alphabetical by department name) or directly to the aggregated summary metric (sorting descending by total revenue so highest-earning branches appear first).

---

Reporting on Work Classes vs. Data Classes

Pega applications maintain a strict architectural separation between Work Classes and Data Classes, which directly impacts how database tables are mapped and queried.

+-----------------------------------------------------------------------------------+
|                       WORK CLASSES VS DATA CLASSES IN REPORTING                   |
+-----------------------------------------------------------------------------------+
| WORK CLASSES (Work- / Work-Cover-)       | DATA CLASSES (Data-)                   |
| - Represents transactional case lifecycles| - Represents reference & entity data   |
| - Standard table: pc_work (PegaDATA)     | - Standard table: pr_data (PegaDATA)   |
| - Standard case properties:              | - Domain entity properties:            |
|   * pyID (Case identifier: O-1001)       |   * CustomerID, AccountNumber          |
|   * pyStatusWork (Open-Active)           |   * StreetAddress, City, State         |
|   * pxCreateDateTime (Creation time)     |   * Price, UnitCost, SKU               |
|   * pxUrgencyWork (Calculated urgency)   | - Mapped via Data-Admin-DB-Table       |
|   * pzInsKey (Primary unique key)        | - External database table integration   |
+-----------------------------------------------------------------------------------+

1. Reporting on Work Classes (Work-)

• Inheritance & Storage: Work classes inherit from Work- (for individual cases) or Work-Cover- (for parent cases covering child cases). By default, work instances persist in the pc_work table within the PegaDATA schema (or specialized tables such as pc_FS_AutoLoan_Work).
• Standard System Properties: Every work report has access to out-of-the-box tracking properties managed automatically by the Pega engine: .pyID, .pyStatusWork, .pxCreateDateTime, .pxCreateOpName, .pxUrgencyWork, and .pyResolvedTimestamp.
• Optimized Keys: The relational primary key for work tables is pzInsKey, which uniquely identifies each case instance across the entire platform (e.g., FS-AUTOLOAN-WORK O-1001).

2. Reporting on Data Classes (Data-)

• Inheritance & Storage: Data classes inherit from Data- and represent structural business entities, reference tables, or lookup objects (e.g., FS-Data-Customer, FS-Data-Policy). Internal data instances persist in the pr_data table or dedicated standalone relational tables.
• External Data Tables: Enterprise applications frequently map Data- classes to external relational databases (e.g., an Oracle customer database or PostgreSQL inventory table). Mapping is established via Data-Admin-DB-Table records.
• Reporting Scope: Report Definitions authored in Data- classes query reference records independently or act as data sources for dynamic UI dropdowns.

---

Joins in Report Definitions

Operational reporting frequently requires synthesizing data across multiple entities—such as displaying case details alongside customer demographics, or searching cases by line-item SKUs. Report Definitions achieve this through three distinct relational joining mechanisms configured on the Data Access Tab:

+-----------------------------------------------------------------------------------+
|                         RELATIONAL JOIN MECHANISMS IN PEGA                        |
+-----------------------------------------------------------------------------------+
| [1] CLASS JOINS          : Standard relational joins (Inner, Left Outer, Right)   |
|                            between independent Pega classes using prefix notation.|
| [2] DECLARE INDEX JOINS  : Joins dedicated index tables (Index-) to query 1-to-N  |
|                            embedded Page Lists stored inside the case BLOB.       |
| [3] ASSOCIATION RULES    : Preconfigured, reusable join definitions authored by   |
|                            system architects for low-code citizen developers.     |
+-----------------------------------------------------------------------------------+

1. Class Joins

A Class Join connects the primary report class to another independent Pega class, mirroring standard relational SQL joins (JOIN ON).

Configuration Steps

1. On the Data Access tab under Class Joins, add a new row.
2. Define a Prefix Alias: A short identifier used to qualify properties from the joined class (e.g., Cust for customer data, Mgr for operator data).
3. Specify the Class Name: The target Pega class being joined (e.g., FS-Data-Customer).
4. Define Join Criteria: Match a property on the primary class to a property on the joined class (e.g., Primary.CustomerID = Cust.CustomerID).
5. Select Join Type:
   • Only include matching rows (Inner Join): Returns a row only when a matching record exists in both the primary case class and the joined class.
   • Include all rows in this class (Left Outer Join): Returns all cases from the primary class. If no matching record exists in the joined customer class, the case is still returned, with customer fields displaying null or blank.
   • Include all rows in the joined class (Right Outer Join): Returns all records from the joined class regardless of whether any primary cases match.

Referencing Joined Properties

Once configured, joined properties are referenced on the Query tab using prefix dot-notation: Cust.pyFirstName, Cust.EmailAddress, Cust.PhoneNumber.

2. Declarative Index Joins (Querying Embedded Page Lists)

A major architectural challenge arises when an application must query or filter by properties stored inside an embedded Page List (e.g., a case containing .LineItems() or .AuthorizedUsers()).

The Problem with Embedded Page Lists

In relational databases, child array elements cannot be mapped to scalar columns on the parent table without violating first normal form (1NF). In Pega, embedded page lists are serialized directly inside the parent case's compressed Binary Large Object (BLOB) column (pzPVStream). Standard SQL queries cannot execute joins or search conditions against serialized BLOB arrays.

The Architectural Solution: Declare Index (Rule-Declare-Index)

To make embedded list properties queryable, a System Architect creates a Declare Index rule:

1. The Declare Index monitors the target Page List property (e.g., .LineItems()).
2. Whenever a case is committed to the database, the Pega declarative engine automatically extracts each element from the page list and writes a distinct relational row into a dedicated Index Table (mapped to an Index- class, e.g., Index-FS-Order-LineItems).
3. The index table maintains a foreign key reference named pxInsIndexedKey, which stores the pzInsKey of the parent case.

Configuring Declarative Index Joins

In the Report Definition's Data Access tab, the architect adds a Declarative Index Join:

• Select the pre-existing Declare Index rule and assign a prefix (e.g., Item).
• The Pega query engine automatically constructs the underlying SQL join between pc_work.pzInsKey and pr_index_table.pxInsIndexedKey.
• The architect can now display, sort, and filter on individual item attributes: Item.SKU, Item.Quantity, Item.UnitPrice.

3. Association Rules (Rule-Obj-Association)

While Class Joins and Declare Index Joins are configured on individual Report Definitions by System Architects in Dev Studio, Association Rules provide a model-driven abstraction designed for low-code reusability.

Architectural Purpose

An Association Rule (Rule-Obj-Association) is a reusable rule that defines the relational join conditions between two classes once, at design time:

• Specifies the join relationship (Primary class, Foreign class, Join Keys, Join Type).
• Authored and maintained in Dev Studio by System Architects across the Enterprise Class Structure (ECS).

Low-Code Enablement in App Studio

When an Association rule exists, citizen developers and business architects building reports in App Studio or the Dev Studio Report Editor do not need to understand database schemas, foreign keys, or prefix aliases. Instead:

1. The associated class appears directly in the visual Data Explorer field picker as an expandable child folder (e.g., expanding "Customer Details" or "Assigned Operator").
2. The user simply drags or checks fields (e.g., "Customer Email").
3. Pega automatically applies the underlying Association rule to generate the appropriate SQL join at runtime.