This example digests the histories of Case Management cases so key events can be displayed on a dashboard. It's built from the "reporting" histories written by Case Management, which contain no personal data and can be kept for long term reporting.
The Histories
The histories that will be digested have the following structure. This example is from the Complaint case type.
{
"labela": "Case Management - Complaint",
"labelb": "1425-5476-1020-9732",
"labelc": "reporting",
"labeld": null,
"labele": null,
"created": 1668780556437,
"sealed": false,
"subject": null,
"lastupdated": 1668781699210,
"events": [{
"pos": [1, 7],
"event": {
"isAssisted": false,
"targetDate": "2022-11-25T14:09:15.506Z",
"tasksToDo": 0,
"language": "en",
"breachDate": "",
"escalationDate": "",
"escalationReason": "",
"isCalendarDays": false,
"caseType": "complaint",
"closeDate": "",
"tasksByType": "",
"caseSubType": "",
"caseStage": "",
"tasksCount": 0,
"openDate": "2022-11-18T14:09:13Z",
"closeReason": "",
"reminderDate": "2022-11-24T14:04:15.506Z",
"assignReason": ""
},
"timestamp": 1668780556437
}, {
"pos": [2, 7],
"event": {
"candidateGroup": "CM-COMPLAINT-MANAGER"
},
"timestamp": 1668780560890
}, {
"pos": [3, 7],
"event": {
"private": false,
"Category": "Employee",
"Service_improvement": "",
"Justified": "Yes",
"description": "Form: [Complaints additional details V1 EN] submitted.",
"proxyUserId": null,
"event": "Form Submission",
"userRole": null
},
"timestamp": 1668780907403
}, {
"pos": [4, 7],
"event": {
"stageChange": "s1,2022-11-18T14:15:52.404Z",
"caseStage": "s1"
},
"timestamp": 1668780952647
}, {
"pos": [5, 7],
"event": {
"breachDate": "2022-11-18T14:16:52.404Z"
},
"timestamp": 1668780963982
}, {
"pos": [6, 7],
"event": {
"statusChange": "Stage 1 resolved,2022-11-18T14:21:21.723Z"
},
"timestamp": 1668781281940
}, {
"pos": [7, 7],
"event": {
"closeDate": "2022-11-18T14:28:15.506Z",
"closeReason": "Resolved"
},
"timestamp": 1668781699210
}]
}
It has the following events.
| Event Position | Description |
|---|---|
| 1 | Written when the case is started, this holds core information, like the target SLA, case type and subtype |
| 2 | The candidate group of case managers |
| 3 | The "additional details" form has been completed |
| 4 | The case stage was changed |
| 5 | The case exceeded its SLA |
| 6 | The case status was changed |
| 7 | The case was closed |
The Digest Filter
This filter targets any histories with a labela that starts "Case Management" and
"filter": {
"AND": [{
"key": "labela",
"GT": "Case Managemens"
}, {
"key": "labela",
"LT": "Case Managemenu"
}, {
"key": "labelb",
"NEQ": null
}, {
"key": "labelb",
"NEQ": 'retention'
}, {
"key": "labelc",
"EQ": "reporting"
}, {
"key": "labeld",
"EQ": null
}, {
"key": "labele",
"EQ": null
}]
}
Event Filter
The digest includes a top-level event filter. This restricts the histories that will be considered for the digest, excluding any that have an event with a key that gets written when one case is converted to another (to avoid inflating the case numbers).
"eventFilter": {
"NOT": {
"KEYEXISTS": "initLabelA"
}
}
The Digest Columns
The following columns will make up the new database table.
"columns": [{
"name": "Process",
"type": "varchar",
"size": 100,
"operation": "label",
"key": "labela"
}, {
"name": "BusinessKey",
"type": "varchar",
"size": 50,
"operation": "label",
"key": "labelb"
}, {
"name": "Started",
"type": "datetime",
"operation": "firsttimestamp"
}, {
"name": "TargetSLA",
"type": "varchar",
"size": 50,
"operation": "lastvalue",
"key": "targetDate",
"eventFilter": {
"KEYEXISTS": "targetDate"
}
}, {
"name": "Breached",
"type": "varchar",
"size": 50,
"operation": "lastvalue",
"key": "breachDate",
"eventFilter": {
"KEYEXISTS": "breachDate"
}
}, {
"name": "Closed",
"type": "datetime",
"operation": "lastvalue",
"key": "closeDate",
"eventFilter": {
"key": "closeDate",
"GT": ""
}
}, {
"name": "CloseReason",
"type": "varchar",
"size": 500,
"operation": "lastvalue",
"key": "closeReason",
"eventFilter": {
"key": "closeReason",
"GT": ""
}
}, {
"name": "Assisted",
"type": "boolean",
"operation": "firstvalue",
"key": "isAssisted"
}, {
"name": "CandidateGroup",
"type": "varchar",
"size": 500,
"operation": "lastvalue",
"key": "candidateGroup",
"eventFilter": {
"KEYEXISTS": "candidateGroup"
}
}, {
"name": "CaseType",
"type": "varchar",
"size": 50,
"operation": "lastvalue",
"key": "caseType",
"eventFilter": {
"KEYEXISTS": "caseType"
}
}, {
"name": "CaseSubType",
"type": "varchar",
"size": 100,
"operation": "lastvalue",
"key": "caseSubType",
"eventFilter": {
"KEYEXISTS": "caseSubType"
}
}, {
"name": "CaseStage",
"type": "varchar",
"size": 100,
"operation": "lastvalue",
"key": "caseStage",
"eventFilter": {
"KEYEXISTS": "caseStage"
}
}]
| Column Name | Description |
|---|---|
| Process | The labela value, which is the case type |
| BusinessKey | The labelb value, which is the unique business key of the case |
| Started | The timestamp of the first event, written when the case was raised |
| TargetSLA | This column uses an event filter to find events that have a targetDate, and uses the lastvalue operation to get the last value of that key. We fetch the last value as SLAs can be manually updated during a case (in this example the SLA wasn't updated, so the value from the first event will be used) |
| Breached | Similar to the TargetSLA, uses an event filter to get the value of the breachDate key, which holds the date/time that the case exceeded its SLA |
| Closed | When the case was closed - the last value of the closeDate key |
| CloseReason | The reason that accompanied the case close |
| Assisted | Whether or not the case was raised using Assisted Service, this key is stored in the first event |
| CandidateGroup | The candidate group for the case, stored in the candidateGroup key |
| CaseType | The case type. The lastvalue operation is used as case types can be changed |
| CaseSubType | The case subtype. The lastvalue operation is used as case subtypes can be changed |
| CaseStage | The case stage. The lastvalue operation is used as we are interested in what stage the case was at when it was closed |
The Full End Point
You can download and see the full End Point script (Javascript) [7KB]. I've not included a full export of the End Point as the example is part of the Case Management product and importing it would overwrite the product version.
The End Point is called by a Scheduled Tasks every five minutes. First it checks to see whether or not the digest exists. If it doesn't it registers a new digest. It then performs the digest.
The Database View
The following view is created (click on the image to see a larger version), which can be queried to populate a dashboard or be read by further End Points to pass data to other reporting software.
Share this page
