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?

[Chad Hall]

I'm just now circling back to this as the need arose again. I would like to take you up on your offer for a Webex to see how you did this. I'm still only seeing reference fields in the Aggregation field drop-down, and that simply isn't useful to me.

Thanks!

[Tara Romero]

Sure!   Please email me at tara.romero@servicenow.com and we can get something set up.

[shaik_irfan]

Chad,

Can you please help me out since i got struck in the same situation where i too pulled out a report using Databaes views and i can see duplicate records which i want to remove / concatinate in a single record

tara.romero

it would be greate if you can send us a screenshot how to do without having Premium PA

[Chad Hall]

We haven't actually figured out how to do this yet. I showed Tara what I was attempting and how it only allowed me to do a distinct count on reference fields. She is still researching solutions. In the meantime, she pointed out that any database view involving sys_audit (which is what I was using) should generally be avoided. I plan to modify my task table by adding a custom field to capture what I'm looking for (a reference to the user who does what I'm interested in) so I can stop trying to hit sys_audit for this completely. I just haven't gotten around to it yet.

[shaik_irfan]

Oh...

My scenario is little different since i was tyring to pull RITM & Approver details in a single list view for which i joined sc_req_item & sc_item_option_mtom table.

But the problem is writing the same records multiple times because we have more than 1 approver for an RITM so if RITM consist of 3 approvers then all the records are written thrice