Simple Banding function for KPI Status or KPI Trend

02.11.2009 Hilmar Buchta

SQL Server 2008

Although the way  of defining the KPI status and trend is very flexible it is also somehow circumstantial if you simply want to rate a KPI by its target value. In this case you would have to calculate the status of the KPI as being between –1 (worse) and +1 (best).

In many cases we find simple case statements here:

  1. case
  2. when KPIVALUE(„TestKPI“) >= 0.95 * KPIGOAL(„TestKPI“) then 1
  3. when KPIVALUE(„TestKPI“) < 0.7 * KPIGOAL(„TestKPI“) then 1
  4. else 0
  5. end

This results in the KPI status being –1, 0 or +1. But if you want to blend smoothly between these values you have to use a formula that is a little bit more complex. Especially  if you need to this computation for many KPIs it’s not really nice.

I had some examples for AS stored procedures (sprocs) in this blog before. So here is a very simple one to calculate a linear approximation between the boundaries:

  1. public static double KPIStatusBanding(double bound1, double bound2, double actual)
  2. {
  3.     double factor=0;
  4.     if (bound1 < bound2) factor = 1;
  5.     else if (bound1 > bound2) factor = 1;
  6.     else return 0;
  7.     if (actual < bound1) return factor;
  8.     else if (actual > bound2) return factor;
  9.     else return factor*2 * (actual bound1) / (bound2 bound1) 1;
  10. }

The parameters are as follows:

bound1 worst value
bound2 best value
actual actual value



If bound1 < bound2 bigger values are better, if bound1 > bound2, lower values are better. With this function the calculation is quite easy. First let’s test the function itself with simple MDX queries:

  1. with
  2. member test as ASStatistics!KPIStatusBanding(100,200,175)
  3. select test on 0
  4. from [Adventure Works]

This results in a status value of 0.5.

In order to use the same boundaries as in my first example, I would use this KPI status expression:

  1. ASStatistics!KPIStatusBanding(
  2. 0.7 * KPIGOAL(„TestKPI“),
  3. 0.95 * KPIGOAL(„TestKPI“),
  4. KPIVALUE(„TestKPI“))


As we can see from the KPI browser the values now gets approximated smoothly:


Your email address will not be published. Required fields are marked *

Join #teamoraylispeople

Gestalte mit uns
die Welt der Daten