Count Distinct on record Number

[Jaymes]

I can't believe I'm the only one wondering this... I've seen similar questions, but not THIS question.

ONE of MANY scenarios we have is that we have Incident Tasks assigned to Assignment Groups who participate in Incident resolution. An Assignment Group can have none, one or many such Tasks.

One report that we'd like would show the number of Incidents a given Assignment Group helped with over a period of time. We can easily create a report that counts all Tasks but when selecting "Count Distinct," "Number" (referring to the Incident number) is not a choice (nor is Parent.Number). 

Example, Incident 1 has tasks for teams: A, A, B, C. Incident 2 has tasks for teams: B, C, C, D, D, D.

Reports are able to produce results counting tasks as A = 2, B = 2, C = 3, D = 3.

With Count Distinct applied to Incident Number, it would be possible to get the results: A = 1, B = 2, C = 2, D = 1

Again, this is one of many cases where COunt Distinct would help slice the data. Why doesn't SNOW allow Count Distinct against the actual record Number?? Alternately, what do I not understand about Count Distinct? Something is amiss.

Thanks!!

[Alessandro Alme]

Hi Jaymes,

Maybe this solution can help you: https://community.servicenow.com/community?id=community_question&sys_id=5343cfe1dbd8dbc01dcaf3231f96...

Thanks,

Alessandro.

[Allen Andreas]

Hi,

This has been brought up before, but not a common request/inquiry.

With that said, check out this post and see if it's something you may be interested in. Perhaps check out the very last post about using pivot table and see if that helps?

https://community.servicenow.com/community?id=community_question&sys_id=69620beddb98dbc01dcaf3231f96...

Please mark reply as Helpful/Correct, if applicable. Thanks!

[Jaymes]

Thanks for the quick replies. Unfortunately, I don't understand how the post on GlideAggregate applies to my question.

On the chat discussing a pivot table solution, I could pivot (see what I did there?) to using a pivot table (had been going after a column chart) but I'm not 100% on board with what niveknonrev did... Did they define new metrics there? It's something I've not had to do and would likely have to explain the need to the SNOW admins here.

[Allen Andreas]

Hi,

Yeah, I see what you did, hah. If my reply was at least helpful, please mark it as such. I believe the GlideAggregate post was in regard to you coding your own report versus using the out-of-box reporting tool. It's a common response when people saying the reporting tool can't do 'x', there's usually one about doing some sort of script.

As far as my post, it was somewhat of a different use case, but I elaborated on the part that would be of interest, which was basically using a pivot type report. So, no hidden message there, I called that out to help. Also the point that others have asked about the count distinct before for the number field, since you were sort of saying it like: why hasn't anyone brought this up before, kind of statement.

Just an idea.