Hello everyone,

I though I might share something I did, as I needed a way to display a trend line as a sparkline, like Excel does, and could not use MDX. Of course, I cannot display 2 curves in the same sparkline, but sometimes, the trend line is more useful than the data itself.

The BSH function I created accepts a CSV string containing the values for the sparkline, and returns a new set of values for the trend line. So you can use either or both.

BSH is really great !

I included a sample report.

NOTE: The existing commons-math library from apache included with pentaho is only at version 1.1. I needed 2.1 to get the newest multi-value linear regression functions.

You can download it here: http://commons.apache.org/math/

I replaced the lib in PRD and BI-Server and I did ont see any problems so far. But no intensive testing.

To compute the new points, I use a level 4 polynomial function. It could be changed easily. It also could be adapted for a normal chart.

This is a sample report output:

The BSH function:

Code:

import org.apache.commons.math.stat.regression.*;
String getTrend(String iData) {
OLSMultipleLinearRegression r = new OLSMultipleLinearRegression();
double[] y;
double[][] x;
double[] y2;
int i ;
int n ;
double[] params;
String[] dataArray = iData.split(",");
n = dataArray.length;
x = new double[n][4];
y = new double[n];
// Initialize X matrix and Y values
for (i =0;i<n;i++) {
x[i][0] = i+1;
x[i][1] = Math.pow(i+1, 2);
x[i][2] = Math.pow(i+1, 3);
x[i][3] = Math.pow(i+1, 4);
y[i] = Double.parseDouble(dataArray[i]);
}
// Calculate factors with regression (a)
r.newSampleData(y,x);
params = r.estimateRegressionParameters();
y2 = new double[n];
StringBuilder v = new StringBuilder();
//Recalculate new curve with level 4 polynomial function
// y = a4*x^4 + a3*x^3 + a2*x^2 + a1*x
for (i =0;i<n;i++) {
y2[i] =
(params[3]*Math.pow(i+1,4)) +
(params[2]*Math.pow(i+1,3)) +
(params[1]*Math.pow(i+1,2)) +
(params[0]*(i+1)) ;
v.append(Math.rint(y2[i]));
if (i < n-1) {
v.append(",");
}
}
return v.toString();
}
Object getValue() {
String data = dataRow.get("Value");
String trend = getTrend(data);
return trend;
}