NOTE: This functionality was replaced by Relative Date functionality. Find more here and here
Dynamic Dates in Reports
This article covers a commonly asked question of: How do I create a report with dynamic dates? This is important for reports that are scheduled to run on a regular basis, but for which you want the time date range of the data to change based on the date the report is run.
Some example solutions follow:
Creating a report that shows activity in the last 10 days...
You may want to simply create a report that pulls activities for the past 10 days, regardless of the date that it is run.
There is 2 ways to achieve this.
There is the possibility in iReport to create a parameter -let's call it today- which returns today's Date with the default expression
new Date()
(Disable prompt and give it class java.util.Date) Then from that parameter -today- you can generate any date passing parameters to the method Date(). You can for example create a parameter returning the date of 10 days ago with this default expression
new Date($P{today}.year, $P{today}.month, $P{today}.date-10)(same, disable prompt and give it class java.util.Date) In the same way you could generate parameter:
- year to date:
new Date($P{today}.year, 0, 1)- month to date:
new Date($P{today}.year, $P{today}.month, 1)- beginning of last month:
new Date($P{today}.year, $P{today}.month - 1, 1)- end of last month:
new Date($P{today}.year, $P{today}.month, 0)Those parameters should be used in the SQL query to select the right data which you want to be displayed in your report.
Or this can also be done with Scriptlets by using date parameters with a default value expression initialized with a calculated date - the current date or the current date minus 10 days. By doing this, the value of these parameters will be determined at run time based on the current date and can be used to filter your query as required.
You could also add a parameter and input control to allow you to specify the number of days back you wish to retrieve data for at report run time.
Note: Subtracting 10 days from the current date, using a single expression may not be easy. You may need to create a helper class to deploy with your report or in your environment.
The Apache Commons Lang library is one option for calculating dates in a single expression. For example, you may have one parameter "TODAY" of type java.util.Date with a default expression of:
new java.util.Date()
and another parameter "TENDAYSAGO" (also of type java.util.Date) with a default expression of:
org.apache.commons.lang.time.DateUtils.addDays($P{TODAY},-10)This leverages the DateUtils.addDays method from the Apache Commons Lang package.
The addDays method was introduced in version 2.2 of the Apache Commons Lang library. You may need to download and add commons-lang-2.2.jar (or newer) to the Jaspersoft classpath.
Creating Reports with Input Controls with Dynamic Dates
Perhaps you would like your users to be able to select from predetermined calculated dates, for example:
- Today
- One Month Ago
- Beginning of Current Month
- End of Current Month
- Beginning of Current Year
- Beginning of Current Week
- etc.
Achieving this is also possible, yet takes a little more effort.
- To start, create a parameter of String type with a matching single-select input control with the predefined String values you would like to have available to the user. For this example, we will call this "DATELITERAL"
Next, create a class with a method that converts the string values to the equivalent Date. For example, something like:
public static Date getDate(String dateLiteral) { switch(dateLiteral) { case "Today": return new Date(); break; case "One Month Ago": Date today = new Date(); Calendar cal = Calendar.getInstance(); cal.setTime(today); cal.add(Calendar.Month, -1); return cal.getTime(); break; .... default: //convert MM-DD-YYYY into Date and return } }Now, create a parameter of Date type with a default expression like:
myClass.getDate($P{DATELITERAL})
Recommended Comments