Add a field on the Count Distinct aggregation options for reports

[JC S_]

We are trying to create a report from the incident_metric table and we want to count the distinct values from the "Value" field. However, we can't see the field "Value" on the choices. How can we add this field on the count distinct choices?

[Archana Natara1]

Hi All,

I was searching for the same and found this solution in other community post. In case if someone is looking for the answer, please see below

This restriction is meant to protect performance. But we get many scenarios like this, I was able to count distinct on Source for device table in discovery by following the steps.

Created a report that was counting distinct on another field which is displayed.   

Enabled list editing (admin required) on sys_report table.

Then changed the SumField in the list for my report (or the appropriate field for your report) to "Source".

It worked like a charm!!

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.

Reference:

https://community.servicenow.com/community?id=community_question&sys_id=7200c321db98dbc01dcaf3231f96...

[Community Alums]

Hi Archana,

Did you find any performance issue with this solution? 

Regards,

Prakash

[Loding_m]

As Archana mentioned, it is not recommended. But an alternative of enabling the list edit to make the change. A simple background script to update the 'sumField' could be used.

For example:

var gr = new GlideRecord('sys_report');

gr.addEncodedQuery('sys_id=*SysIDOfReport*');

gr.query();

while(gr.next()){

    gr.sumfield = '*FieldofTableYouWantToCountDistinct*';

     gr.update();

} 

Please Note: Best Practice is to run and test the above script in a Non-production environment prior to implementing in production.

[Brian Dolinar]

Thanks for the script on modifying a report to use a Count Distinct on a field which is not in the report UI drop-down list!

It does have to be the exact field name of the field, especially if you are using a Database View, where field names get a table_ prefix.  To be sure one has the correct field, view the XML of a single record and get the exact field name to put that in the script.

[simmons_andre]

Hello JC,

We had a similar requirement on one of our projects. We solved this problem by adding reference field that pointed to itself (in your case Incident). We created a business rule that populated this field on insert. After that we were able to use the the self-referenced field in the count distinct list to aggregate our rows appropriately.