PEGACPLSA23V1 Reporting Design

Reporting Design Detailed Explanation

Reporting in Pega enables you to retrieve, filter, and visualize application data to extract meaningful insights and support data-driven decisions. Reports help business users analyze case progress, operational performance, and overall application behavior.

6.1 Report Definitions

What is a Report Definition?

A Report Definition is a rule in Pega that retrieves, filters, and displays application data from the Pega database. It is the foundation of reporting in Pega and provides business users with the ability to generate list reports and summary reports.

Purpose of Report Definitions

1. Retrieve data from one or more tables in the database.
2. Apply filtering, sorting, and grouping logic to organize data.
3. Display the retrieved data in a tabular or chart format.
4. Optimize performance using indexing and best practices.

6.1.1 Key Features of Report Definitions

1. Columns
2. Sorting and Grouping
3. Filtering

1. Columns

Definition: Columns define which properties (fields) to include in the report.

Example: Loan Application Report

Scenario: Display a list of loan applications with the following details:

Property	Column Header
LoanID	Loan ID
CustomerName	Customer Name
LoanAmount	Loan Amount
LoanStatus	Status
SubmissionDate	Submission Date

Steps to Add Columns:

1. Open the Report Definition rule.
2. Go to the Columns tab.
3. Add the required properties and specify column headers.

2. Sorting and Grouping

Sorting: Arrange rows in ascending or descending order based on a column.
Grouping: Combine rows that share the same value into a single group.

Example: Group Loan Applications by Status

Scenario: Group loan applications by their status (e.g., Approved, Pending, Rejected) and sort them by submission date.

1. Group By: LoanStatus
2. Sort By: SubmissionDate (Ascending)

Loan Status	Loan ID	Customer Name	Submission Date
Approved	001	John Doe	2024-06-01
Approved	002	Mary Smith	2024-06-02
Pending	003	Alice Johnson	2024-06-03

Steps to Configure Sorting and Grouping:

1. In the Report Definition rule, go to the Sorting tab.
2. Add the property (e.g., SubmissionDate) and set the order (Ascending or Descending).
3. In the Columns tab, specify properties for grouping under Group By.

3. Filtering

Filtering: Apply conditions to limit the data retrieved by the report.

Example: Filter Loan Applications Submitted After June 1, 2024

Condition: SubmissionDate >= "2024-06-01"

Steps to Add Filters:

1. Go to the Query Tab in the Report Definition.
2. Add a filter condition:
   • Property: SubmissionDate
   • Operator: >=
   • Value: "2024-06-01".

6.1.2 Example: Build a Loan Application Report

Scenario: Generate a report for approved loans submitted after June 1, 2024.

Requirements:

• Display columns: Loan ID, Customer Name, Loan Amount, Status, and Submission Date.
• Filter: Status = “Approved” and SubmissionDate >= “2024-06-01”.
• Sort: Submission Date in ascending order.

Steps to Build the Report

1. Create Report Definition:

   • Go to Reports → Create Report Definition.
   • Name: ApprovedLoanApplications.

2. Add Columns:

   • LoanID → Column Header: Loan ID.
   • CustomerName → Column Header: Customer Name.
   • LoanAmount → Column Header: Loan Amount.
   • LoanStatus → Column Header: Status.
   • SubmissionDate → Column Header: Submission Date.

3. Add Filters:

   • Filter 1: LoanStatus = "Approved".
   • Filter 2: SubmissionDate >= "2024-06-01".

4. Add Sorting:

   • Property: SubmissionDate.
   • Order: Ascending.

5. Save and Run:

   • Save the rule and run the report to display the filtered and sorted results.

Result

Loan ID	Customer Name	Loan Amount	Status	Submission Date
001	John Doe	$50,000	Approved	2024-06-01
002	Mary Smith	$75,000	Approved	2024-06-02
004	Chris Lee	$30,000	Approved	2024-06-04

6.1.3 Optimization Techniques

To ensure that reports run efficiently and perform well, follow these optimization techniques:

1. Use Indexed Columns:

   • Apply database indexes to frequently filtered or sorted columns.
   • Example: Add an index to SubmissionDate for faster filtering.

