Jaspersoft BI Tools

iReport,Jaspersoft Design Studio,Jaspersoft Server, Schema Workbench,Analysis,Dashboards, Self Service BI, Domains,Topics,Ad-Hoc Reports, Jaspersoft ETL, Embedding BI,HTML-5 charts, Fusion Charts,JFree Charts,Drill down reports , Drill down Dashboards,Visualize.js, Performance Tuning, D3 Charts, Jaspersoft Customization

Tuesday, October 08, 2013

start_date/from_date and end_date/to_date parameters - giving default values - iReport

Imp Reference for : daily, weekly, monthly, yearly
http://community.jaspersoft.com/wiki/dynamic-dates-reports

This post let's you know how to use date parameters in SQL query with between operator and using with 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)