Queries based on generated or reformatted data

A query requirement is to produce a trend report in which the chart shows the number of cases that the system creates each day as well as the number of cases that users resolve each day. 

A working assumption for this scenario is that cases might take longer than one day to complete. 

The following figure shows a Trend Report chart that displays the number of cases that were created on a given day and the number of cases resolved on a given day. This report requires two records. Each record requires a WhenOccurred property as an EventType property. The two values for EventType are New and Resolved.

Let us discuss various possible design approaches, its pros and cons

• Work pool: You might want to define a trend report against the work pool class of the analyzed cases, but this approach is not correct. A trend report requires a date against which to plot the two results, such as the number of cases created and the number of cases resolved. You cannot use either pxCreateDateTime or pyResolvedTimeStamp as the plotting date because then the report counts cases that are resolved days later based on the day of the case creation, or cases that are created days before they are resolved are counted when the case is resolved. 
• History table: You can query case history to identify case creation and case resolution events. While you can use History-Work descendent classes to identify case creation and case resolution events, this approach is complex. Because the history table contains numerous rows that contain other types of information as well. Applying filter against such huge records causes performance degradation. So this approach is not recommended.  
• Custom data table: As opposed to searching for case creation and resolution events within case history, you can define a separate data type, for example, Data-SimpleCaseHistory.
  In this case, EventType accepts a minimum number of allowed values, for example, Create and Resolve. You can define a trend report against this table by itself. Alternatively, you can join the work pool class to this table using pyID = CaseID. Either way, each EventType is plotted against the WhenOccurred DateTime value. You can retroactively generate data instances within this table: 
  • String CaseID
  • String CaseType
  • String EventType
  • DateTime WhenOccurred
• Timeline data table: You can use a another approach to define and populate a timeline table that contains the dates against which to plot. 
  You populate this table with data as far into the past and the future as needed. Other trend reports can use the same table.
  However, because you cannot define a JOIN based on the result of an SQL function such as day(), CreateDate, and ResolveDate Date, you add and expose properties within the work pool table. You also index those database columns. The query that uses the timeline table requires two subreports, one that selects and counts rows where the CreateDate value matches a given TimeLine Date value, and one that selects and counts the number of rows where the ResolveDate value matches the same TimeLine Date value. Few of the columns includes
  • Data-Timeline
  • Date Date
  • TrueFalse IsWeekStart
  • TrueFalse IsMonthStart
  • TrueFalse IsYearStart

The timeline approach performs as well as the SimpleCaseHistory approach because you join to the work pool table twice. You can also use this approach as a list report only because each subreport performs a COUNT operation instead of the main report performing a GROUP BY aggregation, which you can chart. By using SQL, you can use the UNION operation on the results of the two subreports, but the Report Definition rule in Pega Platform™ does not support this approach. 

A prefect solution is to base the trend report on the SimpleCaseHistory class alone without joining to the work pool table. This example demonstrates the benefit of extracting data and saving that data to a different form to facilitate business intelligence.