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.

The temptation exists to define the trend report against the work pool class of the cases being analyzed but this would not be correct. A trend report requires a Date against which to plot the two results, i.e., number of cases created vs number of cases resolved. Neither pxCreateDateTime or pyResolvedTimeStamp can be used as the plotting date. If so, cases resolved days later would be counted on the day that the case was created. That or cases created days before being resolved would be counted on the day that the case was resolved.

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, doing so would be complex. Also, the history table contains numerous rows that contain other types of information.

As opposed to searching for case creation and resolution events within case history, a separate Data Type could be defined. For example:

Data-SimpleCaseHistory

String CaseID

String CaseType

String EventType

DateTime WhenOccurred

Here, the allowed values for EventType would be kept to a minimum, for example, “Create” and “Resolve”.

A trend report could be defined against this table by itself. Or, the work pool class could joined to this table using pyID = CaseID. Either way, each EventType would be plotted against the truncation of the WhenOccurred DateTime value to a Date value. Data instances within this table can be generated retroactively.

A different solution is to define and populate a Timeline table that contains the Dates to plot against.

Data-Timeline

Date Date

TrueFalse IsWeekStart

TrueFalse IsMonthStart

TrueFalse IsYearStart

This table would need to be populated as far into the past and into the future as would be 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 would need to be added and exposed within the work pool table. Those database columns would also need to be indexed. The query using the Timeline table would require two subreports, one selecting and counting rows where the CreateDate matches a given TimeLine Date. The second subreport would select and count the number of rows where the ResolveDate matches the same TimeLine date.

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

The ideal solution would be 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.