2. Limit Data Retrieval:

   • Avoid retrieving large datasets by adding filters and pagination.
   • Example: Fetch only the top 100 records.

3. Fetch Only Required Columns:

   • Include only necessary properties in the report to reduce database load.

4. Avoid Complex Filters:

   • Simplify WHERE clauses to minimize query execution time.

5. Monitor Report Performance:

   • Use Pega Predictive Diagnostic Cloud (PDC) to identify and optimize slow-running reports.

Summary of Report Definitions

1. Purpose: Retrieve, filter, and display application data.
2. Key Features:
   • Columns: Define which properties to display.
   • Sorting/Grouping: Organize and aggregate data.
   • Filters: Limit the retrieved data based on conditions.
3. Example: Generate a report for approved loan applications submitted after June 1, 2024.
4. Optimization:
   • Use database indexes.
   • Fetch only necessary columns.
   • Add filters to limit data retrieval.

6.2 Report Types

Pega provides three main types of reports:

1. List Reports
2. Summary Reports
3. Charts

These reports allow business users and developers to present data in both detailed and aggregated formats, including visualizations like charts and graphs.

6.2.1 List Reports

What is a List Report?

A List Report displays data in a tabular format, showing records in rows and columns. It is ideal for presenting detailed, itemized data from the database.

Features of List Reports

1. Column Selection: Choose which properties (fields) to display in the table.
2. Sorting: Sort rows in ascending or descending order based on a column.
3. Filtering: Apply conditions to limit the data displayed.
4. Pagination: Display data in smaller chunks for large datasets.

Example: Loan Applications List Report

Scenario: Display all loan applications with their details.

Fields to Display:

• LoanID
• CustomerName
• LoanAmount
• LoanStatus
• SubmissionDate

Filters:

• LoanStatus = “Pending”

Steps to Create the List Report:

1. Open App Studio or Dev Studio.
2. Create a new Report Definition.
3. Add columns for LoanID, CustomerName, LoanAmount, LoanStatus, and SubmissionDate.
4. Add a filter condition: LoanStatus = "Pending".
5. Configure sorting and enable pagination.
6. Run the report.

Result:

Loan ID	Customer Name	Loan Amount	Status	Submission Date
003	Alice Johnson	$40,000	Pending	2024-06-02
005	Tom Harris	$30,000	Pending	2024-06-03

Use Case for List Reports

• Generate detailed, itemized views of data.
• Use in dashboards for displaying case or task records.

6.2.2 Summary Reports

What is a Summary Report?

A Summary Report aggregates and organizes data using functions like SUM, AVG, MIN, MAX, and COUNT. It is ideal for analyzing trends and high-level summaries.

Features of Summary Reports

1. Aggregation Functions: Perform calculations on numeric data:

   • SUM: Total of a numeric column.
   • AVG: Average value.
   • MIN and MAX: Minimum and maximum values.
   • COUNT: Number of records.

2. Group By: Group data by a specific field to organize the report.

3. Drill Down: Allow users to click on aggregated values to view underlying detailed data.

Example: Loan Applications Summary Report

Scenario: Summarize total loan amounts grouped by Loan Status.

Fields:

• Group By: LoanStatus
• Aggregate Function: SUM of LoanAmount

Steps to Create the Summary Report:

1. Open App Studio or Dev Studio.
2. Create a Report Definition.
3. Select LoanStatus as the Group By field.
4. Add SUM(LoanAmount) as the aggregate function.
5. Run the report.

Result:

Loan Status	Total Loan Amount
Approved	$200,000
Pending	$70,000
Rejected	$50,000

Use Case for Summary Reports

• Generate high-level business summaries for reporting dashboards.
• Analyze trends and aggregated metrics.

6.2.3 Charts

What are Charts?

Charts provide a visual representation of report data, making it easier to analyze trends, compare values, and gain actionable insights.

Types of Charts in Pega

1. Bar Chart: Compare data across categories.

   • Example: Total loan amounts by loan status.

