You can create a report that filters information based on a date range relative to the current system date using a parameter of type DateRange. A date range parameter can take either a date or a text expression that specifies a date range relative to the current system date.
| A relative date expression is always calculated in the time zone of the logged-in user. However, the start day of the week can be configured independent of locale. |
Relative Date Keywords
The text expression for the relative date must be in the format <Keyword>+/-<N> where:
| • | <Keyword> – Specifies the time span you want to use. Options include: DAY, WEEK, MONTH, QUARTER, SEMI, and YEAR. |
| • | <+/-> – Specifies whether the time span occurs before (-) or after (+) the chosen date. |
| • | <N> – Specifies the number of the above-mentioned time spans you want to include in the filter. |
For example, if you want to look at Sales for the prior month, your expression would be MONTH - 1.
| Relative dates don't currently support keywords like "Week-To-Date" (from the start of the current week to the end of the current day). However, you can set a relative date period in a query in JRXML using BETWEEN, which has the syntax: $X{BETWEEN, column, startParam, endParam} For example, to create a week-to-date query, set startParam to WEEK and endParam to DAY. You can do this for other time ranges, such as Year-To-Day, Year-To-Week, and so forth. |
Creating a Date Range Parameter
The class attribute of a JasperReports date range parameter must have one of the following values:
| • | net.sf.jasperreports.types.date.DateRange (Date only) – Accepts text strings with relative date keywords as described above and date strings in YYYY-MM-DD format. For example: |
<parameter name="myParameter" class="net.sf.jasperreports.types.date.DateRange">
| • | net.sf.jasperreports.types.date.TimestampRange (Date and Time) – Accepts text strings with relative date keywords as described above and date strings in YYYY-MM-DD HH:mm:ss format. For example: <parameter name="myParam" class="net.sf.jasperreports.types.date.TimestampRange"> |
Using Date Ranges in Queries
You must use $X{} functions with date ranges, because $P{} does not support the date-range types (DateRange and TimestampRange).
To use date ranges, create a parameter with type date range and use it as the third argument in the $X{} function. To set the default value expression of a date range parameter, use the DateRangeBuilder() class to cast the expression to the correct type:
| • | new net.sf.jasperreports.types.date.DateRangeBuilder("DAY-1").toDateRange() – casts a keyword text string to a DateRange. |
| • | new net.sf.jasperreports.types.date.DateRangeBuilder("WEEK").set(Timestamp.class).toDateRange() – casts a keyword text string to a TimestampRange. |
| • | new net.sf.jasperreports.types.date.DateRangeBuilder("2012-08-01").toDateRange()– casts a date in YYYY-MM-DD format to a DateRange. |
| • | new net.sf.jasperreports.types.date.DateRangeBuilder("2012-08-01 12:34:56").toDateRange()– casts a date in YYYY-MM-DD HH:mm:ss format to a TimestampRange. |
The following JRXML example shows data from the previous day:
This JRXML example shows results prior to the end of last month:
The following table shows two additional examples of relative dates.
Problem | Solution |
| Set up a relative date parameter called StartDate that takes the value: QUARTER. QUARTER evaluates to the first day (the first instant, really) of this quarter. | |
Find all purchases made previous to this quarter | SQL: select * from orders where $X{LESS, order_date, StartDate} |
Find all purchases made in this quarter | select * from orders where $X{EQUAL, order_date, StartDate} |
Using Relative Dates in Input Controls
When you create an input control for a DateRange or TimestampRange parameter, the user can either type a relative date expression or enter a specific date (either by typing or by using the calendar widget).
Use BETWEEN to set up input controls that allow the user to specify a range (other than a day) using either a relative date expression or actual dates. To do this:
| • | Define two date range parameters, for example, StartDate and EndDate. |
| • | Optionally, set default values for one or both parameters using defaultValueExpression. |
| • | Use a $X{} expression with a BETWEEN function in your query. |
| • | Create a date type input control for each parameter, for example, StartDate and EndDate. |
The following JRXML example uses the BETWEEN keyword in the $X() function to find all data from the previous 20 years:
You can use the getStart() and getEnd() methods to get the precise beginning and end of a relative date. Both of these methods return a date instead of a date range. The following example shows how to get the precise start date as a default value expression.
Publishing Reports with Relative Dates to JasperReports Server
Jaspersoft Studio automatically enables support for date range expressions on connections to JasperReports Server. To verify that date range expressions are enabled:
| 1. | Right-click on the server connection in the Repository and select Edit. |
| 2. | In the Server profile wizard, display the Advanced settings and select Supports DateRange Expressions. |
When Supports DateRange Expressions is enabled, input controls for date range parameters work correctly when published to JasperReports Server.
Recommended Comments
There are no comments to display.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now