Relative Date Parameters in Jasper Server – Usage with Examples

Relative Date Parameters in Jasper Server – Usage with Examples

This blog gives a brief introduction of how Relative Date Parameters can be defined and used in Jasper Server Reports. Relative dates allow us to filter report data based on a date range relative to the current system date.

We often come across the situation where we have a Jasper report which has date filters and we want these parameters to be able to accept relative dates instead of just fixed date values. This is often a requirement when we want to schedule such reports periodically.
For example, We want a sales report to be sent to the sales manager every end of week and to a area manager every end of month. For this, we would like to schedule 2 occurrences of this report, one which extracts sales for past 7 days on every weekend and the other that extracts sale for the current month at the month end.

To do something like this, we can design a report with a parameter of date range type. These are the kind of values a relative date range parameter would accept – MONTH-1, QUARTER , YEAR-2, DAY-7, WEEK etc

This is the excerpt from jasper documentation that explains the template for relative dates.

A default value expression for a date range parameter would look like the example below:

new net.sf.jasperreports.types.date.DateRangeBuilder("QUARTER-1").toDateRange()

To use the “Date Range” parameters in the query, we need to use jasper’s $X{} notation. I have put up some examples which will help you implements relative dates with all different options in the $X{} notation.

So, we are going to use the below date as the current system date in all the examples below –

Today's date = '31-07-2016'
Current Quarter = Q3 (July 2016 - Sep 2016)
Current Month = July 2016

The name of the parameter we are using is p_order_date. In case of examples for ‘between’ clause, we are using 2 parameters p_start_date and p_end_date.

1. EQUAL Clause in $X{}

select ORDERNUMBER,PRODUCTCODE,QUANTITYORDERED,TOTALPRICE,ORDERDATE 
from orderfact of
where $X{EQUAL, ORDERDATE, p_order_date} 

p_order_date = QUARTER-1 (Apr 2016 – June 2016)
Selects all orders in the previous quarter i.e. all orders from ’01-04-2016′ to ’30-06-2016′

p_order_date = MONTH (July 2016)
Selects all orders in the current month i.e. all orders from ’01-07-2016′ to ’31-07-2016′

2. GREATER Clause in $X{}

select ORDERNUMBER,PRODUCTCODE,QUANTITYORDERED,TOTALPRICE,ORDERDATE 
from orderfact of
where $X{GREATER, ORDERDATE, p_order_date} 

p_order_date = QUARTER-2 (Jan 2016 – Mar 2016)
Selects all orders after the end of “QUARTER-2” i.e. all orders after ’31-03-2016′

p_order_date = MONTH-6 (Jan 2016)
Selects all orders after the end of “MONTH-6” i.e. all orders after ’31-01-2016′

3. LESS Clause in $X{}

select ORDERNUMBER,PRODUCTCODE,QUANTITYORDERED,TOTALPRICE,ORDERDATE 
from orderfact of
where $X{LESS, ORDERDATE, p_order_date} 

p_order_date = QUARTER-1 (Apr 2016 – June 2016)
Selects all orders before the start of “QUARTER-1” i.e. all orders before ’01-04-2016′

p_order_date = MONTH-6 (Jan 2016)
Selects all orders before the start of “MONTH-6” i.e. all orders before ’01-01-2016′

4. BETWEEN Clause in $X{}

4.1 Non-Inclusive between

select ORDERNUMBER,PRODUCTCODE,QUANTITYORDERED,TOTALPRICE,ORDERDATE 
from orderfact of
where $X{BETWEEN, ORDERDATE, p_start_date,p_end_date}  

p_start_date = QUARTER-3 (Oct 2016 – Dec 2015)
p_end_date = QUARTER-1 (Apr 2016 – June 2016)

The WHERE condition translates to ORDERDATE > QUARTER-3 and ORDERDATE < QUARTER-1
Selects all orders after the end of “QUARTER-3” and before the start of QUARTER-1
i.e. all orders after ’31-12-2015′ and before ’01-04-2016′

4.2 Inclusive between

select ORDERNUMBER,PRODUCTCODE,QUANTITYORDERED,TOTALPRICE,ORDERDATE 
from orderfact of
where $X{[BETWEEN], ORDERDATE, p_start_date,p_end_date} 

p_start_date = QUARTER-3 (Oct 2016 – Dec 2015)
p_end_date = QUARTER-1 (Apr 2016 – June 2016)

The WHERE condition translates to ORDERDATE >= QUARTER-3 and ORDERDATE <= QUARTER-1

Selects all orders from the start of “QUARTER-3” until the end of QUARTER-1
i.e. all orders from ’01-10-2015′ until ’30-06-2016′

4.3 Partially inclusive between

4.3.1

select ORDERNUMBER,PRODUCTCODE,QUANTITYORDERED,TOTALPRICE,ORDERDATE 
from orderfact of
where $X{BETWEEN], ORDERDATE, p_start_date,p_end_date}  

p_start_date = QUARTER-3 (Oct 2016 – Dec 2015)
p_end_date = QUARTER-1 (Apr 2016 – June 2016)

The WHERE condition translates to ORDERDATE > QUARTER-3 and ORDERDATE <= QUARTER-1
Selects all orders after the end of “QUARTER-3” until the end of QUARTER-1
i.e. all orders after ’31-12-2015′ and before/equal to ’30-06-2016′

4.3.2

select ORDERNUMBER,PRODUCTCODE,QUANTITYORDERED,TOTALPRICE,ORDERDATE 
from orderfact of
where $X{[BETWEEN, ORDERDATE, p_start_date,p_end_date}  

p_start_date = QUARTER-3 (Oct 2016 – Dec 2015)
p_end_date = QUARTER-1 (Apr 2016 – June 2016)

The WHERE condition translates to ORDERDATE >= QUARTER-3 and ORDERDATE < QUARTER-1

Selects all orders from the beginning of “QUARTER-3” before the beginning of QUARTER-1
i.e. all orders starting from ’01-10-2015′ and before ’01-04-2016′

These examples should help you implement most of the use cases that we come across.

References –
http://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v60/using-parameters-queries

http://community.jaspersoft.com/wiki/how-use-relative-dates-jrxml-static-reports

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download

Shraddha Tambe | Helical IT Solutions