Example of How to Create Report Definitions in Pega 7

This post shows how a Report Definition rule can used to create reports for case type rules. Common use cases include list-type reports that show case instances and summary-type reports on data gathered during case processing, such as summarizing customer related data such as a chart showing how many customers were serviced in each U.S. state. Pega 7.1.6 was used for all examples.

Summary

  1. Description of Pega 7 Sample Application
  2. Description of the Data Model of the Pega 7 Sample Application
  3. Viewing Instances of a Case Type Rule in the Pega 7 Designer Studio
  4. Creating a List-Type Report for a Case Type Rule in Pega 7
  5. Optimizing Properties for Reporting in Pega 7
  6. Adding Filter Criteria to a List-Type Report in Pega 7
  7. Using a Class Join in a List-Type Report in Pega 7
  8. Creating a Summary-Type Report in Pega 7
  9. Creating a Bar Chart in Pega 7
  10. Creating a Map Chart in Pega 7

Related Posts

A sample application has been created for this post to illustrate how to create reports.

  • The application defines a case type rule called Auto that contains 3 stages.
  • The Auto case represents the process of generating auto insurance quotes based on provided customer data.

Case Designer - Sample application with three stages

The 3 stages of the Auto case type are:

  1. Customer – Collect customer information such as name and address.
  2. Vehicle – Collect vehicle information such as year, make and model.
  3. Quote – Show a 6 month and 12 month quote that is calculated based on the data provided.

When running the Auto case in the Case Manager, the user navigates through these 3 stages and completes the forms shown in the following screen shots to add data to the case instances. An auto insurance quote is generated –based on the collected data– by the application in the last stage.

Stage 1: Collect Customer Information

  • The user collects the customer’s information such as name, age, address and email.
  • At this, point the status of the work item is set to Open.
  • The work item status is stored in a Pega standard property called pyStatusWork
  • This property is inherited from the Work- class.

Case Manager Portal - Create new Auto case, Customer stage

Stage 2: Collect Vehicle Information

  • The user collects information on the vehicle for which to provide an insurance quote.
  • The vehicle information includes the vehicle identification number, year, make, model and so on.
  • The status of the work item is set to Pending at this stage.
  • See Example of Cascading Drop Downs with Linked Class Key Property in Pega 7 for information on how to implement cascading drop-downs for the Make and Model controls.

Case Manager Portal - Create new Auto case, Vehicle stage

Stage 3: Show Quote

  • The application uses the collected information to calculate a 6 and a 12 months insurance quote.

Case Manager Portal - Create new Auto case, Quote stage

  • The insurance quotes are displayed and the work item status is set to Resolved-Completed when the user clicks on Submit.

The class diagram below shows the data model of the Auto case type rule.

  • The classes highlighted in blue are application specific classes that derive from the out-of-the-box abstract Pega classes Data- and Work-Cover.
  • For example, the ABC-Insurance-Data-Customer class derives from Data-Customer, which provides the properties pyCustomerID and pyEmailAddress.
  • It is a Pega best practice to extend out-of-the-box classes and to reuse properties when possible.
  • The ABC-Insurance-Work-Auto class has 2 single page type properties: Customer and Vehicle.
  • The ABC-Insurance-Work-Vehicle class has 1 single page type property that refers to ABC-Insurance-Work-Model.
  • The -Model class has a linked class key reference to -Make for referring to a car make.

Sample Application Data Model UML_Class_Diagram

The Designer Studio can be used to view all instances of a given case type rule.

  • Open the Application Explorer and right click on the case type rule.
  • In the context menu, hover over View and then click on Instances.

Application Explorer View Case Type Instances

  • This action will execute a default list view on the ABC-Insurance-Work-Auto class, showing all instances that have been created.
  • This list view is defined on the Work- class and is named InstanceList ALL.
  • For this example, 32 instances of the ABC-Insurance-Work-Auto case type rule have been created in the system:

Designer Studio View Of Case Type Instances with InstanceList ALL

  • This view is NOT a report definition. It is an out-of-the-box list view defined on the Work- class.
  • The use of custom list views is discouraged and report definitions should be used instead.
  • Alternatively, a tool such as pgAdmin for PostgreSQL can be used to look at the Pega database directly.

Using pgAdmin to view case type instances

In order to create a new list-type report definition:

  • Navigate to the Application Explorer and right click on the case type rule.
  • Select Create+ in the context menu and then Reports > Report Definition.

Designer Studio Create Report Definition from Application Explorer menu

  • In the Create Report Definition form, enter a name for the report definition, here AutoCasesListReport, and select the appropriate app layer and rule set version.
  • The Apply to drop-down should be populated properly with the class name of the case type rule.

Designer Studio Create Report Definition Form

  • Click on Create and open to continue.
  • A simple list-type report can quickly be created by adding columns on the Query tab.
  • In the below example, the following columns have been added:
