Problem task reporting assistance

[arobertson]

Hi all,

I'm trying to create a report on active problems where all the problem tasks are closed but i think i'm over thinking it. The purpose of this report is to pick out stale problem records which are active but have no open tasks.

I was able to create a pivot table on the problem task table where the parent problem is active but this gives me a list of all active problems and all the problem tasks regardless of that state.

I'm thinking i need some script that counts all the problem tasks and then checks that their all closed? but don't really want a database view.

[Mike Allen]

Try this in background scripts var prob = new GlideRecord('problem'); prob.addActiveQuery(); prob.query(); while(prob.next()){ var count = new GlideAggregate('problem_task'); count.addAggregate('COUNT'); count.addQuery('problem', prob.sys_id); count.groupBy('number'); count.groupBy('state'); count.query(); while (count.next()) { var prob_number = prob.number; var total = count.getAggregate('COUNT'); var state= count.state.getDisplayValue(); var number = count.number; gs.print(prob_number + '\nState: ' + state + ' Total: ' + total); } }

[Mike Allen]

To be clear, this doesn't answer your question, but gives you a script to play with.  It will show all active problems and give you the state of all the problem tasks per problem.

[Adam Stout]

This should be straight forward with a Related List Condition (look for no active tasks).  Here is an article with some more explanation:

https://community.servicenow.com/community?id=community_blog&sys_id=bd0eaa2ddbd0dbc01dcaf3231f96199e

[Slawek_Radziewi]

You can use related list condition in report like below.