start_date and end_date parameters – giving default values – iReport

Posted on by By Nikhilesh, in Business Intelligence, Data Visualization, Jaspersoft, Open Source Business Intelligence | 1

Hi Guys,

This post let’s you know how to use date parameters in SQL query using between operator and using  multi select parameter.

Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.

Get your 30 Days Trail Version

Example Query:

column1, colum2
$X{[BETWEEN], date_field_name_from_table, start_date, end_date}

In this way you can use between operator for start_date and end_date parameters with multi select.
Note that multiselect generally selects the input controls at a time.

Default values for start_date and end_date:
Problem statement:
Find the problem statement in this post.

The problem statement which I faced is :
If today is 2013-10-07 then startdate is : 2013-09-16 enddate is : 2013-09-31
if today is : 2013-10-23 then startdate is : 2013-10-01 enddate is: 2013-10-15
The same logic should applicable in january month as well(i.e, for instance if today is : 2013-01-13 then startdate: 2012-12-16 enddate:2012-12-31)

Apart from the solution that given in the community, also find below solution

start_date Default Value Expression:

Syntax followed : ternary expression:  condition?True:False

$P{cal}.get(java.util.Calendar.DAY_OF_MONTH)>15 ?
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 1) :
($P{cal}.add(java.util.Calendar.MONTH, -1) ||
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 16))
? null : $P{cal}.getTime()

end_date Default Value Expression:

$P{cal}.get(java.util.Calendar.DAY_OF_MONTH) == 1 ?
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 15):
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(java.util.Calendar.DAY_OF_MONTH))
? null : $P{cal}.getTime()

In the above two start_date and end_date parameters you can find $P{cal} – which is a parameter created to get the calender dates (in simple words java calender instance)

You must create this($P{cal}) parameter and have to give default value expression before you use this parameter in start_date and end_date parameters.

Default Value Expression for $P{cal} parameters is : java.util.Calendar.getInstance()

for all the input controls Parameters classs is : java.util.Calendar

Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.

Grab The 30 Days Free Trail

(Learning never exhausts the mind)


Best Open Source Business Intelligence Software Helical Insight is Here


A Business Intelligence Framework

0 0 votes
Article Rating
Notify of
1 Comment
Newest Most Voted
Inline Feedbacks
View all comments

See also Andreas’ comment demonstrating GroovyShell opens up a bunch of possibilities in JasperServer without recompiling.