Relative Date Parameters in JasperServer report

[jbl429]

Hello,

I am trying to set up several reports in JasperServer which have date parameters.  Typically the report is based off of a weekly period. Usually I will use parameters of {DATE_START} and {DATE_END} which works fine-- when running the report you can choose whatever start and end date you wish and the report runs beautifully.

The problem, however, is scheduled reports.  I want to send a report at the end of each week with {DATE_START} = 7 days ago and {DATE_END} = today.  But when scheduling reports, I have to choose a specific start and end date.  Furthermore, I can't even ignore these parameters have the default values used.

This seems a little insane.  If I want a report where users can choose the parameters I have to create one report.  If I want a report to mail daily, I have to create another report with no parameter controls, and set the default values in iReport.  If I want weekly report, I have to create a 3rd report.  And a month end report is a 4th one.  This is a serious pain.  Is there something I'm missing?

Please someone tell me there's a better way to handle this!

Thanks in advance,

Jeff

[brooneyx1]

The way I have done this is to create integer properties for offsets from current date  for instance: StartOffsetInDays and EndOffsetInDays.

I then add something like below to my query:

Where page_views.request_date  between Date_sub(CurDate(), Interval $P{StartOffsetInDays} Day)
    and Date_sub(CurDate(), Interval $P{EndOffsetInDays} Day)

 

[jbl429]

brooneyx1,

Thanks for your solution, but it's something that won't work well for me. If someone needs to run a report for a date range from a few months ago, the user will have to figure out how many days back they are running the report for. This won't fly with our users. We are trying to set up a reporting system and empower our users to run reports on demand.

I like the way it's done in iReport with the default values, and was hoping there would be a similar solution in JasperServer.

[brooneyx1]

I agree it isn't optimal, but works till we find a better solution - We schedule the  reports for our customers so they never see the pain. For  predetermined time periods, like last month, last week, etc, we do the following to offset from the current date, again not optimal, but may give you some ideas:

Last Month:

date_format(d.full_date, '%Y-%m') = date_format(DATE_SUB(CURDATE(),INTERVAL 1 month), '%Y-%m')

This Month:

d.date_key between getDateKey(date_format(CURDATE(), '%Y-%m-01'))
     and getDateKey(date_format(curdate(), '%Y-%m-%d'))

The missing part (we do not have)  is to have a drop down input control that allows users to choose: "This Week", "Last Week", "This Month", "Last Month", "This Quarter", etc. Maybe you could do a scriplet that takes in the desired time period ("This Month") and returns the correct start and end dates for the where clause.

 

[mpenningroth]

The professional version of jasperserver you can store different parameter values for the same report end give the report a new name.

so the weekly report, monthly report, quarterly report could all use the same jrxml but submit different saved parameter values.

Another approach would be to write your own parameter defualt value routine.  i believe there is a sample of this using user profile attribues as a source for the default parameters.