2. Line Chart: Display trends over time.

   • Example: Monthly loan application submissions.

3. Pie Chart: Show proportions as slices of a circle.

   • Example: Percentage of loan statuses (Approved, Pending, Rejected).

4. Column Chart: Similar to bar charts but vertical.

   • Example: Number of cases by priority.

Steps to Add a Chart to a Report

1. Open the Report Definition rule.
2. Go to the Chart tab.
3. Select the Chart Type (Bar, Line, Pie, etc.).
4. Configure the X-Axis (Category) and Y-Axis (Values).
5. Save and run the report.

Example: Loan Applications Pie Chart

Scenario: Visualize the percentage of loan applications by Loan Status.

Chart Type: Pie Chart

• Group By: LoanStatus
• Value: COUNT(LoanID)

Steps:

1. Create a Summary Report grouped by LoanStatus.
2. In the Chart Tab, select Pie Chart.
3. Configure:
   • X-Axis: LoanStatus (Category).
   • Y-Axis: COUNT(LoanID).
4. Save and run the report.

Result:

• Approved: 50%
• Pending: 30%
• Rejected: 20%

A Pie Chart will visually display these proportions as slices.

Use Cases for Charts

• Business dashboards for data visualization.
• Compare values across categories.
• Identify trends, outliers, and proportions.

Summary of Report Types

Report Type	Description	Use Case
List Reports	Displays detailed, tabular data.	View detailed records (e.g., task list).
Summary Reports	Aggregates data using functions like SUM.	Analyze high-level metrics (e.g., totals).
Charts	Visualizes data using bar, line, or pie charts.	Display trends, comparisons, or proportions.

6.3 Reporting Performance

Efficient reporting is essential to ensure your Pega application performs well and delivers timely results. Poorly designed reports can lead to slow response times, database overload, and a poor user experience.

Key Performance Areas for Reporting

1. Avoid Large Datasets
2. Optimize Report Definitions
3. Use Database Indexes
4. Declarative Reports for Efficiency
5. Monitor and Troubleshoot Reports

6.3.1 Avoid Large Datasets

Why Avoid Large Datasets?

Retrieving excessive rows of data can:

• Slow down report execution.
• Overload the database and impact application performance.
• Make reports difficult for users to analyze.

Techniques to Limit Data Retrieval

1. Apply Filters:

   • Use WHERE clauses to fetch only relevant data.
   • Example: Retrieve only pending or approved loan applications.

   Filter Example:

   SELECT *  
   FROM LoanApplications  
   WHERE LoanStatus = 'Approved';  
   

2. Pagination:

   • Break large datasets into smaller chunks using pagination.
   • Example: Display 20 rows per page in a loan report.

3. Limit Results:

   • Restrict the number of rows returned using row limits.
   • Example: Fetch the top 100 records.

4. Fetch Only Required Columns:

   • Avoid selecting unnecessary columns in your report.

   Optimized Query:

   SELECT LoanID, CustomerName, LoanAmount  
   FROM LoanApplications;  
   

Best Practice Example

Scenario: Generate a Loan Report for pending loans.

• Filters: Add LoanStatus = "Pending".
• Row Limit: Set a maximum of 50 rows.
• Columns: Display only essential fields (LoanID, CustomerName, LoanAmount, SubmissionDate).

6.3.2 Optimize Report Definitions

Report Definitions can be optimized using the following strategies:

1. Use Indexed Properties:

   • Filters should be applied on indexed columns to improve database query performance.
   • Example: Add an index to SubmissionDate and LoanStatus.

2. Avoid Computed Columns:

   • Avoid applying complex calculations or transformations in the report, as they increase query load.

3. Minimize Joins:

   • Avoid excessive table joins. If possible, flatten data for reports.
   • Use Lookup tables or precomputed aggregates for performance.

4. Aggregate at the Database:

   • Perform data aggregation (SUM, AVG, COUNT) at the database level rather than in the Pega UI.

Example: Optimize a Loan Application Report

Before Optimization:

• Query retrieves all fields and performs client-side sorting.
• Result: Slow execution time.

After Optimization:

