Why

The CIO wanted a dashboard similar to the existing Aging dashboard. However the request was to have it based on CIO directs and their service offerings. There was also a desire to have a drillable pie chart will would then populate a table of details grouped by offering. There's several challenges will all of this, but here's the details of how it was accomplished.

How

Getting CIO directs

First challenge was that there's not a super-easy way of getting the CIO directs. Initially we thought we could use some of the Cost Center hierarchy, but this includes things that actually don't apply. Because of this, we need to work up the supervisory organization tree. Also, since this has to be relatively performant, we can't calculate all of what we need to on demand each time. Here's where the scheduled job comes in.

The job is called 'Set offering and CIO direct' and it's relatively simple. It simply populates a table each day will all of the CIO directs and the current offerings that roll up to them. This data is then used to drive the data for the dashboards.

Ticket Aging Filter Configuration

The Ticket Aging Filter Configuration table is used to configure both the Aging dashboard and CIO Dashboards. This allows a lot to be configured without having to touch the code. For those entries with 'Used By CIO Dashboard' set to true these queries will be used. The %ASSN_GRP% and %ASSN_TO% strings in the queries are simply values that get replaced with queries in either the Aging or CIO dashboards. These would need to be included in any new queries defined. The order on this table actually controls which order the pie slices show up. By default HighCharts orders by count I believe.

Portal

The CIO dashboard page is made up of 3 widgets Yale - CIO Pie Chart, Yale - CIO Datatables and Yale - CIO Filter.

Yale - CIO Filter

This uses the u_cio_direct_to_service_offering table populated in the scheduled job to create the dropdown of the current CIO directs. The other thing of interest is the $rootScope.$broadcast calls for the type and CIO direct selections changing. The Yale - CIO Pie Chart widget will listen for these broadcasts and act appropriately.

Yale - CIO Pie Chart

The Pie chart is initially loaded with all CIO directs found in the u_cio_direct_to_service_offering and for incidents. It is using the HighCharts library which is used by ServiceNow.The queries which are run are based on the data in u_ticket_aging_filter_configuration. The way the queries are built via some find and replace could probably be done in a different way, but it works. The server script can probably be DRYed up as well.

The pie is drawn with an event on click that will broadcast the query used for that pie slice. This is used to trigger a refresh of the data in the Yale - CIO Datatables widget.

There are also two $rootScope.$on which will watch for the type or direct changing in the filter, refresh the data and redraw the chart when that data comes back.

Yale - CIO Datatables

This widget finally listens for any time the Pie chart slice is clicked and renders that data based on the query passed to it. This is using the DataTables library. Also, if the type or CIO direct are changed the data is cleared. The fields here are hard-coded and it would likely be better to have them as widget options, but this was created under a massive time-crunch. A link to export the data to excel based on the query and the cio_dashboard task view is also added to the widget for easy export. The better way to do this would probably be to use the DataTables export functionality, but it does happen to work somewhat differently than the OOB APIs and there is some complexity in getting it setup.

Browser not supported