Max and min breach time in Incident list report

[albertosaenz]

Hi, I've been requested to crate a List Data Visualization with the list of Incidents that matches certain conditions with fields from Incident table but also with 2 additional columns:

• Max breach time: The maximum breach date of all the SLAs of the incident 
• Min breach time: The minimum breach date of all the SLAs of the incident

I want to avoid creating new fields in Incident table.

Another constraint is that I can't use Multi Pivot table report, because they are not exportable to Excel.

Can this be achieved without the creation of two fields in Incident table that are updated via busines rule?

If there is not another way, how should these two fields be calculated?

[DanielJ43996773]

Hello @albertosaenz how are you?

Yes, out of the box, not cleanly in a List Data Visualization based on Incident alone.

The reason is that your two extra columns are aggregates of child records from task_sla:

Max breach time = maximum breach/due datetime across all SLAs linked to the incident
Min breach time = minimum breach/due datetime across all SLAs linked to the incident

A list visualization can show Incident fields, and ServiceNow supports MIN/MAX aggregation in reports, but that aggregation applies to the report result set — it does not natively add two per-row aggregate columns from a child table into each Incident row without either:

persisting values on Incident, or introducing an intermediate reporting structure such as a database view / custom dataset.

The Task SLA records you need live in task_sla, and that is the table where the SLA dates are stored for each task/incident. ServiceNow’s Task SLA documentation confirms that SLAs attached to a task are represented there. Also, in practice the SLA “due” value is tied to the SLA record’s planned end time / due date rather than to Incident itself.

If you want this in a single exportable list, without adding fields to Incident, the best design is usually:

Option 1 — Database View for reporting

Create a Database View joining:

incident
task_sla

Then report from that view. ServiceNow documentation explicitly recommends database views when you need reporting across multiple tables, including SLA + Incident data together.

The limitation is:

a plain database view gives you one row per joined record, so likely multiple rows per incident if there are multiple SLAs.

to get one row per incident with MIN/MAX, you still need an aggregation layer on top of that. Depending on your version and tooling, that may still not give you the exact “normal list with two extra columns” format you want.

So this is viable for analytics/reporting, but not always ideal for a straightforward exportable row-per-incident list.

If the requirement is strictly:

one row per Incident
exportable to Excel
two extra SLA aggregate columns
no duplicate Incident rows

then the most robust solution is to add two fields on Incident and populate them from task_sla. That is also consistent with common ServiceNow guidance for making SLA-based reporting easier on task tables.

Use values from task_sla for the given incident, usually the SLA due/breach datetime field your process considers authoritative. In many implementations this is the SLA record’s planned end time / due date.

For each Incident:

u_min_breach_time = earliest SLA due/breach datetime among related task_sla records
u_max_breach_time = latest SLA due/breach datetime among related task_sla records
Logic

Query task_sla where:

task = current.sys_id

Then:

ignore canceled/irrelevant SLA records if your reporting rules require that
compute:
MIN(planned_end_time) or equivalent due field
MAX(planned_end_time) or equivalent due field
Best implementation pattern

Instead of recalculating on every Incident update, do it when task_sla changes:

after insert
after update
after delete if needed

That is better than a Business Rule on Incident itself, because the source of truth is task_sla.

Typical script pattern

Use an aggregate query on task_sla for performance, not a row-by-row loop. GlideAggregate is the platform mechanism intended for MIN/MAX database aggregation.

If this solution worked for you, please mark it as Accepted Solution, give it a like, and thank you for your time!

Best Regards
Dan.