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.
- Description of Pega 7 Sample Application
- Description of the Data Model of the Pega 7 Sample Application
- Viewing Instances of a Case Type Rule in the Pega 7 Designer Studio
- Creating a List-Type Report for a Case Type Rule in Pega 7
- Optimizing Properties for Reporting in Pega 7
- Adding Filter Criteria to a List-Type Report in Pega 7
- Using a Class Join in a List-Type Report in Pega 7
- Creating a Summary-Type Report in Pega 7
- Creating a Bar Chart in Pega 7
- Creating a Map Chart in Pega 7
A sample application has been created for this post to illustrate how to create reports.
- The application defines a case type rule called
Autothat contains 3 stages.
Autocase represents the process of generating auto insurance quotes based on provided customer data.
The 3 stages of the
Auto case type are:
Customer– Collect customer information such as name and address.
Vehicle– Collect vehicle information such as year, make and model.
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
- The work item status is stored in a Pega standard property called pyStatusWork
- This property is inherited from the
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
Pendingat 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.
Stage 3: Show Quote
- The application uses the collected information to calculate a 6 and a 12 months insurance quote.
- The insurance quotes are displayed and the work item status is set to
Resolved-Completedwhen 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
- For example, the
ABC-Insurance-Data-Customerclass derives from
Data-Customer, which provides the properties
- It is a Pega best practice to extend out-of-the-box classes and to reuse properties when possible.
ABC-Insurance-Work-Autoclass has 2 single page type properties:
ABC-Insurance-Work-Vehicleclass has 1 single page type property that refers to
-Modelclass has a linked class key reference to
-Makefor referring to a car make.
- For more information on how to create linked class key references and cascading drop-downs, see: Example of Cascading Drop Downs with Linked Class Key Property in Pega 7
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.
- This action will execute a default list view on the
ABC-Insurance-Work-Autoclass, showing all instances that have been created.
- This list view is defined on the
Work-class and is named
- For this example,
32instances of the
ABC-Insurance-Work-Autocase type rule have been created in the system:
- This view is NOT a report definition. It is an out-of-the-box list view defined on the
- 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.
- See Example of Connecting to Pega 7 PRPC PostgreSQL DB with pgAdmin for more information.
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.
- 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.
- 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:
pyID– the work item ID such as
A-47– see pyID on Pega Help
pzInsKey– the instance handle key such as
ABC-Insurance-Work A-47– see pzInsKey on Pega Help
pxCurrentStageLabel– the name of the current stage such as
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
- Note: The references to the
Vehicle.VehicleIdentificationNumbercause 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.
- At this point, the report definition does not have any filter criteria defined and will retrieve all
- The report definition can be run by clicking on Action > Run. The results will be shown in a new window:
- 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.
- The column format can be
Display Textor 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
pxCoveredCountfor keeping track of sub-cases
py– Pega common out-of-the-box properties for reuse, such as
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.Lastnameare 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.
- 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-Autoand the table name is
Nowfor the Population Schedule at and click on Next to continue.
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.
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.
Nowwas 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.
- A look at the Pega DB table
pc_ABC_Insurance_Workshows that a new column named
- 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
Vehicle.VehicleIdentificationNumberproperties (…which was optimized in the same way).
- 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
- Using this unoptimized property in a filter condition causes 2 warning messages as shown below:
- 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.Yearas 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:
- The parameter can be referenced in the filter criteria on the Query tab, instead of using a fixed value.
- 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
- Click on Apply Changes to refresh the report view.
- If multiple filter criteria are defined on the Query tab, boolean logic can be used to evaluate the filter criteria.
- In the above example, the filter condition is set to
A AND Bso 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 = 2016and
State = California.
- 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
Autocase 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
Autocase type rule has a single page property called
Vehiclewith a page definition of
ABC-Insurance-Data-Vehiclewhich has a single page property called
Modelfor storing the car model.
- The page definition of the
ABC-Insurance-Data-Modeland this class has a property called
makewhich refers to class keys of
- 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
makeproperty is a foreign key to car makes.
- When an
Autocase type instance is populated with data, the
Modelproperty stores the key of the car model in
Model.idand the key of the make in
- The actual names such as
"Accord"are NOT stored in the
- The make and model keys and actual names are stored in data tables as shown below.
- For example, the car model instance
MO014refers to the make instance,
M-4with a value of
- 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.
- Running the report shows California customers and the make and model keys of their vehicles.
- 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.
- Click on the button Edit conditions and configure the filter condition to link the
Vehicle.Model.makeproperty to class keys in the
- Another class join is added the same way to retrieve model names:
- The filter condition links the
.idproperty in the
ABC-Insurance-Data-Modelclass which is prefixed with
ModelTablein the class joins section.
- 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
pyLabelthat holds the actual make or model name.
- Running the report now shows the actual make and model names instead of class keys:
- 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
Autocase type instances for each U.S. state.
- A new report definition called
AutoCaseSummaryReportwas created. The Query tab of the summary-report definition contains 2 columns as shown below:
Countin the SUMMARIZE drop-down, results in a GROUP BY query that will count the number of
pzInsKeyvalues for each value of
- When the report is run, it will show one row for each U.S. State and the number of
Autocase type instances for each one.
- Similarly, a summary-type report can be used to show the premium totals (i.e. auto insurance policy revenue) for each state. Here, the
Sumoption is selected in the SUMMARIZE drop-down.
- Note: The second column has been configured (…click on gear icon) to use a currency formatter.
- 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
Statecolumn needs to be dragged to the x-axis so that a bar is shown for each U.S. state.
- The gear icon next to the chart’s column names can be used to configure formatting:
- For the
Total Premiumscolumn, the default US
Currencyformat is selected and configured to not display any decimal places.
- 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 Premiumcolumn values. In the below example, three conditional colors have been defined:
- The chart can be viewed by running the report definition via Actions > Run.
- When hovering over a bar, a tool tip shows the actual value. The tool tip section can be customized as well.
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.
- In the Select Chart Type modal dialog, click on the Map icon.
- The main Chart tab area will show a symbolic map with the
Total Premiumscolumn on the y-axis and the
Statecolumn 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
Statecolumn values have not been properly mapped to the map chart values for U.S. states.
- 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:
- Navigate to Reporting > Settings > Map in order to configure the map for U.S. states:
- On the map settings page, select the
USA By Statemap 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).
- 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.
- 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.