Data Visualization(report): Group by a substring

[Flavio4]

Hello,

the need is to produce a report/data visualisation, grouping by a substring of the Name of a column.

E.g. list of some Task table records by Group By the first 7 characters of the Service.Name (for example to group all the "ServAAA1", "ServAAA2", "ServAAA3", etc. tasks, in comparison with the group of "ServBBB1", "ServBBB2", etc.

Apart from adding a Calculated Column in the Task table (which might not be a good idea), are there other ways to do it?

Thank you
Flavio

[Ankur Bawiskar · Accepted solution]

@Flavio4 

OOTB you cannot group by on substring field value

no straight forward way unless you have a custom field on your table.

Did you try to use Report function field?

Function Fields in ServiceNow Reports: Calculate and Display Data on the Fly 

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Flavio4]

Thank you Ankur.

Unfortunately in our instance the Function Fields function is disabled, because of alleged risks of jeopardizing performance.
I retain that it is not possible in ways other than the Function Fields.

Regards
Flavio

[Dr Atul - LNG]

Hi @Flavio4 

Grouping by a substring is not possible. Based on the data, the best approach is to use a search condition like starts with ServAAA, which will display only the records that match this prefix. Then you can perform a group by on the field.

[Dr Atul - LNG]

Did you try my workaround?