Date calculation in iReport

The Problem

When working with JasperServer, JasperReports, and iReport it is a common problem to perform date calculations. You would usually want to calculate dates to fill parameters when calling sub reports for example. iReport expressions offer a way to do calculations in Java or Groovy. But each calculation, like the definition of a parameter value, must consist of exactly one expression. When trying to define a complex date object, describing the last day of the last month for example, most people find it very difficult, if not impossible, to do this in a single expression. I have seen different suggestions to deal with the situation. Most recommendations seem to go towards doing all date calculation in your SQL query or using an additional Java library. Both ideas help remedy the problem. Both also have drawbacks. SQL based results may force you to design the report around the date calculation, making it harder to unterstand. Additional Java libraries may increase the complexity of your development setup, since each JasperServer instance and each iReport developer needs matching versions of the library. I would like to propose another possibility, that tries to solve the problem doing simple Groovy expressions, and does not require additional libraries.

The Idea

The input for a report usually consists of a date. The date to run the report for. In an ideal solution the report logic calculates all other required dates internally. To do this properly you would usually depend on the Java Calendar class. It turns out however that several function calls are required to do date calculations properly using the Calendar class. Suppose we already have the date to run a report for in the parameter runDate. The following lines of code would calculate the last day of the last month.

Calendar cal = Calendar.getInstance();
cal.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate());
cal.add(Calendar.MONTH, -1);
cal.set(Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH));

return cal.getTime();

The problem is of course, that iReport would not allow multiple expressions and a return statement. We need to come up with a way to put all this into a single expression. Don’t worry. It can be done. Let’s do this step by step. First of all we need to convert the local variable cal into an iReport parameter. Just add a parameter of type java.util.Calendar with default value Calendar.getInstance() and ask iReport not to prompt for its value. This is what the parameter definition should look like.

Now we have a calendar object available in our expressions, that can be referenced like any other parameter using $P{cal}. We could rewrite our pseudo code now. Notice how the explicit allocation of the cal object is not necessary any more, saving us one assignment expression.

$P{cal}.set($P{runDate}.getYear()+1900,$P{runDate}.getMonth(),$P{runDate}.getDate());
$P{cal}.add(Calendar.MONTH, -1);
$P{cal}.set(Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(Calendar.DAY_OF_MONTH));

return $P{cal}.getTime();

Now it is time to have a close look at the documentation for the Calendar class. The add and set methods we use for our calculations do not have a return type. In a boolean context, calls to add and set evaluate to false.  We can exploit this fact and put all three calls into a single expression using the logical OR operator ||.

(
 $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
 $P{cal}.add(Calendar.MONTH, -1) ||
 $P{cal}.set(Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(Calendar.DAY_OF_MONTH))
);

return $P{cal}.getTime();

We are almost there. We can use the ? operator to write the entire calculation in a single expression, also avoiding the return statement. Take a minute to read up on the ? operator if you are unsure how it works. We know for a fact that the calendar calculation expression we wrote evaluates to false, because each statement evaluates to false. So a single expression that does the calculation and returns the resulting date looks like the following.

(
 $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
 $P{cal}.add(Calendar.MONTH, -1) ||
 $P{cal}.set(Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(Calendar.DAY_OF_MONTH))
)
? null : $P{cal}.getTime()

This is it.  We are doing a multiline calculation using the Calendar class in a single expression. You should probably put an expression like this into a report variable and use the variable whenever you need it.

Conclusion

The suggested technique solves the problem of doing multiline date calculations without using external libraries, while also keeping your date calculations out of your SQL. The price to pay is getting used to a slightly inconvenient syntax. For me this is usually a small price to pay, especially since the calculations can be encapsulated in iReport variables or parameters. See the example report for details.

Example Report

You may want to download an example report created in iReport 3.7.3, which demonstrates the suggested calculation technique by defining and printing a few date variables.

Related Literature:

