When you want to create Service Level Management reports you can use the few fields available on Vapp_item to tell what the ticket status is in regards to SLA compliance. The problem is that those fields will show the ticket as Breached SLA if any of the related violation thresholds has breached. So, if you miss the response time SLA your ticket will show as breached even if you meet all other thresholds.
For more accurate SLM performance indicators I would recommend to use the VSLA_AGREEMENT_COMPLIANCE table. It brings each of the thresholds that affect a given ticket with individual compliance status and time calculation. This opens up a lot of possibilities for SLM reports with more accuracy and detail.
In order to get ready for your SLM reports you will need to go to the Domain Designer and add the following table to your domain:
- VSLA_AGREEMENT_COMPLIANCE
If you are not sure about how to add the table to your domain, check these articles:
Advanced Reporting 101: Accessing the Domain Designer
Advanced Reporting 101: Adding a new Table to the Domain
Create the following joins:
| Table Name | Field Name |
|---|
| Left Table | VAPP_ITEM | ROW_ID |
|---|
| Right Table | VSLA_AGREEMENT_COMPLIANCE | ITEM_ID |
|---|
| Join Type | Left Join |
|---|
| Table Name | Field Name |
|---|
| Left Table | VAPP_ITEM | SLICE |
|---|
| Right Table | VSLA_AGREEMENT_COMPLIANCE | SLICE |
|---|
| Join Type | Left Join |
|---|
For detailed instructions on how to create a join, check this article:
Advanced Reporting 101: Creating a Join between tables
Now you need to create a few calculated fields on that table:
|
| date_created | Timestamp | EPOCH_TO_UTC_DATETIME(dbo_VSLA_AGREEMENT_COMPLIANCE.created_date) |
| date_escalation | Timestamp | EPOCH_TO_UTC_DATETIME(dbo_VSLA_AGREEMENT_COMPLIANCE.escalation_date) |
| date_last_reminder | Timestamp | EPOCH_TO_UTC_DATETIME(dbo_VSLA_AGREEMENT_COMPLIANCE.last_reminder_date) |
| date_sla_due | Timestamp | EPOCH_TO_UTC_DATETIME(dbo_VSLA_AGREEMENT_COMPLIANCE.sla_due_by) |
| date_sla_start_time | Timestamp | EPOCH_TO_UTC_DATETIME(dbo_VSLA_AGREEMENT_COMPLIANCE.sla_start_time) |
| date_modified | Timestamp | EPOCH_TO_UTC_DATETIME(dbo_VSLA_AGREEMENT_COMPLIANCE.modified_date) |
| date_ticket_created | Timestamp | EPOCH_TO_UTC_DATETIME(dbo_VSLA_AGREEMENT_COMPLIANCE.ticket_created_date) |
For detailed instructions on how to create a Calculated Field, check this article:
Advanced Reporting 101: Calculated Fields
Last thing you need to do is to make this new structures visible. Since all calculated fields are in the same table, all you need is to make the VSLA_AGREEMENT_COMPLIANCE table visible.
Because your new table has a join with vapp_item, you can add it as a subset of Tickets, so it stays organized and it is always handy when you are working with tickets data. In my system I have it as a subset and renamed to SLA Information.
For details on how to make a table or field visible, check this article:
Advanced Reporting 101: Displaying Tables and Fields