pyIDthe work item ID such as A-47 – see pyID on Pega Help
pzInsKeythe instance handle key such as ABC-Insurance-Work A-47 – see pzInsKey on Pega Help
pxCurrentStageLabel – the name of the current stage such as Vehicle
pyStatusWork – the work status such as Resolved-Completed – see pyStatusWork on Pega Help
Customer.Lastname – embedded single page of type Customer – see data model in section 2
Vehicle.VehicleIdentificationNumber – embedded page of type Vehicle – see data model in section 2

Designer Studio Edit Report Definition Form - Query Tab

  • Note: The references to the Customer.Lastname and Vehicle.VehicleIdentificationNumber cause a warning message because these properties have not been optimized (see next section for optimization). Clicking on the warning shows more information.
  • Performance: For each result item, PRPC has to decode the BLOB in order to display these properties. This can result in poor performance when processing large result sets.

Designer Studio Edit Report Definition Form - Warning Display Property not optimized

  • At this point, the report definition does not have any filter criteria defined and will retrieve all 32 instances.
  • The report definition can be run by clicking on Action > Run. The results will be shown in a new window:

Designer Studio Edit Report Definition Form - Run report in Report Viewer

  • The width and format of each column can be adjusted by clicking on the gear icon next to a column definition on the Query tab of the report definition.
  • A new dialog allows to specify the width in pixels or as a percentage.

Designer Studio Edit Report Definition Form - Query tab set column formats

  • The column format can be Display Text or other default formats such as number-, percentage or currency formats. Custom formats can be created as well.

In Pega, there are standard properties and custom properties (created by developers). The standard properties have one of the following three prefixes:

px – Special properties whose values are set by PRPC, such as pxCoveredCount for keeping track of sub-cases
py – Pega common out-of-the-box properties for reuse, such as Data-Customer.pyEmailAddress
pz – Used for internal system processing, such as pzInsKey, can be read but not set by the application
  • The standard properties are stored in separate columns in the Pega system database.
  • Custom properties created by users such as the above Customer.Lastname are stored by default in a BLOB type column called pzPVStream. This DB column is also called the storage stream.
  • In order to create performance efficient reporting, custom properties that are part of a report definition need to be optimized.
  • The process of optimizing a property is done in the Designer Studio and causes the creation of a dedicated DB column in the table of the respective class.
  • When a report is executed, PRPC uses the dedicated column instead of examining the storage stream.

Running the Property Optimization Wizard

  • In Designer Studio, navigate to the Application Explorer.
  • Expand the Data Model node of the relevant class and then expand the Property node.
  • Right click on the property that should be optimized and select Optimize for reporting.

Designer Studio - Applicaiton Explorer - Optimize property for reporting

  • The Property Optimization wizard will be launched and guide the user through 3 steps.

Step 1: Properties and Classes

  • On the first step, the property that is to be optimized and the affected class and its Database table are shown. In this example, the class is ABC-Insurance-Work-Auto and the table name is pc_ABC_Insurance_Work in the PegaDATA database, containing 32 instances.
  • Select Now for the Population Schedule at and click on Next to continue.

Designer Studio - Property Optimization Wizard - Properties and Classes Form

Step 2: Properties and Classes

  • Property optimization will cause Pega to create a new column in the table of the class that contains the optimized property.
  • After optimization, the property values will be stored in a new dedicated DB column instead of in the BLOB (see storage stream). Click on Next to start the optimization.

Designer Studio - Property Optimization Wizard - Eligible Classes Form

Step 3: Optimization

  • A confirmation message should indicate that the Optimization started successfully.
  • Clicking on the button Column Population Jobs Dashboard shows the status of the optimization process.

Designer Studio - Property Optimization Wizard - Optimization Form

  • When Now was selected for the population schedule in step 1, the optimization should complete quickly. In the below screen shot, the status Completed indicates that the propery was successfully optimized.

Designer Studio - System-Database - View Column Population Jobs

  • A look at the Pega DB table pc_ABC_Insurance_Work shows that a new column named lastname_1 was created.
  • See this post on how to access the Pega database using a tool such as pgAdmin.
  • The below screen shot shows the 32 instances in the table and the new columns for the  Customer.Lastname and Vehicle.VehicleIdentificationNumber properties (…which was optimized in the same way).

pgAdmin PostgreSQL Peag database - View work object instances

  • When opening the report definition again, the warning message about the properties not being optimized is not shown anymore.

Optimizing is highly recommended for properties that are used in report definition filter criteria. In the below screen shot, a report column and filter condition has been added to query Auto case type instances by model year using Vehicle.Year, so that only instances are retrieved where the model year matches 2016.

Designer Studio - Edit Report Definition - Add Filter Criteria

  • Using this unoptimized property in a filter condition causes 2 warning messages as shown below:

