US and Worldwide: +1 (866) 660-7555
Results 1 to 3 of 3

Thread: Displaying Trend in sparkline

  1. #1
    Join Date
    Mar 2009

    Default Displaying Trend in sparkline

    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:

    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:

    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)
        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)) ;
            if (i < n-1) {
        return v.toString();
    Object getValue() {
        String data = dataRow.get("Value");
        String trend = getTrend(data);
        return trend;
    Attached Files Attached Files
    Last edited by haubuchon; 06-22-2010 at 10:30 PM.

  2. #2
    Join Date
    Mar 2009


    After further testing, I saw what seems to be a conflict with mondrian, using commons-math library.

    I'm not a java developer, si I'm asking to anyone out there if they know a way I could use in BSH (BeanShell) a different version of a jar than the one used in pentaho, (located in web-inf/lib) without causing a conflict with the existing jar ?

    Thanks !

  3. #3
    Join Date
    Apr 2008


    I would recommend posting the conflicting jar question in the mondrian forum with a link back to this thread. This should increase the probability of a response from someone with mondrian know-how.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts