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.
Solution:
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
(
   java.util.concurrent.TimeUnit.SECONDS.toHours($V{time})
   +":"+
   java.util.concurrent.TimeUnit.SECONDS.toMinutes(
                                                    $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}))
      )
      -
      (
         java.util.concurrent.TimeUnit.MINUTES.toSeconds(
                                                            java.util.concurrent.TimeUnit.SECONDS.toMinutes(
                                                                                                            $V{time} - java.util.concurrent.TimeUnit.HOURS.toSeconds(java.util.concurrent.TimeUnit.SECONDS.toHours($V{time}))
                                                                                                            )
                                                        )
      )
   )


).toString()


The source java code for the expression is :
http://www.compileonline.com/compile_java_online.php
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");
  }
}