Designer Studio - Edit Report Definition - Add Filter Criteria - Property Optimization Warnings

  • Performance Severe: Using an unoptimized property for a filter criteria causes very poor performance, because PRPC has to decode the BLOB of every row in the DB table in order to examine the property value.
  • Performance Caution: Displaying an unoptimized property in a report may result in poor performance, because PRPC has to decode the BLOB of every item in the result set to extract the property value.

Using Report Definition Parameters

  • After optimizing Vehicle.Year as shown in the previous section, the warnings will disappear.
  • In order to make the report definition more flexible, input parameters should be defined.
  • Switch to the Parameters tab of the report definition rule and add the desired input parameters, for example:

Designer Studio - Edit Report Definition - Add Input Parameters

  • The parameter can be referenced in the filter criteria on the Query tab, instead of using a fixed value.

Designer Studio - Edit Report Definition - Reference Input Parameters on Query Tab

  • The report definition can be run by clicking on Actions > Run.
  • To set a filter value, click on the Filters link in the upper right-hand corner of the Report Viewer.
  • This expands a section that allows to specify the filter value for the model year. Here it is 2016.
  • Click on Apply Changes to refresh the report view.

Designer Studio - Run Report Definition - Set Filter Criteria in Report Viewer

  • If multiple filter criteria are defined on the Query tab, boolean logic can be used to evaluate the filter criteria.

Designer Studio - Edit Report Definition - Multiple Filter Criteria with Boolean Logic

  • In the above example, the filter condition is set to A AND B so that a logical AND is used to select rows where the model year and state match specified parameter values.
  • The below screen shot shows the query results in the Report Viewer when setting the filter criteria to Model Year = 2016 and State = California.

Designer Studio - Run Report Definition - Set Multiple Filter Criteria in Report Viewer

  • At this point, the Save As button is used to save a copy of the report definition for the creation of a new report that will show the make and model for each Auto case type instance.
  • See the data model shown in the UML class diagram in section 2. Obtaining the make and model names requires the use of class joins.

  • According to the data model described in section 2 (see UML class diagram), the Auto case type rule has a single page property called Vehicle with a page definition of ABC-Insurance-Data-Vehicle which has a single page property called Model for storing the car model.
  • The page definition of the Model property is ABC-Insurance-Data-Model and this class has a property called make which refers to class keys of ABC-Insurance-Data-Make instances.
  • Note: See Example of Cascading Drop Downs with Linked Class Key Property in Pega 7 on this blog for details on how the make and model data is stored using data tables and a linked class key property.
  • In relational database terms, the make property is a foreign key to car makes.

Designer Studio - Edit Property - Set Automatic reference to class instances (linked) under Data Access

  • When an Auto case type instance is populated with data, the Model property stores the key of the car model in Model.id and the key of the make in Model.make.
  • The actual names such as "Honda" and "Accord" are NOT stored in the Auto case instances.
  • The make and model keys and actual names are stored in data tables as shown below.
  • For example, the car model instance MO014 refers to the make instance, M-4 with a value of Mercedes-Benz.

Designer Studio - Data Tables for Car Make and Car Model

  • The below screen shot shows a modified list-type report that shows customers in California. The report includes the car make and model key values.

Designer Studio - Run Report Definition - Add columns for instance keys

  • Running the report shows California customers and the make and model keys of their vehicles.

Designer Studio - Report Viewer - Filter Criteria for U.S. State

  • Displaying the make and model class keys in a report is not useful to users, so class joins are needed to look-up the actual make and model names.
  • In the report definition, switch to the Data Access tab. Click on Add class join and set a prefix and select the class name.

Designer Studio - edit Report Definition - Data Access Tab Add Class Join for Car Make

  • Click on the button Edit conditions and configure the filter condition to link the Vehicle.Model.make property to class keys in the ABC-Insurace-Data-Make table.

Designer Studio - edit Report Definition - Data Access Tab Filter Condition for Car Make

  • Another class join is added the same way to retrieve model names:

Designer Studio - edit Report Definition - Data Access Tab Add Class Join for Car Model

  • The filter condition links the Vehicle.Model.id to the .id property in the ABC-Insurance-Data-Model class which is prefixed with ModelTable in the class joins section.

Designer Studio - edit Report Definition - Data Access Tab Filter Condition for Car Model

  • On the Query tab, the columns that refer to the make and model keys are replaced with the prefixes for the make and model data classes specified on the Data Access tab.
  • Each class has an inherited property pyLabel that holds the actual make or model name.

Designer Studio - Edit Report Definition - Query Tab - Add Columns for Car Make and Model names

  • Running the report now shows the actual make and model names instead of class keys:

