Best way to group by year?

[CJB]

Hey guys, I have a list report for assets I'm trying to put together, but have hit a wall. Customer is requesting that the assets be grouped by purchase year, but right now, I don't have that option. Purchase dates include month and day as well, so it's grouping by mm-dd-yyyy, when I need it to be yyyy. I assumed I would need to create a script, but I honestly don't even know where to start. I guess I will need to create a business rule? Thanks.

[Adam Lankford · Accepted solution]

You could create a calculated field on the table that would extract the year.  Then you could group by this field in the out of the box reporting in ServiceNow.

[Travis Woods]

Hi I would like to just provide an update on this question. I had a similar use case that led me to this question and upon testing this solution, it did not work for me. What did work was setting an after business rule on the table as mentioned in this post: https://www.servicenow.com/community/itsm-forum/how-to-query-calculated-field/td-p/681926

If the link doesn't work this is what I am referring to:

Calculated fields are inherently flawed in that you cannot query the calculated value. When you do a query on a calculated field, you are querying on the value stored in the database (as of the last update) which may be different from the current calculation.

A better approach might be to have an "after" business rule on insert/update/delete of records in table2 that updates the appropriate record in table1 with the correct Count value.

The code I used in my business rule:

try{
	if(current.date){
		var gdt = new GlideDateTime(current.date);
		// Extract the month from the date
		var month = gdt.getMonthUTC();
		current.u_month = month;
        // Extract the year from the date
        var year = gdt.getYearUTC();
        // Set the extracted year to the u_year field
        current.u_year = year;
		current.update();
	}
} catch(e){
	error(e);
}