Requirement
The client, an international commercial insurance company needed an IT Service Management (ITSM) dashboard to keep track of its performance against agreed Service Level Agreements (SLAs). The ITSM software used to track incident tickets and service requests did not calculate the SLA performance and could not be customised.
Challenges
All the data required to calculate the SLA performance was in the ITSM system but processing was not straightforward. SLAs had to be calculated for two types of ticket, four priorities and nine departments consisting of seventeen teams.
The client also wanted the month to date position which meant working with 6 ticket statuses. The results had to be readily understood, and have drill-down capabilities such as identifying breached tickets for example.
ITSM dashboard showing SLA performance by team and ticket type
Solution
The solution was a custom built VBA system based on Excel that had 2 spreadsheets and 8 VBA procedures. Ticket data was extracted from the ITSM system using three custom journal extracts and reports, then imported into a Statistics worksheet for pre-processing. From there the processed data was imported to the Dashboard worksheet. Two spreadsheets were used to keep the large volume of input data separate from the Dashboard which needed to load quickly and have good performance.
Design Highlights
The solution used VBA to import data and to perform the complicated calculations needed to work out the SLA results. Functions used include: array formulas, scroll bar and radio button controls, conditional formatting, dependent drop downs and data validation. It also featured an innovative ranking method used to manage the graphs and axis labels, and to create a dynamic, scrollable list of filtered and sorted items.
This example demonstrates some advanced Excel techniques to produce a clean, user-friendly dashboard.