Designer Studio - Report Viewer - Show Columns for Car Make and Model names

  • If a warning message indicates that a property used in a class join is not optimized, the report definition will not run properly and an error message will be shown.
  • Make sure to optimize properties used in class joins.

  • A summary-type report is used to summarize a given property. For example, to count or to obtain min. and max. values.
  • In this example, a summary-type report is used to show the number of Auto case type instances for each U.S. state.
  • A new report definition called AutoCaseSummaryReport was created. The Query tab of the summary-report definition contains 2 columns as shown below:

Designer Studio - Edit Report Definition - Summary Report - Query Tab - Customers per State

  • Selecting Count in the SUMMARIZE drop-down, results in a GROUP BY query that will count the number of pzInsKey values for each value of Customer.State.
  • When the report is run, it will show one row for each U.S. State and the number of Auto case type instances for each one.

Designer Studio - Report Viewer - Summary Report - Query Tab - Customers per State

  • Similarly, a summary-type report can be used to show the premium totals (i.e. auto insurance policy revenue) for each state. Here, the Sum option is selected in the SUMMARIZE drop-down.

Designer Studio - Edit Report Definition - Summary Report - Query Tab - Total Premiums per State

  • Note: The second column has been configured (…click on gear icon) to use a currency formatter.

Designer Studio - Report Viewer - Summary Report - Total Premiums per U.S. State

  • Summary-type reports are needed for creating bar charts and map charts.

  • A bar chart is created based on a summary-type report. For this example, the summary-type report from the previous section (which shows the insurance premium totals per U.S. state) is used.
  • The bar chart shows the U.S. states on the x-axis and the total premiums of auto policies in each state on the y-axis.
  • On the existing summary-type report definition, here called AutoCaseSummaryReport, switch to the Chart tab.
  • Click on the Include Chart button. Drag and drop the aggregate property column on the y-axis, in this case the Total Premiums column.
  • The State column needs to be dragged to the x-axis so that a bar is shown for each U.S. state.

Designer Studio - Edit Report Definition - Chart Tab - Bar Chart

  • The gear icon next to the chart’s column names can be used to configure formatting:

Designer Studio - Edit Report Definition - Chart Tab - Bar Chart Formatting

  • For the Total Premiums column, the default US Currency format is selected and configured to not display any decimal places.

Designer Studio - Edit Report Definition - Chart Tab - Bar Chart Formatting for Currency

  • The General Settings link on the Chart tab can be used to set the text size
  • The Color Settings link can be used to define conditional coloring of the bars based on the Total Premium column values. In the below example, three conditional colors have been defined:

Designer Studio - Edit Report Definition - Chart Tab - Conditional Colors for Bar Chart

  • The chart can be viewed by running the report definition via Actions > Run.

Designer Studio - Report Viewer - Bar Chart for Total Premiums per U.S. State

  • When hovering over a bar, a tool tip shows the actual value. The tool tip section can be customized as well.

The same AutoCaseSummaryReport summary-type report can be used to create a map chart to show the total premiums for each U.S. State on a geographical map.

  • Open the report definition and navigate to the Chart tab and click on the All Chart types link.
  • This link opens a new dialog that shows all available chart types.

Designer Studio - Report Definition - Chart Tab - All Chart Types

  • In the Select Chart Type modal dialog, click on the Map icon.

Designer Studio - Report Definition - Chart Tab - View All Chart Types

  • The main Chart tab area will show a symbolic map with the Total Premiums column on the y-axis and the State column on the x-axis.
  • The values of the x-axis will be mapped to the U.S. states on the map.
  • Note: At this point, a warning message will appear since the State column values have not been properly mapped to the map chart values for U.S. states.

Designer Studio - Edit Report Definition - Map Chart - Total Premiums per U.S. State

  • Clicking on the warning message will show the following modal dialog indicating that the Group By column values need to be linked correctly to the regions or areas of the selected map:

Designer Studio - Edit Report Definition - Map Chart Warning Group By column values need to be linked to map areas

  • Navigate to Reporting > Settings > Map in order to configure the map for U.S. states:

Designer Studio - Navigate to Reporting - Settings - Maps

  • On the map settings page, select the USA By State map from the Map type drop-down and specify the class.
  • In this case, the class is ABC-Insurance-Work-Auto. Once the class is selected, the table showing the MAP REGION and PROPERTY VALUE mappings should be updated automatically as shown in the screen shot below (…here the state names already match).

Designer Studio - Reporting Settings for Map USA By State

  • Click on the Save button and navigate back to the report definition for the map chart and select the correct map name in the Map drop-down control. Here, the map’s name is USA By State.

Designer Studio - Map Report Definition - Set map chart USA By State

  • Click on Save and run the map chart by clicking on Actions > Run.
  • Hovering over a U.S. state shows a tool tip section which can be customized.
  • The conditional color settings configured for the bar chart earlier, are applied to the map areas.

Designer Studio - Map Chart - Run map chart USA By State for Total Premiums per U.S. State