How to limit a report to a date range

[mbritton]

Hi,

I have a report in which the user enters two dates $P{StartDate} and $P{EndDate}, which are (strangely enough), the start and end date of the report. I'm looking for a way to return a blank report (or even better, an error message), if EndDate is more than 6 months after StartDate (we're having memory issues and need to stop people from pulling large lists of data, so that our app doesn't crash).

Thanks in advance for the help!

[jroeterd]

calculate days between both prompt dates and use in filter to prevent uses for more then 180 days or something like that.

 

Jeroen  

[slow]

try this:

define a variable, date_sub, of type Long as this:

new Long((new SimpleDateFormat ("yyyy.MM.dd").parse ($P{EndDate}).getTime()-
          new SimpleDateFormat ("yyyy.MM.dd").parse ($P{StartDate}).getTime())
          /86400000L
    )

(use the date format you want but modify the string "yyyy.MM.dd"....)

if the user give two dates wich subtraction is more than 180 days, then you can choose two way...

the first is to represent only data between the start date and six months after date...
the second is to represent nothing

the six-month-after-start-date can be obtained declaring a variable as after_6_month_date:
Date object:    (new SimpleDateFormat ("yyyy.MM.dd").parse ($P{StartDate}).getTime()+86400000L*180)

if you want to represent nothing you can pass to the sql statement two dates that can't be subtracted... such as
2010.01.01 and 2009.31.12

the date of a day before the start date can be obtained with a variable date_before
Date object:    (new SimpleDateFormat ("yyyy.MM.dd").parse ($P{StartDate}).getTime()-86400000L)

so you can use a variable real_end_date setting it to:

$V{date_sub}.longValue<180? $P{EndDate} : $V{after_6_month_date}
if you want to show something...

$V{date_sub}.longValue<180? $P{EndDate} : $V{date_before}
if you want to show nothing...

and then pass to the sql statement the $P{StartDate} and the $V{real_end_date}

 

pay attention to pass only string-dates to the sql statement in the format your db exptect them...

is use it... :)

_________________________________________

if it works... give me KARMA points please!    : ) 
_________________________________________

listening:  Death in Vegas - Dirge

 

 

 

 

 

 

 

[mbritton]

Slow, thanks for the help. However, when I change the $P{EndDate} parameter in my sql query to $V{EndDate} (which is my name for your $V{real_end_date}, I get at sql error at '$'. Any idea what could cause this?

[mbritton]

Never mind, I fixed this by changing all the variables to parameters, using the same expressions you suggested. Thanks so much!

[kiwiyo]

Hi mbritton,

When i tried to limit the date range, i get this error msg:

1. The method parse(String, ParsePosition) in the type SimpleDateFormat is not applicable for the arguments (Date Need your advise on this :)

Thanks :)