1. Use filters on indexed columns: LoanStatus.
2. Select only required fields: LoanID, CustomerName, LoanAmount.
3. Add a row limit of 50.
4. Perform server-side sorting on SubmissionDate.

6.3.3 Use Database Indexes

What are Database Indexes?

A database index improves query performance by enabling faster data retrieval. Without indexes, the database must scan the entire table, which is time-consuming.

When to Use Indexes

1. Frequently Filtered Columns:

   • Columns used in the WHERE clause of reports.
   • Example: LoanStatus, SubmissionDate.

2. Sorted Columns:

   • Columns used for sorting data.

3. Foreign Keys:

   • Columns that link tables together.

Steps to Add Indexes

1. Identify the frequently queried or filtered properties.
2. Use Pega’s Database Schema Optimization to add indexes to these columns.
3. Test the report performance before and after applying indexes.

Example: Add an Index to SubmissionDate

If a report frequently filters loans by SubmissionDate, add an index:

CREATE INDEX idx_submission_date  
ON LoanApplications (SubmissionDate);

6.3.4 Declarative Reports for Efficiency

What are Declarative Reports?

Declarative reports use Declarative Indexing to improve performance by precomputing frequently queried data into a separate index table.

When to Use Declarative Reports

• For reports that query complex relationships or properties in embedded pages.
• To avoid the overhead of computing data at runtime.

Steps to Configure Declarative Indexing

1. Enable Declarative Indexing for the property.
2. Pega creates a separate index table in the database.
3. Use the index table as the data source for reports.

Example: Reporting on Embedded Data

Scenario: Generate a report on child cases within parent loan cases.

• Use Declarative Indexing to store the embedded child case details in a separate table.
• Reference the index table in the Report Definition.

6.3.5 Monitor and Troubleshoot Reports

Monitoring report execution helps identify bottlenecks and optimize performance.

Tools for Monitoring Reports

1. Pega Predictive Diagnostic Cloud (PDC):

   • Monitor slow-running reports.
   • Identify inefficient queries and excessive data retrieval.

2. Performance Analyzer (PAL):

   • Analyze database interactions and execution times for reports.

3. Log Files:

   • Check logs for query performance issues or errors.

Steps to Troubleshoot a Slow Report

1. Identify the report execution time using Pega PDC.
2. Check the SQL query generated by the Report Definition.
3. Optimize:
   • Add filters to limit data.
   • Use indexed properties.
   • Simplify joins and computed columns.

Summary of Reporting Performance

Technique	Description	Use Case
Limit Large Datasets	Fetch only relevant data using filters.	Avoid unnecessary row retrieval.
Optimize Report Definitions	Use indexed columns and minimal columns.	Faster execution of queries.
Database Indexes	Add indexes to frequently queried properties.	Improve filtering and sorting speed.
Declarative Reports	Precompute data into index tables.	Query embedded or complex data faster.
Monitoring Tools	Use PDC and PAL to identify slow reports.	Optimize slow-running reports.

6.4 Business Intelligence Exchange (BIX)

The Business Intelligence Exchange (BIX) is a Pega feature that enables the extraction of application data for external reporting and analytics tools. BIX allows you to export structured data from the Pega database to external systems in formats like XML, CSV, or database tables.

6.4.1 Purpose of BIX

BIX is designed to:

1. Export Pega Data: Extract case and data object information for use in external BI tools.
2. Support External Analytics: Enable integration with reporting tools like Tableau, Power BI, or SQL-based dashboards.
3. Enable Historical Analysis: Store extracted data in external databases for historical and trend analysis.
4. Scheduled or On-Demand Exports: Configure BIX to run automatically on a schedule or manually as needed.

6.4.2 Key Features of BIX

1. Multiple Export Formats:

   • CSV: Simple and lightweight format for tools like Excel.
   • XML: Structured format for integration with other systems.
   • Database Tables: Export data directly into external relational databases.

2. Scheduled vs. On-Demand Exports:

   • Schedule data extraction jobs to run periodically (e.g., daily, weekly).
   • Trigger exports manually as needed.

