ServiceNow: related list conditions in reports
Tags: servicenow, report, dashboard
Published: 2023-03-14 20:53:08

Reporting in ServiceNow has some less known features and one of them are **related list conditions**. Think about these use cases how would you achieve:

`1) Show users without any role`
`2) Show Problems with more than 5 incidents`
`3) Show analysts who did not touch any incident in last 6 months`

You would probably start with Group by aggregation and ended up with scripted workaround (no way). For the first case "Show users without any role" (this is quite useful list for platform admins) we actually want to technically search for no data (without role). So group by would not help us. Glory to the related list conditions!

You start build report (`Reports > Create New`) and related list conditions are just under the _Conditions_ in report builder:

![Related list conditions](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nv5tw144ahlheijdr9kq.png)


The most complicated thing is to find the correct relationship from the dropdown box. It is based on all relations on the reported table, so there can be many relationships. You also choose the quantity. It means you tell the machine how many records in the related list are applicable for your query. You will mostly use `equal`, `greater than`, `less than`. 

![Related list conditions: quantity](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ds66v2i4917up3umpx56.png)

Additionally, we can also apply conditions on related list records (the third use case). The limitation is that you can use only one related list in the conditions, it is not possible to combine it.

No one built report right the first time, so take time, don't stress and play with it. Let's describe 3 scenarios mentioned in the beginning.

### 1) Show users without any role
Target table is `sys_user` and select `Roles` as related list. Quantity condition is `None` (no records = no roles).

![Related list conditions: Show users without any role](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m6w94v5qm99ypugpay14.png)

Quick verification, click `Run` on the report:

![Run report](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7bnn8u2cdojftpykhbfe.png)

and open any record, you should see no records in `Roles` related list:

![Related list conditions: Show users without any role](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rsyc711worp19fjuyiao.png)

### 2) Show Problems with more than 5 incidents
Target table is `problem`, related list is `Incident->Problem` and quantity `Greater than 5 `:
![Related list conditions: Show Problems with more than 5 incidents](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qu66t8a73hg1g44567yc.png)

If you click `Run` and open any record, check the related list `Incidents`. It will have more than 5 incidents:

![Related list conditions: Show Problems with more than 5 incidents](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c8f4bko4u4fx5pbzvttz.png)


### 3) Show analysts who did not touch any incident in last 6 months
I have moral problem with this kind of reports but why not for the use case :) 

Target table is `sys_user`, apply conditions `active=true` and roles has `itil`. In related list select `Incident->Assigned to`, quantity is `None` and condition is `Updated on Last 6 months`. 

Let's explain it a bit more, I need to tell machine this fact: show me users who have **0 records** in related list `Incident⇾Assigned to` while applying condition that incidents were updated in _last 6 months_. With quantity `None` it is exactly what I need.

![Show analysts who did not touch any incident in last 6 month](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8qyxdd2xl95v4c2q6a8p.png)

Quick verification, click `Run` on the report and open any user, you should see only incidents which were updated 6 months ago and before that. (You will probably need to add related list "Incident⇾Assigned to" on user form to check the results.)

![Related list conditions](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3n2z68cxyxjjb0fw7qou.png)

The magic here is the condition `Updated on Last 6 months` because we can apply conditions on related list records too! This gives us a powerful tool for many reporting use cases. 

### Performance
It might take some time on large data sets to get report results because technically related list conditions must do some extra queries. I am not sure how exactly is this done in the backend, if there is somehow one join SQL, or it goes one record by one and does separate queries. 

When you build report always start on a small subset (like incidents created on this week). On the other hand, do not be afraid, ServiceNow performance is scalable and in the worst case you will block just your session.

I naively thought that all 3 use cases would be easily done by `Create your report with Analytics Q&A` feature directly in Report designer which utilizes natural language understanding. The questions are well-structured, ServiceNow language model could understand it, but no result. I can see some space for improvement.

---

What is the biggest power of report designer? The best thing is that report designer is available for users with itil role. Anyone with itil role can make and save the reports. There is literally just a small step to inform itil people about the report designer power and which use cases they can do by themselves. No admin needed, no expensive consultancy services. Just spread the information.