Combining Columns on Report Multi-Level Pivot Table

[cboyd12]

 I have created the report pictured below. I need it be in the Multi-Level Pivot Table type and show the number of changes open grouped by month. Right now I am only able to group the changes by the Opened date.

I have looked at other resources online and some suggested to just add a "Month" column to the task table, and then use that in the report to get the right view. However, I don't know if that is the best practice or route to go.

Any advice?

[Christopher Sik · Accepted solution]

Hi @cboyd12 ,

Unfortunately, I don't think there is an out-of-box way to group/merge columns in ServiceNow Pivot reports the way you can in Excel. 

You could, of course add the additional column as you suggested. But there may be a performance impact over time by applying that calculated field to every record on the task table. If you're considering that, I would recommend adding the additional field to the Change table specifically. 

Better still, since the value is unlikely to change once calculated, you could populate the value by modifying the record producer for change records by adding a flow that calculates the month value (using the opened date value) whenever a change record is added to the change table and inserts that value into your custom "month field".  You'd need a small script include in the flow that would extract the month number from the opened date value and return a month name text value.  You'd also have to backfill the field value on previous records either manually from the list view or using a similar script script.

But if you are looking for a more out-of-box approach using just the reports module, you could consider creating a bar chart report that groups change records by month (using the opened by date). Then add a breakdown to the report, so that when users click a particular month's bar, they are presented with your pivot report which shows the monthly count broken down by Assignment Group.

Alternatively, you could add an "additional stack by" option for "Assignment Group" to the bar chart which would allow users to display the monthly bars as multi-colored bands. Each colored band would represent each of your assignment groups. Hovering over each band would display the group name and their count for the selected month. Clicking the band would provide a list view of change records assigned to that group for the selected month that contributed to the monthly count.

Creating drill-down reports in this manner is usually easier for viewers to digest and by making it interactive they can focus on the specific data points that are valuable to them. Incorporating a report like this as a dashboard widget would afford you even more filtering options while allowing you to display different elements of your change records on the same pane in the form of other widgets. For example, you could show change records by month, by state, by assignment group, etc. simultaneously on a single pane of glass. Interactive filters would allow the user to change the time span, the assignment group, etc. So now a single dashboard provides the value of multiple reports all at once. And even more so, since multiple dataset views/metrics can be viewed side by side.

[cboyd12]

This was very helpful, thanks so much for your detailed response Christopher!