3. Incremental Extraction:

   • Extract only data that has changed since the last export to optimize performance.

4. Extensive Filtering:

   • Apply filters to include only specific records or properties in the export.

5. Integration with ETL Tools:

   • Exported data can be fed into external ETL (Extract, Transform, Load) tools like Talend, Informatica, or middleware solutions.

6.4.3 BIX Data Extraction Flow

Here is a step-by-step explanation of how BIX works:

1. Identify Source Data:

   • Select the Pega classes or case types containing the data to extract.

2. Configure BIX Rules:

   • Define an Extract Rule to specify:
     • Properties to export.
     • Export format (CSV, XML, or Database).
     • Filters to limit the records.

3. Export Data:

   • Run the BIX Extract Rule manually, or configure it to run on a schedule.

4. Store Data Externally:

   • Export data into:
     • Flat files (CSV/XML).
     • External databases (via JDBC connections).

5. Load into Reporting Tools:

   • Use tools like Tableau or Power BI to create dashboards and reports using the extracted data.

6.4.4 Steps to Configure BIX

1. Create an Extract Rule

1. Go to Dev Studio → Create → SysAdmin → Extract Rule.
2. Configure the following:
   • Class: Specify the data class or case type to extract data from (e.g., Work-LoanApplication).
   • Properties: Select the properties (fields) to include in the export.
   • Format: Choose the output format: CSV, XML, or Database Table.
   • Filters: Add conditions to limit the records being exported.

2. Specify the Destination

• For CSV/XML: Specify a file path or location where the data will be stored.
• For Database: Configure the target database connection (e.g., Oracle, SQL Server).
  • Use a JDBC connection to connect to the external database.

3. Run the BIX Extract Rule

1. Manual Execution:
   • Run the Extract Rule manually from Dev Studio.
2. Schedule the Export:
   • Configure a Job Scheduler or a Job in Admin Studio to run the extract rule periodically (e.g., every night at 2 AM).

4. Verify Exported Data

• Validate that the exported data matches the required fields, format, and filters.
• Test the integration with external reporting tools like Tableau or Power BI.

6.4.5 Example: Exporting Loan Applications for Tableau

Scenario: A business team wants to analyze loan application data using Tableau for external reporting.

Steps:

1. Create Extract Rule:

   • Class: Work-LoanApplication
   • Properties: LoanID, CustomerName, LoanAmount, LoanStatus, SubmissionDate.
   • Format: CSV.
   • Filters: LoanStatus = “Approved”.

2. Configure Destination:

   • Output Path: /data/exports/loanapplications_approved.csv.

3. Schedule the Export:

   • Use a Job Scheduler to run the Extract Rule daily at 2 AM.

4. Load into Tableau:

   • Tableau reads the exported CSV file from the specified location.
   • Business users create reports and dashboards based on the loan application data.

Result:

Loan ID	Customer Name	Loan Amount	Loan Status	Submission Date
001	John Doe	$50,000	Approved	2024-06-01
002	Mary Smith	$75,000	Approved	2024-06-02

The data can now be visualized in Tableau as charts, graphs, or summary reports.

6.4.6 Incremental Data Extraction

What is Incremental Extraction?

Incremental Extraction enables BIX to export only the records that have changed since the last extraction. This reduces the amount of data exported and improves performance.

How to Enable Incremental Extraction

1. Configure the "Last Updated" column (e.g., pxUpdateDateTime) in the Extract Rule.
2. BIX will extract only records where pxUpdateDateTime > Last Export Date.

Example:

• Run an incremental extraction daily to fetch only new or updated loan applications.

6.4.7 Best Practices for BIX

1. Select Only Necessary Properties: Export only the data fields required for reporting to avoid large files.
2. Use Incremental Extraction: Minimize data volume by exporting only new or updated records.
3. Optimize Scheduling: Schedule BIX jobs during off-peak hours to avoid performance impact.
4. Test Exported Data: Verify the accuracy of data before integrating with external BI tools.
5. Monitor Performance: Track export job execution times and optimize as needed.

Summary of BIX

