Ooh, There's count distinct for that (sys_reports)

[ChrisF323949498]

My friend looked back surprised, what??

They had just spent hours trying to get a unique count of assignment groups, across their incidents to appear in a counter on a dashboard and missed a useful feature.

They also exported to Excel and figured it out via a manual report.

Then they wrote a script, to pop them into a set, and referenced that script in the sys_id field, thus deduped that way to give a count.

Innovative approaches, but when I said 'You know there's distinct count for that' they raised an eye brow.

So, I wanted to share it because I've seen numerous community posts asking questions that can be solved by this, and so it seems like it's a 'hidden gem'.

Use case

'I know we have all these incidents, but how many unique support groups are they being assigned to? Can we get a counter on the dashboard as I suspect we can delete about 50% of our assignment groups in the platform? But I need data!'

1. Create your sys_report
2. Set type > to 'Indicator'
3. > Configure: choose 'Aggregation: Count distinct'
   1. Then choose your desired field (Assignment group for this use case)

Now we have the distinct count (I.e. If there are 8 Incident records and each record is assigned to either Group1 or Group2, we get the 2 unique groups(Group1 and Group 2) and NOT '8' (number of incidents)

(Side note: Think of it as being similar to 'Grouping by', but side note, Empty is likely excluded for aggregate, since its not a value. Where as if you use 'Group by', you'll see EMPTY as a separate group. Try it)

I agree it's simple and may even be obvious for some - but as I mentioned, I only knew of it as I'd used it, so just spreading the awareness as this may save someone hours.

Have a great day all!