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.

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 36 other followers