Table of Contents
Report ranges is an underutilized feature of ServiceNow. It allows us to group values like dates, durations and numbers into ranges. This is a much needed functionality if you want to report on numeric data with a wide range of values like reassignment counts, elapsed time or time past since a date.
Say for example that we wanted to look at our open SLA:s to see by how the values of the elapsed percentage. We would create a bar chart grouping the SLA records by “actual elapsed percentage”. It would look something like this:
The report tells us pretty much nothing, there are just too many variations of the value to plot them. This is where report ranges come in.
We find report ranges as its own module in the Reports application:
When we open it, we can see a list of some ranges that come preinstalled. A global range set up for the “sys_created_on” field comes preinstalled. There are also ranges for assessment results.
Setting up a new range
Lets proceed with setting up a new range for elapsed percentage on the task_sla table. Start by clicking “new” to open the form:
Label: The text that will appear on the report axis for the range.
Color: Specify a color in hex format. When using the range in a visualization, like a bar chart, the range will be of this color.
Color name: Pick one of the ServiceNow predefined colors.
Element: The field containing the value to group.
Name: The name of the table the range applies to. We can set this field to “global” if we want the range to apply to all tables with this field. As we saw earlier, the standard settings apply a global range for the “sys_created_on” field of all tables.
Order: The order field is used to order the ranges. If we have a record with a value that fits into two ranges, it will be grouped into the range with the lowest order value. This field is important since we cannot specify a lower value for our range.
Upper value int: The end of our range. The field only lets us input an integer, meaning a whole number. Decimal numbers can not be specified. If we had two ranges, 50-75 and 75-100, a record with a value of 75.3 would be grouped into the latter range.
Upper value duration: If we were trying to group a date or date-time-field, this would be the field where we specified the end of the range relative to current time. Only dates in the past can be used. For example, we specified the element as the “Opened” field, and the upper value duration as “23 hours, 59 minutes, 59 seconds”, the range would contain all records opened within the last 24 hours.
Value list: This list allows us to specify values that should be grouped. For example if we wanted to group different
Now we’ll set up some more ranges for our elapsed percentage. Notice the use of the order field and the upper value int:
In the last range, we want to include all SLA:s above 100 elapsed percent. We cannot leave the “upper value int” empty though, so we’ll set it to an absurdly high value.
When we run the report again, we get the SLA counts grouped into the ranges we set up. This results in a much more informative report. The individual bars can still be clicked to get to a list view of the underlying data.
The bad thing about report ranges and a work-around
One problem with report ranges is that once they’re set up, they are mandatory. You cannot chose different report ranges, or remove them. They will be applied to all reports grouping the field that they are set up for, including existing reports.
Say for example that someone wanted to group the open SLAs into bigger ranges, perhaps by an interval of 25% instead of 10%. They would not be able to do so. Multiple report ranges cannot be setup for the same field on the same table.
One way to get around this is by leveraging database views. A database view is usually a join between multiple tables, but it doesn’t have to be. If we only provide one input table to the view, it acts as a kind of table alias that can have its own reporting ranges. Unfortunately this leads to a lot of database views being created so it’s not an optimal solution, but it is a solution.
We’ll demonstrate with a database view created like this:
And then set up new report ranges with a 25% interval:
We can now create a new report based on the database view, and our new 25% range will be applied:
This means that multiple widgets grouped by different report ranges in the same dashboard:
Grouping with Value List
The value list in the range form also gives us some nice options. For example, an incident can be resolved using one of multiple resolution codes.
We could build a report and group it by this field:
But if we want an overview rather than a detailed view we could group these codes into ranges:
Now when we create a chart and group the incidents on resolve code, we get the following graph:
Remember, this report range will apply to all future and existing reports based on this field. So for fields commonly used in reporting, create a database view and build the ranges against that so you don’t unnecessarily limit the reporting options.