Jaspersoft BI Tools

iReport,Jaspersoft Design Studio,Jaspersoft Server, Schema Workbench,Analysis,Dashboards, Self Service BI, Domains,Topics,Ad-Hoc Reports, Jaspersoft ETL, Embedding BI,HTML-5 charts, Fusion Charts,JFree Charts,Drill down reports , Drill down Dashboards,Visualize.js, Performance Tuning, D3 Charts, Jaspersoft Customization

Tuesday, February 18, 2014

Cross Tab Calculations in iReport - Converting seconds to Time using Expression in Measure variables.

Hi Guys...!!!
A work around in Japser iReport Cross Tab component. 
Scenario :
Assume you have time filed(format: hh:mm:ss, it could be java.sql.Time or String type) and want to add the times...
Directly you can not add the time as you generally do with Integer or Long types.
1) Convert the time to seconds and make the type as Integer or Long using SQL query.
2)  Lets say the filed name is $F{time} which is in seconds and type is Integer.
3) Fill the cross tab with this measure. You can find that the values will fill on cross tab with $V{time} variable.
4) Click on the variable (i.e., On $V{time}) go to the properties and then write the below expression  at Text field properties.
Type: java.lang.String for the Measure.
5) At last convert the expression to string using .toString() function.

 Convert seconds to Time on cross tab measure
                                                    $V{time} -
                                                                        java.util.concurrent.TimeUnit.HOURS.toSeconds( java.util.concurrent.TimeUnit.SECONDS.toHours($V{time}))

        $V{time} - java.util.concurrent.TimeUnit.HOURS.toSeconds(java.util.concurrent.TimeUnit.SECONDS.toHours($V{time}))
                                                                                                            $V{time} - java.util.concurrent.TimeUnit.HOURS.toSeconds(java.util.concurrent.TimeUnit.SECONDS.toHours($V{time}))


The source java code for the expression is :
JAVA Code:
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.concurrent.TimeUnit;

public class TestDate {

  * @param args
  * @throws ParseException
 public static void main(String[] args) throws ParseException {
  // TODO Auto-generated method stub
  String timee="31:58:55";
  DateFormat format=new SimpleDateFormat("hh:mm:ss");
// format.parse(timee).getSeconds();
int seconds = format.parse(timee).getHours()*3600+format.parse(timee).getMinutes()*60+format.parse(timee).getSeconds();

    String[] parts = timee.split(":");
  long millis = TimeUnit.HOURS.toSeconds(Long.parseLong(parts[0]))
            + TimeUnit.MINUTES.toSeconds(Long.parseLong(parts[1])) + Long.parseLong(parts[2]);
  System.out.println(millis+" in Seconds");
  //System.out.println(seconds + " Converted to Seconds");
  long s_hour = TimeUnit.SECONDS.toHours(millis);
  long tempSec = millis - (TimeUnit.HOURS.toSeconds(s_hour));
  long s_minute = TimeUnit.SECONDS.toMinutes(tempSec) ;
  long tempSec1 = tempSec - (TimeUnit.MINUTES.toSeconds(s_minute));
  long s_seconds = TimeUnit.SECONDS.toSeconds(tempSec);
  System.out.println(s_hour  + " in Hours");
  System.out.println(tempSec+" Which is a reminder");
  System.out.println(s_minute + " in Minutes");
  System.out.println(tempSec1+" in Seconds");