start_date and end_date parameters – giving default values – iReport

Hi Guys,

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

Example Query:

SELECT
column1, colum2
FROM
XYZ_table
WHERE
$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.
http://community.jaspersoft.com/questions/819583/default-values-date-parameters

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()

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

Sadakar
(Learning never exhausts the mind)

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download