Create report where count > 1

[spike]

I'm trying to create a report on metric_instance where there are more than one instance of the Assignment Group metric been recorded. I only want to report on those incidents where there is more than one of those metrics.

However there doesn't seem to be a way for me to be able to put a clause in there for count > 1.

Any ideas on how I might go about achieving this?

I've looked at the related list section but it lists not tables when I try to use it.

Taking it the other way and looking at incidents and then trying to use the related list to filter, I find the metric_instance table isn't listed so not able to drive it from that side either. 

Any help or direction would be greatly appreciated!

[Community Alums]

Hi @spike 

So is assigned to duration - a OOTB Metric instance? If it is, then you can get the metric definition of the Metric instance and then create a report on the Metric instance table and apply the necessary filters to get the report. 

Regards,

Gagan k

[spike]

Thanks again @Community Alums for responding.

I can create the report, and have done. But I only want to see those incidents that appear more than once. EG the ones with the arrows on the screen shot.

I can't see a way of doing that. That's where I'm stuck..

(Report is on table incident_metric).

[Community Alums]

Hi @spike ,

You can dot walk and get the definition number. Then select the filter option as greater than/equal to 2. Attached is the screenshot of the same. 

However, you need to do another thing, which is change the type of the graph as Bar and you have an option 'Aggregation' where you have to select Count distinct, which will give you distinct count of the incidents. 

Or

If you want to show the Incidents in the 'list' view, then just apply the filter without the aggregation. As you will not have the option to select it in the list view.

I guess you should get what you are looking for.

Please mark helpful/like if the solution is in line with your expectation.

Regards,

Gagan k

[Community Alums]

Hi @spike 

Were you able to check the solution I have mentioned in earlier post?

Regards,

Gagan k

[spike]

It does and it doesn't. I see what you're doing, and I understand how you're using the count distinct and dot walking to get the various values. I just don't have the same options as you do for grouping.

I can kinda get it to work with the bar graph but I get a massive "Other" bar that I can't drill down into. Even when I restrict the timeline I still get quite a bit other bar. And if I set the various group bys etc and then move to a list report, it doesn't show the right data (includes all the count=1 entries).

And I'll be honest I've forgotten what I was trying to achieve. I've got it round here somewhere so when I find it, I'll loop back to this and take another look.

Thanks for your help and input.