Count Distinct on Incident_Metric -> Number column

[brandimore]

Hello

I would like to count distinct on the Number column in this table. Unfortunately it's not an option when I go to review the list of Aggregates. I saw another post about creating a new Indicator and Source for this to work. This seems like a lot of work for something this simple, but I did this anyways. When I'm in my report it's not an option to select.

I want to make sure that I'm not making more work for something. Can someone confirm how you can make a new field as an option to Aggregate?

Thanks

Instanbul is the version I'm running. We do have PA also.

[Arnoud Kooi]

If you have access to a background script:

var sysid = 'reportsysid'
var gr = new GlideRecord('sys_report');
if (gr.get(sysid)) { 
   gr.sumfield = 'inc_number';
   gr.update();
}

[Josh Cooper]

The real question is, whether this is a one-off report, or something you want to track day over day or month over month.

It's easy to do this with Performance Analytics, and that gives you access to the historical trending data, and that's where the indicator and indicator source come in - they won't impact the base reporting.   But that indicator looks like this:

and the "Incident.Metric.Resolved" indicator source looks like this:

Because of the trending/interval type nature of PA, you'll want to make sure the indicator source has some sort of date limiter, like created on Today, or Yesterday, or This month, or Last month, etc... that way you can compare date intervals.   Without that, it just becomes an aggregate of ALL records, which is of limited value and will eventually outgrow your result set.

[brandimore]

Thanks for the responses. I've made a bit of headway. I'd like to run it by you Josh and see if the above still applies.

We are creating a report that will be executed a few times a month (the result is a dashboard). We created an Interactive filter and it points to the Incident_Metrics table.

We get distinct Ticket Numbers returned now which is great.

The issue: Is that the Interactive filter is a reference filter, and we cannot point to any Value field. So we are forced to reference the "Assignment group" in the Incident_Metrics table, and unfortunately that field is ALWAYS set to the current owner of the incident.

What can resolve this: What we need to do is be able to filter out the value field.

Again, maybe I'm not going in the right direction and the above will work but I wanted to give you an update.

Thanks again for your time.

[brandimore]

This dashboard filter above needs to be able to filter out the "value" field. Unfortunately, because it's not a reference field we cannot do this. I'm forced to pick the assignment group and as mentioned, it's ALWAYS set to the current group. Which doesn't work