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:
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.
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).
Quick verification, click Run on the report:
and open any record, you should see no records in Roles related list:
2) Show Problems with more than 5 incidents
Target table is problem, related list is Incident->Problem and quantity Greater than 5:
If you click Run and open any record, check the related list Incidents. It will have more than 5 incidents:
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.
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.)
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.
Top comments (0)