47 comments to Date calculation in iReport

  • FS

    Hi Slawomir,
    Interesting article. I tried the expressions in a Jasper Report with Java as the expression language. I get an error saying that || is not defined for return type void. Any idea how we can get around this problem?
    Regards,
    FAhim

  • Slawomir Chodnicki

    Hi Fahim,

    I was using the expressions inside iReport, and I believe it has Groovy on per default. Try using Groovy as the expression language, and let us know if it worked.
    Regards,

    Slawo

  • FS

    Hi Slawo,
    Thanks for the quick reply. I have seen your sample report and it works fine. We use a custom component in Flex to display a Jasper Report in an I-Frame and the documentation for this component suggests using Java as the expression language. Is it still possible to use the void return type in an expression in iReport?
    Regards,
    Fahim

  • Slawomir Chodnicki

    Hi Fahim,
    I’ve been testing a few things but as of yet I did not find a solution for this in pure Java. The trouble is that I don’t see a way to chain multiple calls to a void returning method like the calendar methods. My suggestion is that you check out a library that does does not have this flaw, i.e. you can simply chain the calls. Check out the apache commons DateUtils on this one. If you can incorporate this lib into your project, date expressions should be no problem any more.

    http://commons.apache.org/lang/api-2.4/org/apache/commons/lang/time/DateUtils.html

    Let me know if it worked :-)

    Slawo

  • FS

    Hi Slawo,
    As you may have guessed, I am new to Jasper Reports and right now I am looking at Scriptlets to see how I can use external class methods in reports. I will look at the link you have provided and let you know how I go.
    Thanks again for your contributions.
    Regards,
    Fahim

  • Michelle Rosenaur

    Your instructions are very well written, the example is very helpful and you provided just what I needed to make the end-users happy. Thank you Slawo!

  • Slawomir Chodnicki

    Thanks a lot :)

  • Andreas Zielke

    Hi Slawomir,

    thanks for this neat posting.

    You can also use the GroovyShell to circumvent the limitation of the JasperReport expressions.
    Define the expression for your second parameter like so:


    new GroovyShell(new Binding([runDate:$P{runDate}])).evaluate("""
    Calendar cal = Calendar.getInstance()
    cal.setTime(runDate)
    cal.add(Calendar.MONTH, -1)
    cal.set(Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH))
    return cal.getTime()
    """)

    …you don’t even need a “cal” Parameter.

    Kind regards
    Andreas

  • Slawomir Chodnicki

    Hi Andreas,

    Thanks for this! That’s great. It makes us Turing complete on JaperReport expressions again :)

    Cheers

    Slawo

  • Wow, that’s great, and very well written. Took me a while to find this page. Wish I found it a few hours earlier!

  • Bobby

    Hy Slawo i’m new to jasper and currently trying to create a report example for travel application,
    what i want to ask is:

    I created a query to retrieve how many times there are transactions per months, this is it:

    select MONTHNAME(perjalanan.tanggal) as “Bulan”,
    Count(*) as “Jumlah perjalanan”
    from perjalanan
    group by MONTHNAME(perjalanan.tanggal)
    order by MONTHNAME(perjalanan.tanggal) asc

    i create this using ireport 3.7.6 and what i got just strange codes number like : [79,112,56] like that.. i need to show month names in that field..
    how can i do this?

    ————–
    October | 23
    September | 5
    ————–

    thank you for your time :)

  • Slawomir Chodnicki

    Hi Bobby,

    your query looks entirely reasonable to me. What does the preview button of the query editor show you? My guess is that your text fields are filled incorrectly for some reason. Maybe try to follow a few beginner’s tutorials on iReport. I think this one gives a good, and quite comprehensive introduction. http://www.opentaps.org/docs/index.php/Tutorial_iReports

    Cheers

    Slawo

  • Flor Mendiola

    Thank you!

    It is very helpful and clever. I’ve been searching for a solution for 2 days and I finally found one that made sense.

    Cheers,
    Flor

  • Well, I tried your dates.jrxml in iReports 4.0.1 but it doesn’t work.
    Is it not working with 4.0.1 in general? Why?

    Thank you a lot for your help!

  • Slawomir Chodnicki

    Hi Peter,

    I just tested with a freshly downloaded 4.0.1 iReport version and the report works for me. Please make sure you run it with an empty datasource. If you’d provide more details on the error I might be able to help.

    Cheers

    Slawo

  • Jessie

    I have gotten this code to work with calculating the first day of the week. My problem occurs when I try to group based on this date….because it is still in seconds EVERY column is a new header.
    I need to set the date to MM/dd/yyyy excluding the HH.mm.ss before it is converted to the first day of week.
    I am new to groovy, using iReports
    Suggestions?

  • redBeard

    This is excellent. This solves some problems for me.

    I did find a slightly different solution however. I created the ‘cal’ parameter and ‘lastMonth’ variable.

    However, rather than setting the ‘cal’ date in every variable (and then calculating a new value), I do a bit less in the ‘lastMonth’ variable calculation and a bit more in the text field displays.

    My lastMonth calculation:

    (
    $P{calendar}.add(Calendar.MONTH, -1)
    )
    ? null : $P{calendar}.getTime()

    My last month report period display text field:

    new SimpleDateFormat(“MMMM”).format($V{lastMonth})
    + ” 1 – ”
    + new SimpleDateFormat(“MMMM”).format($V{lastMonth})
    + ” ”
    + $P{calendar}.getActualMaximum(Calendar.DAY_OF_MONTH)

    But again, your insight helped me tremendously.

  • Slawomir Chodnicki

    Hey redBeard,

    yeah, shifting calculations from the vars to the output is a nice idea, thanks for sharing!

    Cheers
    Slawo

  • Palwinder

    Great trick…..

  • Florian

    Awesome, thanks for posting this.
    Helped me a lot.

  • AV

    Very helpful article. Thank you sooooooooo much!!!

  • Loganathan

    Thanks a lot…nice article…its saved my time:)

  • Patrick

    Hi, great tutorial for exactly the problem that I have.

    As I’m not a Java developer I’m not familiar with the classes. My problem is that I try to use this with a report on a jasperreports server 4.0. I get the error message:
    com.jaspersoft.jasperserver.api.JSExceptionWrapper: Report design not valid : 1. java.lang.ClassNotFoundException: java.util.calendar

    Does someone know what to do?

  • Poonam

    In my case, i have to get the input from user as date & need to add 1 day , & pass these dates as start & end dates to procedure. I need to get the results from the procedure. so when i use $V{endDate} as $P{startDate} +1. I try to run it with data source. I get exception lile “com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ’$V’ in ’field list’ ”

    Please suggest what to do in this case.

  • wenrdog

    How would you use the GroovyShell in an iReport Parameter? For example, I’ve been having issues with Unknown Class when using GroovyShell.

  • cash

    Rock on, man. This worked great. So thankful for this excellent article. FYI I mentioned it on this forum post:

    http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=74185

    mainly because I wasn’t sure how to use the $V’s in a query. Apparently you can’t but you can pass the $V from the master report into a subreport as a string.

    @Poonam: check out that post.

    Sweet! Thanks again.

    Cheers
    Cash

  • cash

    Sorry, I meant as a $P string.

  • Slawomir Chodnicki

    Cash,
    Thanks very much for the comment!
    Cheers
    Slawo

  • siva

    (
    $P{cal}.clear() ||
    $P{cal}.set(Calendar.YEAR, $P{year_current})||
    $P{cal}.set(Calendar.WEEK_OF_YEAR, $P{week_current}) ||
    $P{cal}.setFirstDayOfWeek(Calendar.MONDAY) ||
    $P{cal}.setMinimalDaysInFirstWeek(4)
    )
    ? null : $P{formatter}.format($P{cal}.getTime())

    The above code calculates the start date from ‘week_current’ and ‘year_current’ input’s. The problem is that the code calculates the startDate in ireport but not in jasper server.

    When I run the report on jasper server, the display mode (pop-up window) comes up blank without any input controls.

    But when I change the above code to match one of your date calculations,the same report runs without any issues on jasper server i.e, the display mode (pop-up window) shows the defined input controls.

    I would appreciate if you can let me know what is wrong on my date calculation expression which is preventing the input controls to appear on jasper server.

  • siva

    Is there anybody who can reply to my previous post I made about startDate calculation

  • richie

    yes siva, i have the same problem but no answer

  • Ruba

    Hi, I am new to jasper and my problem is to get the days from the fields of two timestamps fields. I have got the difference taking the two feilds in variable but this is not good for the report I am designing. Kindly let me know how can I get the difference of two feilds through the sql query.

  • Dave

    Your example worked very well. Thanks for sharing!

  • Anonymous

    Suppose you have a Parameter PARAM1 nd you want to add 366 days to param1 then you can do it by following way
    1) Declare another parameter say $P{finalDate}

    2) Code like below for $P{finalDate} as the Default Value Expression
    new Date($F{PARAM1}.getTime() + 366L*24*60*60*1000)

    Dont forget to put L after 366 . Without putting L , it may not work properly and may not give accurate date.

    It will add 366 days to PARAM1

  • Ryan

    Don’t know if anyone is still looking at this thread.
    It is a great helper.

    Only question I have is where would you put a field value?

    I am trying to get the weeks in the year from an actual field value.

  • Anonymous

    Hi

    I am using iReportDesigner 5.1.0 and cant find a way to add a parameter of type java.util.Calendar with default value Calendar.getInstance().

    I am trying to run the example as you have provided and gets error “unable to resolve class java.util.Calendar.getInstance”

    Any help appreciated

  • Numerinico

    Thanks ! It helped me a lot :)

  • ljhvfejh

    When I found this site I thought, okay I finally found a solution to my problem, but nope :(
    I am trying to use the concept described above to create a date formatter and to set the time zone. This is what I have for the parameter:

    and here is the variable:

    However I keep getting the error:ompiling to file… C:\Users\305048\Desktop\CommonFooter.jasper -> C:\Users\305048\Desktop\CommonFooter.java
    Errors compiling C:\Users\305048\Desktop\CommonFooter.jasper!
    it.businesslogic.ireport.ReportClassLoader@1697a63 net.sf.jasperreports.engine.JRException: Errors were encountered when compiling report expressions class file: 1. Type mismatch: cannot convert from void to boolean                 value = (java.lang.String)((((java.text.SimpleDateFormat)parameter_DateFormatter.getValue()).setTimeZone(TimeZone.getTimeZone(“GMT”)))?”":((java.text.SimpleDateFormat)parameter_DateFormatter.getValue()).format(new Date()));                                             2. Type mismatch: cannot convert from void to boolean                 value = (java.lang.String)((((java.text.SimpleDateFormat)parameter_DateFormatter.getValue()).setTimeZone(TimeZone.getTimeZone(“GMT”)))?”":((java.text.SimpleDateFormat)parameter_DateFormatter.getValue()).format(new Date()));                                             3. Type mismatch: cannot convert from void to boolean                 value = (java.lang.String)((((java.text.SimpleDateFormat)parameter_DateFormatter.getValue()).setTimeZone(TimeZone.getTimeZone(“GMT”)))?”":((java.text.SimpleDateFormat)parameter_DateFormatter.getValue()).format(new Date()));                                             3 errors

    So how do I go around this?

    Thanks!!!

  • ljhvfejh

    parameter

    new SimpleDateFormat(“EEE, MMM d, yyyy hh:mm:ss a z”)

    variable
    ($P{DateFormatter}.setTimeZone(TimeZone.getTimeZone(“GMT”)))?”":$P{DateFormatter}.format(new Date())

  • Gerardo Gzz

    Thanks a lot for post this article, helped me a lot!

  • pandiarajan

    i have one problem in date calculation i ireport

    description
    i have to find minimum and maximum date in the given date list.
    for example dates are coming from 30/9/2014 to 5/10/2014. so when i lunch the report from date 5/10/2014 to date 30/9/2014 as displayed. suppose if i select date with in same month no issue and if i select 5/9/2014 to 10/10/2014 also no issue. the first case only issue. please help me to short out this issue.

    thanks in advance.
    regards
    Pandiarajan E

  • Gerardo

    Hi, I´m using iReportDesigner 4.5.1(have to use this version where I work), and tried your dates JRXML file and it works fine.
    I wanted to do a report calculating the previous Business Day, and using your ideas I was able to do it, the problem is when I run my project I get the following error (just the first line for an example):

    “net.sf.jasperreports.engine.JRException: Errors were encountered when compiling report expressions class file: org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:

    calculator_myReport_1416417261864_661985: 190: unable to resolve class java.lang.Calendar”

    I have looked in the comments to check if the expression is set to Groovy and the properties are the same, now I tried to copy paste my project to your dates JRXML file and I receive no errors, and my report runs with the date I want to calculate. Why is this? Does anyone have any idea?

  • La vision

    I need help please, i want to calculate quaters of year in my report but i don’t how to do,someone have a solution?
    example:
    YEAR :2015 QUATER:4

  • parlad

    hi there , i perform add, subtract, multiply and divide operation in single variable and it give me a wrong data in total. Is there a possibility of needing to calculate them in different variable?
    my operation looks like : $F{a}.subtract($F{a}.multiply($F{c}.divide($F{d})))
    and it gives me wrong calculation

    thanks in advance

  • DJones

    when I compile the code below as provide – I receive error
    (
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.MONTH, -1) ||
    $P{cal}.set(Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(Calendar.DAY_OF_MONTH))
    )
    ? null : $P{cal}.getTime()

    ERROR:
    The operator || is undefined for the argument type(s) void, void

    This is my first adventure into iReport which is the report-generator for an Application. I need to customize a report. Can anyone assist? I’ve researched and not able to resolve this error message within a reasonable time. And – no support to contact.

  • IGOR

    Hi, thank you for this great article! After all these years it is still helping people :-)

    Did anyone solved this using Java as report language? As I can’t use Groovy because will have to rewrite a lot of my reports.

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>