Queries based on generated or reformatted data

Suppose there is a requirement to produce a trend report where the chart shows the number of cases created each day as well as the number of cases resolved each day.

Assumption: Cases may take longer than one day to complete.

• Work pool: The temptation exists to define the trend report against the work pool class of the analyzed cases, but this is not correct. A trend report requires a Date against which to plot the two results (such as the number of cases created versus the number of cases resolved. Neither pxCreateDateTime nor pyResolvedTimeStamp can be used as the plotting date. If so, cases resolved days later are counted on the day of the case creation. Or, cases created days before they are resolved are counted when the case is resolved.
• History table: An attempt could be made to query case history to identify case creation and case resolution events. While it is possible to use History-Work to identify case creation and case resolution events, it is complex. Also, the history table contains numerous rows that contain other types of information.
• 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
  • String CaseID
  • String CaseType
  • String EventType
  • DateTime WhenOccurred

  Here, the allowed values for EventType are kept to a minimum, for example, “Create” and “Resolve”. A trend report can be defined against this table by itself. Or, the work pool class can be joined to this table using pyID = CaseID. Either way, each EventType is plotted against the WhenOccurred DateTime value's truncation to a Date value. Data instances within this table can be generated retroactively.

• Timeline Data table: A different solution is to define and populate a Timeline table that contains the Dates against which to plot.
  • Data-Timeline
  • Date Date
  • TrueFalse IsWeekStart
  • TrueFalse IsMonthStart
  • TrueFalse IsYearStart

  This table needs to be populated as far into the past and the future as needed. Other trend reports could leverage the same table.

  However, because it is not possible to define a JOIN based on the result of a SQL Function such as day(), CreateDate, and ResolveDate Date, properties need to be added and exposed within the work pool table. Those database columns also need to be indexed. The query using the Timeline table requires two subreports, one selecting and counting rows where the CreateDate matches a given TimeLine Date. The second subreport selects and counts the number of rows where the ResolveDate matches the same TimeLine date.

Conclusion

The Timeline approach is as performant as the SimpleCaseHistory approach due to having to join to the work pool table twice. It is also used only as a List report since each subreport performs a COUNT instead of the main report performing a GROUP BY aggregation, which can be charted. Using SQL, it is possible to UNION the result of the two subreports, but Pega’s Report Definition rule does not support this.

The ideal 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 persisting it to a different form to facilitate business intelligence.