KPI Scorecard

If you create a KPI report you might want to include a table similar to the one on the left. This table is based on one saved search summarizing some different numerical KPI values grouped by customer category.

The example code below will illustrate how to build a table like this by only using 4 columns (Category, Value1, Value2 and Comparison). Simply add additional columns to add more values and comparisons).

CSS

<#assign kpiColor1 = "#e45866" /> <!-- Red -->

<#assign kpiColor2 = "#fac619" /> <!-- Yellow -->

<#assign kpiColor3 = "#afcc8b" /> <!-- Green -->


<#assign colorKey = "#e3e3e3" /> <!-- Gray -->


table.fullwidth {

width: 100%;

}


td {

font-size: 10pt;

padding: 4px 6px;

}


span.detailedOverview {

color: #4d5f79;

font-size: 12pt;

font-weight: bold;

padding: 4px 4px 4px 15px;

}


tr.summaryRow {

font-weight: bold;

margin-top: 6px;

background-color: #d1e1d7;

}


div.detailedOverview {

page-break-inside: avoid;

border: 0.5px;

border-color: #8cb49a;

background-color: #f7f7f7;

corner-radius: 10px;

}

FUNCTIONS

  • funcColorValue:

    • Returns: A HEX color-code.

    • Parameters:

      • value: Numerical value (positive or negative).

      • dir: Enum. Should be either "pos" or "neg". If "pos" function will return green if value is positive, else red. Opposite if "neg". If value is 0, yellow is returned.

      • alpha: Should be a value between 1-100. Sets the transparency of the color returned. 100 is no transparency.

  • varianceNum:

    • Returns: Numerical value (or string "N/A" if at least one parameter is missing value).

    • Parameters:

      • prev: Numerical value (previous period)

      • curr: Numerical value (current period)

<#function funcColorValue value dir alpha>

<#local red = "${kpiColor3}"/>

<#local yellow = "${kpiColor2}"/>

<#local green = "${kpiColor1}"/>


<#if value gt 0>

<#if dir = "pos">

<#local color = "alpha("+alpha+"%, "+red+")"/>

<#else>

<#local color = "alpha("+alpha+"%, "+green+")"/>

</#if>

<#elseif value lt 0>

<#if dir = "pos">

<#local color = "alpha("+alpha+"%, "+green+")"/>

<#else>

<#local color = "alpha("+alpha+"%, "+red+")"/>

</#if>

<#else>

<#local color = "alpha("+alpha+"%, "+yellow+")"/>

</#if>


<#return color />

</#function>




<#function varianceNum prev curr>

<#if prev?has_content &&curr?has_content>

<#return (curr?number-prev?number)>

<#else>

<#return "N/A">

</#if>

</#function>

Body

About the below code:

  • The entire table is enclosed in a <div> (with class "detailedOverview").

  • Since we want to print a summary row at the end of the table, we must create our own variables (VALUE1SUM, VALUE2SUM and VARIANCESUM) and aggregate the data for each result we list through.

  • The VARIANCE is calculated for each line using the function varianceNum. To the function we submit two numerical values (NB! Notice that Value2/Previous value is passed on before value1/current value). The VARIANCE is printed on the row and used for setting the background-color for the comparison column.

  • In the forth column we set the background-color using the funcColorValue function. We submit the already calculated VARIANCE for the current line, the trend direction ("pos" or "neg", see the Functions section for more details) and an alpha value (between 1-100). This third parameter sets the transparancy level of the color code returned.

  • Before starting the new list iteration we aggregate our three custom variables VALUE1SUM, VALUE2SUM and VARIANCESUM by adding themselves with the value for that result/row.

  • When the list is done we print the summary row (class "summarRow") using our custom variables VALUE1SUM, VALUE2SUM and VARIANCESUM.

NB! We use ?number?string["0"] when printing some values. This is FreeMarker syntax for rounding of numbers.

<div class="detailedOverview">


<p><span class="detailedOverview">Summary</span></p>


<table class="fullwidth" margin="5px">

<tr background-color="#d1e1d7" font-weight="bold">

<td><p>Customer</p></td>

<td align="center"><p>Value 1</p></td>

<td align="center"><p>Value 2</p></td>

<td align="center"><p style="font-family:Symbol"><b>Δ</b></p></td>

</tr>


<#assign VALUE1SUM = 0 />

<#assign VALUE2SUM= 0 />

<#assign VARIANCESUM = 0 />

<#list [NAME OF DATASET] as item>

<#assign VARIANCE = varianceNum([item.Value2]?number, [item.Value1]?number)/>

<tr margin-top="2px">

<td background-color="${colorKey}"><p class="item">[item.Category]</p></td>

<td align="right">${[item.Value1]]?number?string["0"]}</td>

<td align="right">${[item.Value2]?number?string["0"]}</td>

<td align="right" background-color="${funcColorValue(VARIANCE,"pos","30")}">${VARIANCE?number?string["0"]}</td>

</tr>

<#assign VALUE1SUM = (VALUE1SUM + [item.Value1]?number)/>

<#assign VALUE2SUM = (VALUE2SUM + [item.Value2]?number)/>

<#assign VARIANCESUM = (VARIANCESUM + VARIANCE?number)/>

</#list>


<tr class="summaryRow">

<td><p>Total</p></td>

<td align="right">${VALUE1SUM?number?string["0"]}</td>

<td align="right">${VALUE2SUM?number?string["0"]}</td>

<td align="right" background-color="${funcColorValue(VARIANCESUM,"pos","100")}">${VARIANCESUM?number?string["0"]}</td>

</tr>


</table>

</div>