Nearest SLA Breach time on a Report/Dashboard

[rohanbhatia]

On Cases we are using SLAs. At max there will be 2 SLA in active state. One is of target Response and other one will have target Resolution. 
I have a report which has been made on Case table. I want to show a column in that report named as Breach time which will show Nearest SLA Breach time whether it is of Response or Resolution.

PS: I tried to make report on task_sla table but for that I need to group on case field which we don't want. We want a new column in a single row. 

Should I make a new field or there is another way to approach this?

[aruncr0122]

Hi @rohanbhatia ,

You don’t need a new field for this. You can use a calculated field or GlideAggregate approach:

Create a calculated field (or display value) on the Case table that uses a script to query the task_sla table for that case, fetch active SLAs, and return the earliest planned_end_time.

Example logic:
var slaGR = new GlideRecord('task_sla');
slaGR.addQuery('task', current.sys_id);
slaGR.addQuery('active', true);
slaGR.orderBy('planned_end_time');
slaGR.query();
if (slaGR.next())
answer = slaGR.planned_end_time;

Then, use this field in your report to show the nearest SLA breach time directly in one row per case.

Thanks!

[rohanbhatia]

can i use this calculated field in reports?