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

smacleod
Tera Expert

2 hours ago

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.

0 Helpfuls
  • 91 Views
1 REPLY 1

Itallo Brandão
Tera Guru

an hour ago

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.

0 Helpfuls