Counting the colors of your scorecard items can provide valuable insights into your organization's performance. While Spider Impact doesn't have a built-in feature for this, you can achieve it using a clever workaround with datasets. This article and accompanying video will show you how to set up self-referential datasets to count and analyze scorecard item colors, opening up new possibilities for tracking and reporting on your KPIs.
First you need to set up an import connection to your Spider Impact database. If you're a hosted customer, please contact support and we'll get it set up for you.
Next you create a dataset based on that import connection.
This is the SQL to use in the import connection:
SELECT
org.organizationnodename AS 'Organization',
sn.scorecardnodename AS 'Scorecard Item',
sn.scorecardnodeid AS 'ID',
CASE sn.nodetype
WHEN 0 THEN 'Root'
WHEN 1 THEN 'Generic'
WHEN 2 THEN 'Perspective'
WHEN 3 THEN 'Objective'
WHEN 4 THEN 'Metric'
WHEN 5 THEN 'Linked'
WHEN 6 THEN 'Docking'
ELSE 'Unknown'
END AS 'Scorecard Item Type',
score.score,
CASE HEX(score.color)
WHEN '00A9C3' THEN 'Turquoise Dark'
WHEN '60CBDB' THEN 'Turquoise'
WHEN 'CDE9FA' THEN 'Blue Light'
WHEN '0074BB' THEN 'Blue Dark'
WHEN '1A94DA' THEN 'Blue'
WHEN 'CCF3F9' THEN 'Turquoise Light'
WHEN '3654D4' THEN 'Periwinkle Dark'
WHEN '839AFF' THEN 'Periwinkle'
WHEN 'C4CFFF' THEN 'Periwinkle Light'
WHEN '6D1DB8' THEN 'Purple Dark'
WHEN 'AD5AFA' THEN 'Purple'
WHEN 'DBB5FF' THEN 'Purple Light'
WHEN 'A31DB8' THEN 'Fuchsia Dark'
WHEN 'E55BFA' THEN 'Fuchsia'
WHEN 'F5B5FF' THEN 'Fuchsia Light'
WHEN 'C93C20' THEN 'Red Dark'
WHEN 'F25131' THEN 'Red'
WHEN 'F7A695' THEN 'Red Light'
WHEN 'EB6600' THEN 'Orange Dark'
WHEN 'FF8800' THEN 'Orange'
WHEN 'FFC889' THEN 'Orange Light'
WHEN 'EBB000' THEN 'Yellow Dark'
WHEN 'FBCC3B' THEN 'Yellow'
WHEN 'FBE499' THEN 'Yellow Light'
WHEN '619900' THEN 'Green Dark'
WHEN '96CD00' THEN 'Green'
WHEN 'CAE779' THEN 'Green Light'
WHEN '2D3640' THEN 'Gray Darker'
WHEN '8E9699' THEN 'Gray Dark'
WHEN 'BCC2C4' THEN 'Gray'
WHEN 'D7DEE0' THEN 'Gray Light'
WHEN 'EDEDED' THEN 'Gray'
WHEN '98A3B3' THEN 'Slate'
WHEN 'ABBFD8' THEN 'Infantry Blue'
WHEN 'FFFFFF' THEN 'White'
ELSE 'Unknown'
END AS 'Color',
score.actualvalue AS 'Actual Value',
nativecal.calendarname AS 'Native Calendar'
FROM
calendars currentcal
JOIN calendarperiods currentperiod ON currentperiod.calendarid = currentcal.calendarid
JOIN scorecardnodescores score ON score.calendarperiodid = currentperiod.calendarperiodid
JOIN scorecardnodes sn ON sn.scorecardnodeid = score.scorecardnodeid
JOIN scorecardnodes rootsn ON rootsn.scorecardnodeid = sn.scorecardrootnodeid
JOIN organizationnodes org ON org.organizationnodeid = rootsn.organizationnodeid
LEFT JOIN calendars nativecal ON nativecal.calendarid = sn.calendarid
WHERE
currentcal.standardcalendartype = 3
AND YEAR(enddate) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(enddate) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
As you can see here, this will create one dataset record for the current monthly performance of all scorecard items with columns like the organization, the scorecard item, the score, etc. We'll use ID for the record ID column.
We'll set it up to import new data every night at midnight, and we'll choose to replace all records.
When we're done we have a dataset that we can explore to get quick answers about our scorecard item performance. For example, here I've added a filter to show only the 99 red KPIs, and we're breaking them down by organization.
From here you can do all kinds of things, like create a dataset KPI to track the percent of red KPIs every month so you can track your color counts over time. Or, you can write a report counting the objectives of each color for every organization. You can even create dashboards showing how many items of each color you have.
We've created a video to walk you through the process and to show you a few possibilities.