Horizontal Bar report on table | can't sort by aggregated field

[Med99]

I'm trying to create a Horizontal Bar report on sn_vul_vulnerable_item table for example showing Top 10 Configuration Items with the lowest average Remediation Time (Average ofage_closed field, grouped by cmdb_ci).

Filters: State = Closed AND Configuration item is not empty

Problem: When I add a Sort Field on age_closed ascending, I get the warning: "Invalid order by field age_closed: You can only order by the Group By field and/or the Stacked field"

Tried both Classic UI and New UI but same result. Any workaround? Thanks 

[Huynh Loc]

Hi @Med99  ,
Review the 'Sort by' value and the 'Group by' value, and make sure they are matching, which will help you resolve the issue
https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0852211

If this response was helpful, please consider marking it as Correct and Helpful. You may mark more than one reply as an accepted solution.

[Med99]

I wanna list the top 10 Configuration items with the least age closed. Can't do this with matching the Sort by' value and the 'Group by' value

[Tanushree Maiti]

Hi @Med99 

Refer KB : KB0852211 "WARNING: Invalid order by field "instance.number": You can only order by the "Group By" f... 

Issue

When running a report, the following error is displayed at the top of a report, just below the filters:

"WARNING: Invalid order by field "instance.number": You can only order by the "Group By" field and/or the "Stacked" field"

Cause

One cause of the issue is the 'Sort by' value which is not matching the 'Group by' value.

Resolution

Review the 'Sort by' value and the 'Group by' value and make sure they are matching.

It's important to note that the 'Sort by' option does not support dot-walked fields as documented in the following documentation page at step 7: Create a bar report: "Add sort cannot be applied to dot-walked fields."