Making the most of your Historical On Call report data

The information in this article is the intellectual property of xMatters and is intended only for use with xMatters products by xMatters customers and their employees. Further, this intellectual property is proprietary and must not be reused or resold.

The Historical On Call report lets supervisors know how much time their team members have been spending on call, and divvies up each person's history into manageable segments. 

We're working on adding more features and functionality to the report to make it even more useful and to allow supervisors to fine-tune their exported data, but there's already one sure-fire way to make the data easily accessible and understandable: pivot tables! A pivot table is a great way to break down and summarize large data sets – much like the one in your newly generated Historical On Call report – so you can analyze and extract the data that's most important to you.

Of course, there are thousands of different tutorials, walkthroughs, videos, and other assorted resources online on how to use pivot tables, but we felt it would be helpful to show you how to create one tailored specifically for the Historical On Call report.

In this example, we'll come up with a way to easily see the total number of on-call minutes that we need to pay each user for, and be able to validate the data by drilling down into the totals and schedules.

Preparing and selecting your data

To begin, you'll need to generate a Historical On Call report from your xMatters instance, as explained in the online help. (If you'd rather just follow along with a completed pivot table, I've attached the finished example I made using this tutorial to the bottom of the article.)

Once you've generated your report, open it up in Microsoft Excel and let's have a look at what we've got.

That's ... kind of a lot, I know. Let's break it down a bit.

For starters, each row of the report represents one segment - a block of uninterrupted on-call time for a single user at the same escalation level (primary vs. secondary vs. tertiary, etc.). The data is sorted this way because shifts can span multiple days or weeks or months or just never really even end at all. By breaking shifts into segments, you don't have to wait for a shift to end before you can start calculating on-call time. This also makes it easier to account for temporary absences, rotations that move people out of primary on-call responder positions, and other changes in each person's schedule. 

Also, remember that 24x7 shifts without escalations are not included in the report – these are considered broadcast groups, not on-call schedules.

Creating your pivot table

To create a pivot table (or, as it's known in Microsoft Excel, a PivotTable), click the Insert tab, and then click PivotTable.

In the dialog box, you'll notice the entire worksheet is selected as the range, and so is the option to create the PivotTable in a new worksheet.

Both of these options are exactly what we want, so go ahead and click OK. Excel adds a new worksheet and opens the PivotTable Fields window so we can start sifting through our data.

Building your pivot table

Here's where we want to take a moment and think about what data we want to use to build our PivotTable. We know that, obviously, we want to get the total number of minutes each user spends on-call. But we also want to be able to limit that total to only the times each user held a primary or secondary on-call position. Oh, and of course, to make sure we're billing the right department, we need to know which groups the user was on call for!

Let's start with our most important value: the total number of on-call minutes. We'll make that the basis of our entire report by dragging the On-Call Time (mins) from the list of available fields and dropping it into the Values area.

That's pretty cool, but we know we don't want to pay out almost 25,000 minutes of on-call time, so let's add a few more details to help us tease out some more useful figures.

Drag and drop these fields to the Rows list in the PivotTable Fields area:

• User ID: We need this so we can tell who gets paid.
• Group Name: This tells us which group the user was on call for.
• On-Call Shift Name: This tells us which shift within each group the user was part of.
• Shift Occurrence Start: This helps us identify the date and time of day for each shift where the user accumulated on-call minutes. 

Already we're seeing a much more understandable breakdown – each user's total, followed by the total for each group, and the total for each shift within the group. 

Some of those still seem a little high, so let's try limiting the report to only those minutes where the user was either primary or secondary on-call.

Drag and drop the Escalation Level field into the Filters area, and notice how Excel adds it to the top row of the worksheet. Click the All drop-down field, and then select the Select Multiple Items check box. Adjust the checked items so only 1 and 2 are selected.

Click OK and review the updated PivotTable.

That immediately looks a lot different! Our first user has disappeared entirely because his on-call minutes were all escalation level 5 (management, amiright?) and our totals have reached much more manageable levels.

Customizing it your way

This is really just a quick and simple example about how to use pivot tables to analyze, organize, and summarize your exported Historical On Call report. There are lots of ways to use the available fields to quickly find and total up the information that matters to you.

• If your on-call schedules are based on response windows (for example, only those people scheduled to respond within the first 15 minutes of an incident are considered to be on-call), you can use Escalation Time as your filter instead of Escalation Level.
• If your shifts tend to be broken up a lot, or if you're looking for potential coverage gaps, use Segment Time Start instead of Shift Occurrence Start.
• Use the Absence and Replacement Time fields to curate lists that let you see who's replacing who on a regular basis.
• You can create multiple, independent PivotTables on different worksheets and have them all showing you different information and applying different filters while using the same underlying data.

Try it for yourself! Generate your own Historical On Call report and run it through the PivotTables feature a few times, or download the attached file and play around with some simple sample data.