Setting default date parameters for weekly reports

[tugrul083]

Hi,

I have been stuck with this for a long time now and would really appreciate any help to get me going.

I want to add default values for my startdate and enddate parameters such that they are set to "Last Sunday" and "Sunday before" by default. This will enable me to schedule weekly reports while still giving the users to change the report dates on their convenience.

I am not proficient with Java and the fact that Jasper Studio allows only 1 expression in a paramater doesn't help.

I have tried using the following expressions. They did work for a couple of reports but didn't give the correct dates for others. Also with reports that use stored procedures, it simply won't work. There's something wrong and it's unreliable.

Startdate: new SimpleDateFormat("yyyy-MM-dd").format(NOW()-(WEEKDAY(NOW())+7))

Enddate: new SimpleDateFormat("yyyy-MM-dd").format(NOW()-(WEEKDAY(NOW())))

The sql equivalent of what I'm trying to write would be the following.

Startdate: DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW())+6 DAY)

Enddate: DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW())-1 DAY)

Any help would be greatly appreciated. Many thanks and regards.

[H Mendoza]

Hi there -

Maybe try relative dates; check out the following links: 

http://community.jaspersoft.com/wiki/how-use-relative-dates-jrxml-static-reports

http://community.jaspersoft.com/questions/803328/relative-dates-daterange-class

http://community.jaspersoft.com/questions/816920/relative-dates-parameter-month-year

http://community.jaspersoft.com/categories/relative-dates

 

Thanks.

[tugrul083]

Thank you so much Hugo! Using date range parameters I've solved the issue with most of my reports! :-)

I'd like to ask another question if I may; Date range parameters worked fine for both SQL queries run on the report AND on a stored procedure which has only date inputs. ie. CALL Stored_Procedure $X(startdate, enddate). This works fine.

However the "$X()" syntax doesn't seem to work with stored procedures that have more than 2 paramater inputs such as CALL Stored_Procedure $X(startdate, enddate, ID1, ID2. ID3, ID4, ID5, ID6) 

Any ideas? 

Many thanks and regards,

Tugrul