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?

[shivanipatel]

Chad,

We are glad you took advantage of the ServiceNow Community to learn more and to get your questions answered. The Customer Experience Team is working hard to ensure that the Community experience is most optimal for our customers.

If you were not able to receive the answer you hoped for but you do believe that you received clarity in terms of how to proceed, we would greatly appreciate if you could mark the appropriate thread as "Correct Answer". This improves the ServiceNow Community experience.

If you are viewing this from the Community inbox you will not see the correct answer button.   If so, please review How to Mark Answers Correct From Inbox View.

Thanks!

Shivani Patel

[Adam Stout]

I believe that this restriction is meant to protect performance (for instance grouping by distinct name in the sys_user table could perform very badly).   However, is this was a hard requirement, I was able to count distinct on Number by creating a report that was counting distinct another field.   Once this was done.   I enabled list editing (admin required) on sys_report.   Then I changed the Sum Field (or the appropriate field for your report) to "Number".

I recommend not doing this, but if it is required, do it in a sub-prod.   Enable list editing on sys_report.   Then open a new update set just for this report.   Once the report is done.   Close the update set and follow your change process to migrate it to production.

[Tara Romero]

Yes, but I was hoping for a way to get around what he was trying to do all together by creating a metric instance. I just wasn't sure about the right way to go about creating that.

[shaik_irfan]

Adam,

Can you please help me out for my requirement which is little required for our client

My scenario is little different since i was tyring to pull RITM, What access (Pulled Question & Value) & 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.

How to get rid of this by hiding unique records ???

[Mohammed_Iqbal]

Hello Adam,

I also want to implement the same requirement can you please help me here, I have to use aggregate as “Count Distinct” .

I would need to do a count distinct of “Inc.Number” or “Inc.sys_id”, but they do not show up in the choice field for count distinct.

Would you have any idea why they do not show up or what can be done to achieve the same result (without having Performance Analytics)?

I want number column in the aggregate field.

Regards, 

Mohammed Iqbal