# 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: