Right Outer Join

9 September 2009

JasperReports First Dates

Filed under: JasperReports — Tags: , , , — mdahlman @ 16:09

Date Calculations in JasperReports

The Problem

When working with JasperServer, JasperReports, and iReport it’s common to want to perform date calculations. This is surprisingly hard to do in Java expressions. In pseudo code the desired result can be extremely easy to express. For example, “MyDate + 1 day” or “The first day of the month before the month in which MyDate occurs”. The problem is that while Java has all of the pieces you need to perform such calculations, it requires you to instantiate Calendar objects and use these to operate on Dates. But this isn’t possible in a simple JR parameter expression. So what can you do?

My standard solution was to push these date calculations to the SQL query. This works for many purposes, but it has drawbacks. For one thing SQL has lots of date functions, but they are painfully verbose for some needs. Worse, there are cases where I need the date calculation in the report but I don’t need it in the query. Frankly, I feel dirty adding an extra column to my result set just to calculate what the first day of this month is. And of course pushing the calculation to the SQL query isn’t an option at all when your data source isn’t a SQL database.

The Solution

I was recently introduced to the awkwardly named “Lang” component of the Apache Commons project. It’s homepage beautifully summarizes what it does. “The standard Java libraries fail to provide enough methods for manipulation of its core classes. The Lang Component provides these extra methods.” Somehow it manages to both subtly mock and mightily contribute back to the Java world simultaneously. Guillaume, thanks for the introduction; I’m sure Lang and I will become close friends.

It has a class called DateUtils with methods like addDays, parseDate, round, truncate, and setDays. Are you already salivating? These static functions allow you to perform all sorts of new data calculations with ease.

So let’s get started. I created a report that uses iReport’s built-in “Empty datasource” so it will be easy for any iReport user to work with it. I ran it on 9 Sept 2009, and it looks like this:

Date manipulations using Commons Lang's DateUtils

Date manipulations using Commons Lang's DateUtils

Do you see it? “MyDate + 1 day” There it is! It gets better. “The first day of the month before the month in which MyDate occurs” That’s there too! Look, it’s already tomorrow in New Zealand. Maybe they’ve already read my post even though I’m not quite done in my timezone. But I digress.

Notice "One import set"

Notice "One import set"

The syntax is easy to follow. For example MyDate + 1 day is just “DateUtils.addDays($P{MyDate},1)”. Don’t forget that you need to import the relevant package. In iReport click on the root report element, and in its properties you’ll find Imports. Add “org.apache.commons.lang.time.*” (without the quotes) as an import. If you don’t add this import then you would need to use the syntax “org.apache.commons.lang .time.DateUtils.addDays ($P{MyDate},1)”. It works… but it’s a lot harder to read. Thanks to CBox for reminding me of this.

I have attached the report here so that anyone can give it a try (or browse all Right Outer Join files). As always, I renamed it from .jrxml to .odt because WordPress won’t let me upload .jrxml files. Please rename it after downloading.

If anyone discovers useful examples that ought to make it into this report, please let me know. I’ll be happy to update the report and re-post it.

The Ignored Stuff

As useful as this is, I have ignored some highly useful related ideas. For example, what if I want to deploy a report to JasperServer so that the user can schedule it with appropriate start and end dates? It’s easy for them to choose specific start and end dates… but how can they select “the start of the month” or “the end of the year”? There’s no easy way. Using the techniques mentioned above it’s possible, but it’s certainly not trivial. Maybe I’ll get a chance to address this in another post soon.

About these ads

8 Comments »

  1. Generally I do not post on blogs, but I would like to say that this post really forced me to do so, Excellent post!

    Comment by Tnelson — 30 September 2009 @ 17:48

  2. I don’t know If I said it already but …Cool site, love the info. I do a lot of research online on a daily basis and for the most part, people lack substance but, I just wanted to make a quick comment to say I’m glad I found your blog. Thanks, :)

    A definite great read..Jim Bean

    Comment by JimmyBean — 1 October 2009 @ 02:04

  3. I’m very interested in date math. Loaded your demo of “DateUtils”. It complained about the “splitType” being in a band, so I removed those references. Now, it complains that it will not resolve the “DateUtils” even though the import of “org.apache.commons.lang.time.*” is set. I am running iReport 3.5.0.

    All I want to do is create a report title that says “Monthly Report for MMMMM, YYYY” where MMMMM is the name of last month and YYYY is the year that goes with it.

    Any help is much appreciated.

    Comment by Dick Cooman — 15 January 2010 @ 19:56

    • Actually, you don’t need DateUtils to do what you want. You should be fine with the following(of course you’ll need to replace “new java.util.Date() with an appropriate Date parameter):
      “Monthly report for ” + new SimpleDateFormat(“MMM, yyyy”).format(new java.util.Date())

      I put a more complete answer on the jasperforge.org site since it’s more likely to be found by more people there. So please refer to that to solve the import problem and the splitType problem.

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

      Comment by mdahlman — 18 January 2010 @ 12:04

  4. Your report works perfectly! However, when I use the same code in Version 4.0, I get an error – Error evaluating expresssion : Source text : DateUtils.addDays($P{MyDate},-1)

    (I needed yesterday instead of Next Day as in your example)

    Any thoughts?

    Comment by TSnyder — 18 March 2011 @ 14:00

    • I’m glad it worked well for you. It’s working for me unaltered in iReport 4.0, and it works fine with -1 to get yesterday as well. So there must be a difference in your 4.0 setup. Perhaps it’s as simple as commons-lang missing from the classpath.

      If you still can’t figure it out, please post to the iReport forum on jasperforge.org.

      Comment by mdahlman — 18 March 2011 @ 15:42

  5. Your file won’t open. Says it’s corrupt.

    Comment by Peter J — 4 August 2011 @ 13:09

    • I don’t know what I did, but that file was indeed broken. I created a new .zip of the sample reports, and I uploaded it to GoogleDocs. The article above is updated with the relevant links now.

      Comment by mdahlman — 21 December 2011 @ 11:44


RSS feed for comments on this post. TrackBack URI

Go on... leave a reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 36 other followers

%d bloggers like this: