Distinct Count in Reporting

[Jane Stone]

Does anyone know what determines which fields are available for selection when choosing the Distinct Count option in Reporting? My scenario is that I am reporting using a database view that has multiple rows per change request. I created a trend report based on the planned start date but I want the count in the bar to be based on the number of change records, not the number of rows in the data. If I choose the option Count Distinct only certain fields are available and the change number field is not one of them. Does anyone know if there is a way to make it available?

[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!!

Don't do this in prod, do this change in sub-prod instance.  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.

[kemmy1]

How did you do this:  "Created a report that was counting distinct on another field which is displayed.  "  Using the Report module in ServiceNow?