Distinct Count on Non-Reference Fields

[Chad Hall]

When creating a report that uses aggregation, such as a Bar chart, if you want to aggregate with a Distinct Count it only gives you the option to aggregate on Reference fields. I want to aggregate based on a String field (such as Number in an incident). How can you do this type of Distinct Count aggregation in a report?

[Naresh27]

HI Chad,

If you have Performance Analytics activated in your instance, you can aggregate(Count distinct) on all fields for instance Incident 'Number'.

Go to performance analytics in your application Navigator and look for automated indicators and create one that you can aggregate (Count distinct) all table fields.

before you create indicator, you need to create indicator source. if you are looking help on how to create a report in PA let me know.

Thanks

[Chad Hall]

I do not have Performance Analytics. I'm trying to do this in just the basic native Reporting designer.

[Tara Romero]

Hi again,

I too was thinking about PA, that is why I asked what release.   But now that is irrelevant, lol.   You can count distinct on string fields in regular reporting if the table you are using recognizes that the field is not already distinct.   In your example, incident by default is an distinct field so you could not use that count on it.   However if you are trying to count incident from a view where it is one to many relationship, then you would be able to distinct count on incident.   So it depends on your table/field that you are trying to count and whether or not it is needed.

Cheers

Tara

[Chad Hall]

I actually ran across this on a database view that involved incidents and their audit history. So I was running into the exactly one-to-many situation and wanted to net it down to distinct incidents instead of getting duplicates. I tried to simplify the scenario for this post, but ultimately I want it for database views that have duplicate rows for a single incident. I don't have the option to Distinct Count on anything besides reference fields though.

[Tara Romero]

Hey Chad,

I just tested this and was able to do the distinct count.   What I did notice was that the field names appeared to be labeled different.   Let me know if you want to set up a webex and see if we can't figure it out.

Cheers

Tara