Right Outer Join

3 March 2010

JasperReports Second Dates

Filed under: iReport, JasperReports, JasperServer — Tags: , , , , — mdahlman @ 18:11

JasperReports Second Dates – Meet the Relatives

The Problem

I want a simple way to run a report for “Yesterday” or “Next Month”. It’s easy enough to parameterize the StartDate and EndDate; therefore the user can get whatever dates she wants. But what about schedules? If I know that the report always runs for “Last Month” then it is easy enough to put the date calculations into the SQL query. But what if my data source isn’t SQL? What if I want to use the same report for a weekly summary and a monthly summary?

The Solution

  1. Create a parameter named DateRange that lets users select the appropriate values like “Tomorrow”, “Next week”, etc.
  2. Create other parameters to convert DateRange into the corresponding StartDate and EndDate values. These parameters will rely on Java Commons Lang (specifically the DateUtils class) to calculate the dates.
  3. Create an input control in JasperServer associated with DateRange so users can select an appropriate value. Do not create input controls for StartDate and EndDate since these get calculated automatically.

The idea is simple enough, but the details are not trivial. I began by creating a simple table to query. It has a single date field populated with one value for each day in 2010. Using this my report can use this simple query:

SELECT the_date
FROM date_helper
WHERE
    the_date >= $P{StartDate}
and the_date <  $P{EndDate}

One of the most common problems with date ranges is missing values because truncating time values from a date. For example, if I plug “today” as the StartDate and the EndDate in the query above then I will get an empty result set. That’s because “today” really means “today at midnight”. So when I want data from “today” I need to set StartDate to “today” and set EndDate to “tomorrow”. It’s easy enough to do… but it’s easy enough to forget.

Next I defined the list of DateRanges that I want to make available. I settled on this sample:

  • Today
  • Yesterday
  • Tomorrow
  • This Week
  • Last Week
  • Next Week
  • This Month
  • Last Month
  • Next Month

I then defined the main logic for the date calculation. This logic should go in a separate Java class that gets compiled into a JAR file which can be shared by all the reports on the server. It should not be in the .jrxml file. I repeat: it should not be in the .jrxml file. Having said that, I put the logic in the .jrxml file. I considered this the simplest way to make the sample easily accessible by others. But if you plan to use this sample code, do yourself a favor and move it into a reusable JAR file.

The logic for the date calculations is simple. The hardest part was calculating the relative dates based on weeks. Java provides no simple way to determine the day of the week for a given Date. You can format a Date to return “Sun”, “Mon”, “Tue”, etc. But you cannot just get 0, 1, 2, etc. (I will be eternally grateful to the reader that tells me I’m wrong and shows how I can do it.) Therefore I needed to embed verbose logic like this:

DateFormatUtils.format(new Date(),"E").equals("Sun")
? 0
: DateFormatUtils.format(new Date(),"E").equals("Mon")
  ? 1
  : DateFormatUtils.format(new Date(),"E").equals("Tue")
    ? 2
    : DateFormatUtils.format(new Date(),"E").equals("Wed")
      ? 3
      : DateFormatUtils.format(new Date(),"E").equals("Thu")
        ? 4
        : DateFormatUtils.format(new Date(),"E").equals("Fri")
          ? 5
          : 6

Yikes. It’s easily understandable, but it’s painful.

Having done that, the report is trivially simple. The samples below show the results for “This Week” and “This Month”. Notice how the EndDate for ThisMonth is actually the first date of next month, but that returns only rows from this month.

The report calculates the start and end of "This Week"

The report calculates the start and end of "This Week"

The report calculates the start and end of "This Month"

The report calculates the start and end of "This Month"

When running the report in iReport you need to type in a value like “last week”. After you upload it to JasperServer and create the appropriate input control then you can more easily pick the relevant value from a list.

I have attached my .jrxml file along with the SQL needed to populate my sample table. Note that you can run the report without having the table. You would just need to remove the SQL query. If you do that then the report will still run. Obviously it won’t retrieve any data if it has no query, but it still calculates the StartDate and EndDate. You’ll see those displayed in the title band.

To make things easier I also attached a copy of the report exported from JasperServer. This exported copy includes both the .jrxml files as well as the input control that allows you to select from the predefined list of values.

There are plenty of ways to improve this sample. First, move the date calculation out of the report and into a separate JAR file. Second, there are all sorts of other date ranges you might want to include: fiscal periods, years, quarters, etc.

RelativeDatesJRXML_20100303 RelativeDatesExport_20100303 Browse attachments to all Right Outer Join articles

(The links above are .odt files because WordPress won’t let me attach .zip files. You need to download them and then manually change the filename to *.zip.)

Advertisements

5 Comments »

  1. […] Shared JasperReports Second Dates. […]

    Pingback by Frau Klein during the week in the interweb | Frau Klein — 7 March 2010 @ 19:04

  2. […] Shared JasperReports Second Dates. […]

    Pingback by Frau Klein im Internet during the week | kerstins kleiner blog — 8 March 2010 @ 12:09

  3. I knew that this code would break in different Locales. But it just dawned on me that this would be easy to prevent. I should have used this method instead:
    DateFormatUtils.format(new Date(),"E",java.util.Locale.US).equals("Sun")
    In code that formats a date for presentation I would not hardcode the Locale. But in this case where I’m just calculating the day of the week as an integer, hardcoding the Locale is appropriate.

    Comment by mdahlman — 1 December 2010 @ 13:36

  4. I know this is an old post, but I just wanted to share an idea:

    I solved the problem of getting the # of the week by creating an array of strings and getting their positions in that array. Might be a bit cleaner then the nested logic statements.

    e.g. create variable DaysOfWeek of type java.lang.String[]. Variable Expression is this: java.text.DateFormatSymbols.getInstance().getShortWeekdays()

    Then you’re able to access the position with something like this: $V{DaysOfWeek}[$F{day_of_week}+1]

    Comment by Steven Crawford — 10 September 2013 @ 08:46

    • That could be an interesting idea. But for the me the hard part was getting the integer day of the week. Getting the ShortWeekday is straightforward. How did you calculate $F{day_of_week}?

      Comment by mdahlman — 10 September 2013 @ 13:05


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

Blog at WordPress.com.

%d bloggers like this: