BSHExpressions - when we need control over reporting

Following pentaho forums is a great way to find topics for a blog. This time I used Gunter's question.

I need to make a simple report that show the duration of an event and sum it per day. The type of duration in the database is time (MySQL).

I have tried summing the duration values, but apparently the ItemSumFunction cannot handle the summing. So i converted the time to an integer, made the sum using the ItemSumFunctio, but now i don't know how i can reformat it to show like 'hh:mm:ss'

I'm a very big fan of the latest approach; I think that using longs in the fact table just simplifies everything. Then we only need to solve the formatting aspect of it. Since it's a report we're talking about, we'll use a BSHExpression. (JFreeReport is awesome, there's always more than one solution available for each problem, this is just one of them).

We can't use a simple date formatter because we want dates also over 24h. So I created the following BSHExpression:

import java.text.SimpleDateFormat;
SimpleDateFormat formatter = new SimpleDateFormat("mm:ss");

String secondsToFormattedDate(n){
d = new Date(n * 1000);
return ( d.getTime()/3600000 + ":" + formatter.format(d));
}

String getValue(){
long l = dataRow.get("CategoryAmountExpression").longValue();
return secondsToFormattedDate(l);
}

There are a lot of options to do this code, obviously; we could easily avoid the date object, but it's a way to show how we can invoke other classes.




The final result of this is what we expected: