Relationship made on Sys_relationship not showing up when setting up a visualization

[smacleod]

I am trying to set up a chart to show us the task SLAs for Ritms with tasks assigned to your groups (or selected groups).

I have set up a Sys_relationship record which works in the related items lists from the task SLA (Task SLA.task = task.parent).

When I go to set up the chart using this relationship in the Related List Condition, it is not there.

[Itallo Brandão]

Hi @smacleod ,

This is a very common "gotcha" in ServiceNow reporting.

The Explanation: Reporting Conditions (and Related List Conditions) rely exclusively on Database Schema relationships (Reference fields/Foreign Keys). Your sys_relationship record works on the form because it is a Scripted relationship calculated by the UI at runtime. The Report Builder's SQL generator cannot interpret or execute that JavaScript to build a database query, so it simply ignores it.

The Solution: Database View To report on "SLAs of RITMs" based on criteria from the "Child Tasks", you need to join these tables properly using a Database View. This creates a virtual table that links them via SQL.

How to set it up:

1. Navigate to System Definition > Database Views.

2. Click New. Name it something like u_sla_ritm_task_joined.

3. Add the View Tables (Related Lists):

   • Table 1 (The SLA):

     • Table: task_sla

     • Order: 100

     • Variable prefix: sla

   • Table 2 (The Task):

     • Table: sc_task

     • Order: 200

     • Variable prefix: sctask

     • Where clause: sla_task = sctask_request_item (This assumes the SLA is attached to the RITM. It links the SLA's parent RITM to the Task's parent RITM).

4. The Result: Now, go to your Report/Visualization.

   • Source Table: Select your new view u_sla_ritm_task_joined.

   • Group By: sla_stage (or whatever SLA field you need).

   • Filter Condition: sctask_assignment_group is (dynamic) One of My Groups.

Note: Be aware that if a RITM has multiple tasks assigned to your groups, the view will generate a row for each task, potentially duplicating the SLA in the count. You may need to use "Count Distinct" aggregation if available, or just be aware of the 1-to-many behavior.

If this explanation helps you visualize the data correctly, please mark it as Accepted Solution.

Best regards,
Brandão.

[smacleod]

How do I use this new table to filter the Task SLA table.

This new table does not appear to work correctly with the visualizations (does not correctly display "breached" for SLAs). I am trying to have the catalog tasks show up in the related list conditions: 

[lauri457]

Related list conditions only work when the tables actually have a reference column in them. You'd need to set up a reference field or a m2m relationship between [task_sla] and [sc_task] and then you could build a related list condition directly or on the m2m record.

@Itallo Brandãos solution is better because the database view will just create an inner join for you and you will have access to it directly as a data source on your data viz and you will be able to dotwalk the conditions from sc_task. Preferably add only the fields you need to the view

This is what a database view does behind the scenes:

SELECT
    /* fields omitted */
FROM task sctask
INNER JOIN task_sla tsla
    ON sctask.a_ref_2 = tsla.task
WHERE sctask.sys_class_name = 'sc_task'
  AND sctask.assignment_group IN (
        'cfcbad03d711110050f5edcb9e61038f',
        '74ad1ff3c611227d01d25feac2af603f'
      )

With inner join you will get only the rows that match between table A and table B. 

Just be aware of performance impact if you make complex conditions

var list = new GlideRecord("u_sctask_ritm_sla");
list.addQuery("sctask_assignment_group", "IN", "cfcbad03d711110050f5edcb9e61038f,74ad1ff3c611227d01d25feac2af603f")
list.addQuery("sctask_parent.ref_sc_req_item.cat_item.name", "STARTSWITH", "C")
list.query();

SELECT
    /* fields omitted */
FROM (
    task sctask
    INNER JOIN task_sla tsla
        ON sctask.a_ref_2 = tsla.task
    LEFT JOIN task task1
        ON sctask.a_ref_2 = task1.sys_id
    LEFT JOIN sc_cat_item sc_cat_item2
        ON task1.a_ref_1 = sc_cat_item2.sys_id
)
WHERE sctask.sys_class_name = 'sc_task'
  AND sctask.assignment_group IN (
        'cfcbad03d711110050f5edcb9e61038f',
        '74ad1ff3c611227d01d25feac2af603f'
      )
  AND sc_cat_item2.name LIKE 'C%'

[smacleod]

the problem is that the charts are using the exact same fields and criteria as the Task SLA, but are not displaying the data as "breached" or with hours to being due, just as "same day"