It's Just a "Reporting" Field...

[Adam Stout]

When configuring a new report Performance Analytics, we sometimes get slowed down because the data isn't structured the way we need for reporting.  There is a disconnect between the way we operationally use the data and the way we want to show it for reporting.   For example, operationally we rollup our group hierarchy on HR's hierarchy but in other cases we need to see it based on the functional area which is slightly different. 

For my entire career in analytics, I have been taught that we can't change the operational system the data comes from, we need to reactively deal with it and transform the data we get into a format we need for reporting.  This is why we have teams of people handling Extract-Transform-Load (ETL) to clean and format the operational data to a usable format for our reports.  ETL is how we clean the operational data and transform it to provide the data and format we need for reporting.

ServiceNow embraces a different paradigm: Performance Analytics and Reporting is part of the platform and not a separate system that we need to protect and insulate ourselves from.  We need to keep the data clean and trustworthy in the platform and ensure we have all the fields needed for reporting as well.

Some application developers and system admins disagree with the concept of adding "Reporting Fields" to operational tables.  If you hear this objection, it is important to push back. 

Analytics is much like application security.  You have to build it into your application, you can't just bolt it on later on.  Just like application security, the sooner you start thinking about analytics in any implementation the better off you will be and the less rework you will have in the future.

If your management team requires data to be structured in a specific way on reports, why wouldn’t you expose that structure within the operational system?

Adding a "Reporting Field" will allow us to use that not only in reporting but also allow us to easily use that field in:

• Performance Analytics
• Reporting
• Interactive Filters
• Business Rules
• SLA Conditions
• UI Policies (to customize the record form based on this)
• Workflows
• Machine Learning
• Everything else the NOW Platform can do

Note: Transforming the data via a PA Script doesn't address the root issue.  In most cases, this is just ETL by a different name.

Use Case

Let's walk through a simple example use case and discuss how we can take a "Reporting Field" and add it to the operational system.

Background

We have 100 assignment groups handling incidents, and each assignment group belongs to a parent group - either Tier 1, Tier 2 or Tier 3.  The CIO wants a summary of incidents broken down by "Front Office" and "Back Office” categories, but neither category is directly related to the assignment group or parent group.

The Old Solution

We create and maintain a list outside of ServiceNow that categorizes each assignment group as Front Office or Back Office, and we use Excel or BI tools to create a pivot table. Inevitably, the list isn’t available to everyone who needs it, and quickly becomes outdated.   Often, I see customers prefix their assignment groups with something like "FRONT-" or "BACK-" to help a human know which group is what.

The Right Solution

The best way to handle this request is to add a field (most likely a choice list) to the Groups table (sys_user_group) that identifies which assignment groups are "Front Office" and which are "Back Office".  We can then easily

• Breakdown by Front and Back office in Performance Analytics
• Filter by Front and Back office in an Interactive Filters
• Group by Front and Back office in a Report
• Use Front and Back office in a Business Rule
• Use Front and Back office information when training Machine Learning

Alternative Solutions

Since we are dealing with a hierarchical data structure with sys_user_group, we could replace our tier hierarchy with a group layer of Front and Back Office.  This would most likely be effective (and maybe a good choice depending on other needs) but in our case, the hierarchy was already being used for a different type of reporting.

You could also set Front Office and Back Office as a group type.  However, "List" fields are difficult to report on so I chose not to use this approach.

Other Use Cases

There are many other places where we can benefit from materializing the "Reporting Field" as a field on the object.  Using a business rule (BR) that runs after changes can execute the calculation only when needed, saving CPU cycles Performance Analytics or reporting would have used.

Here are a few other examples:

• Problem Tasks Count Total/Active/Closed - A counter of the number of problem tasks that exist and are active and inactive (based on a BR that updated the PRB when the task state is updated)
• Flagging "High Visibility" Incidents - If my organization has a definition of "High Visibility" Incidents as
• • All P1s
  • P2s on Critical Services or Submitted by VIPs

Instead of adding these criteria into every report, create a BR that updates a "High Visibility" flag when the Priority or Business Service changes (note, if Criticality is something that can change for Business Service, you may have to add a BR there as well to address this)

• Group Reassignment Count - There is an OOTB counter that increments every time an incident is reassigned to a different person but not for group reassignments.  If this is important, add a BR that tracks it
• Highest Priority - I want to track the incidents that were ever set as P1s.  Instead of querying sys_audit, add a field for "Highest Priority" and update it with a BR that updates if the new priority is higher than the current "Highest Priority"

Conclusion

Including traditional "Reporting Fields" in your operational process will allow you to easily report on the data as well as do so much more with it.  Exposing these properties in your operational tables allows everyone to see the information and make better decisions.

Follow Up

If you are looking for some hands-on examples of how to put this in practice, check out this blog, "Conquer Challenging Reports by Leveraging the Now Platform - Reporting on the Latest Status"