Feature	Description
Formats	CSV, XML, Database Tables
Scheduled/Manual	Run exports manually or schedule them
Incremental Extraction	Export only new/updated records
Integration	Use Tableau, Power BI, or ETL tools
Use Cases	External reporting, trend analysis

Conclusion of Reporting Design

We have now completed all key topics in Reporting Design:

1. Report Definitions: Retrieve and filter data using columns, sorting, and filters.
2. Report Types: Generate List Reports, Summary Reports, and Charts.
3. Reporting Performance: Optimize reports by limiting data, using indexes, and monitoring queries.
4. Business Intelligence Exchange (BIX): Export Pega data for external reporting tools like Tableau and Power BI.

Reporting Design (Additional Content)

1. Report Definitions – SQL Bridge and Technical Foundations

Clarification: Report Definition and pzQueryBuilder

A Report Definition in Pega isn't just a user interface configuration — it's a powerful tool that generates SQL queries under the hood via an internal engine called pzQueryBuilder.

Why This Matters

• The pzQueryBuilder engine translates your visual report configuration (columns, filters, grouping, etc.) into an executable SQL statement targeting the Pega database.

• Understanding this helps developers optimize reports by predicting how filters, joins, and aggregates behave in SQL — crucial for performance.

LSA Relevance

• You might be asked to analyze SQL output, debug slow reports, or optimize Report Definitions without writing raw SQL — knowing that pzQueryBuilder builds the query logic is essential.

2. Report Types – Summary Reports, Drill Down, and Structure

Clarification: Summary Reports are Extensions of List Reports

Although presented as separate types, Summary Reports are actually an enhanced form of List Reports with additional grouping and aggregation features (SUM, AVG, COUNT, etc.).

Key Insight

• A Summary Report starts with a base List Report structure, then applies grouping logic and aggregate functions on top of it.

Feature	List Report	Summary Report
Tabular data	Yes	Yes
Grouping	No	Yes
Aggregation (e.g., SUM)	No	Yes

Drill Down Capability

• Summary Reports can be configured with Drill Down functionality — allowing users to click on a grouped or aggregated value to open a detail-level List Report.

• This provides a hierarchical view: from high-level metrics down to individual records.

Example

• Summary: “Total loan amount by status”

• Drill-down: Click on “Approved” → See all loans with status = Approved

LSA Relevance

• Drill Down Reports are commonly used in dashboards and often feature in exam case studies involving report reuse and navigation design.

3. Reporting Performance – Debugging with Clipboard Analyzer

New Tool Insight: Clipboard Analyzer for Report Debugging

During report execution, data retrieved from the database is loaded into the Clipboard (memory). The Clipboard Analyzer, available in Dev Studio, allows developers to inspect report-related data pages and their structure.

Use Cases

• Inspect the output structure of a Report Definition (especially D_ prefixed pages)

• Validate whether grouping and aggregation results are structured correctly

• Identify performance bottlenecks related to large result sets or unnecessary data fetching

LSA Tip

• In large-scale applications, you're often asked to diagnose reports that:

  • Timeout

  • Return incorrect structures

  • Overload memory

Understanding how to trace and analyze report execution on the clipboard is key to providing architectural guidance.

4. Business Intelligence Exchange (BIX) – Positioning and Usage Differences

Clarification: BIX vs Traditional Extracts

While both BIX (Business Intelligence Exchange) and standard extract functions retrieve data, their purpose and implementation differ.

Feature	Traditional Extracts	BIX
Use Case	Real-time or UI-driven data export	Scheduled batch export for reporting
Format Support	CSV, Excel via UI	CSV, XML, Relational DB
Scale	Small to moderate	Enterprise-grade large-scale exports
Integration Target	Users or UI download	BI systems like Tableau, Power BI
Filtering/Incremental Support	Limited	Extensive filtering + incremental logic

When to Use BIX

• Nightly data extraction for analytics

• External warehousing for historical analysis

• Scheduled export to downstream systems

LSA Perspective

• You may be asked to design a reporting strategy for enterprise analytics.

• Knowing when to recommend BIX over UI extracts — especially for offline, high-volume, or scheduled integration — is essential.