Relative Dates With selectable DateRange

[mjmapi]

Hi Guys,

I have been struglling with this without success, i need to setup relative dates in my report, report should allow user to select relative dates e.g Yesterday or if not using reletive dates user must be able to select their daterange e.g June 1 (As start), June 30th as end.

Using net.sf.jasperreports.types.date.DateRange i have the relative date part working e.g DAY-1, WEEK e.t.c, how do i get the Daterange working with this?, currently the  calender only allows me to select a single date/day, no option to select multiple date if user does not wish to use relative dates, 

Any hints on how i can get this working

Thanks in advance

Meli

[mjmapi]

This is how i was able to resolve this issue,

As stated on the above post  when I define a  relative field (DAY, WEEK etc) using net.sf.jasperreports.types.date.DateRange, users cannot later select a date range but are force to select a single day, if client does not want to make use of relative date e.g. WEEK instead they want stats for a specific timeline e.g 1^st to 3^rd of Month, net.sf.jasperreports.types.date.DateRange is not helpful in this case, unless someone knows how to get this working, to get past this

 

Using SQL script formatting (MySQL)

 

• Create 3x parameters, startdate, enddate of java.sql.Timestamp type (is for prompt)
• Timeframe of java.lang.String type (At runtime this will take your relative fields e.g Today, Yesterday) (is for prompt)
• Include the clause below to let sql filter the data based on the users parameters

 

 

where cd1._timestamp >=

(

Case $P{timeframe}

  When 'Today' then CONCAT(CURDATE()," ", "00:00:00")

  When 'Yesterday' then CONCAT(date(NOW() - INTERVAL 1 DAY)," ", "00:00:00")

  ELSE  $P{start_time}

  End

)

 

And  cd1._timestamp <=

(

Case $P{timeframe}

  When 'Today' then  CONCAT(CURDATE()," ", "23:59:59") -- tested

  When 'Yesterday' then CONCAT(date(NOW() - INTERVAL 1 DAY)," ", "23:59:59")  --  tested

  ELSE  $P{end_time}

  End

)

 

 

Using JasperStudio

Create 5x parameters in order

• • timeframe of java.lang.String type (At runtime this will take your relative fields e.g Today, Yesterday) (is for prompt)
  • Start_time, end_time of type java.sql.Timestamp (is for prompt)
  • Startdt, enddt of type java.lang.String (not for prompt) 

 

Startdt (Default Value Expression):

$P{timeframe} == "Today"?      new SimpleDateFormat("yyyy/MM/dd"+" "+"00:00:00").format(new Date().getTime() - 0* 24 * 60 * 60 * 1000):

$P{timeframe} == "Yesterday"?  new SimpleDateFormat("yyyy/MM/dd"+" "+"00:00:00").format(new Date().getTime() - 1* 24 * 60 * 60 * 1000):

$P{timeframe} == "Last Week"?  new SimpleDateFormat("yyyy/MM/dd"+" "+"00:00:00").format(NOW()-(WEEKDAY(NOW())+7)):

$P{timeframe} == "This Month"? new SimpleDateFormat("yyyy/MM/dd"+" "+"00:00:00").format(java.util.Date.parse(MONTH(NOW( )) + "/1/" + YEAR(NOW()))):

$P{timeframe} == "Last Month"? new SimpleDateFormat("yyyy/MM/dd"+" "+"00:00:00").format(java.util.Date.parse((MONTH(NOW( )) - 1) + "/1/" + YEAR(NOW()))):$P{start_time}

 

 

enddt(Default Value Expression):

$P{timeframe} == "Today"?      new SimpleDateFormat("yyyy/MM/dd"+" "+"23:59:59").format(new Date().getTime() - 0* 24 * 60 * 60 * 1000):

$P{timeframe} == "Yesterday"?  new SimpleDateFormat("yyyy/MM/dd"+" "+"23:59:59").format(new Date().getTime() - 1* 24 * 60 * 60 * 1000):

$P{timeframe} == "Last Week"?  new SimpleDateFormat("yyyy/MM/dd"+" "+"00:00:00").format(NOW()-(WEEKDAY(NOW()))):  ///Lastday of last week denoted as 00:00:00, last week end day is this week start

$P{timeframe} == "This Month"? new SimpleDateFormat("yyyy/MM/dd"+" "+"23:59:59").format(java.util.Date.parse(MONTH(NOW( )) + "/" + DAYSINMONTH(NOW())+ "/" + YEAR(NOW()))):

$P{timeframe} == "Last Month"? new SimpleDateFormat("yyyy/MM/dd"+" "+"23:59:59").format(java.util.Date.parse((MONTH(NOW( )) - 1) + "/" + DAYSINMONTH(new SimpleDateFormat("yyyy/MM/dd"+" "+"00:00:00").format(java.util.Date.parse("01/" + (MONTH(NOW( )) - 1) + "/" + YEAR(NOW()))))+ "/" + YEAR(NOW()))):$P{end_time}

 

 

MySQL date filtering/selection format in MySQL

where cd1._timestamp >= DATE_FORMAT($P{startdt}, '%Y/%m/%d %H:%i:%s')

and  cd1._timestamp <= DATE_FORMAT($P{enddt}, '%Y/%m/%d %H:%i:%s')