HELP! Scheduling Reports for Yesterday

dnewby,

This is a great feature request. We are well aware of it at JasperSoft, but it's not in JasperServer 3.0.

There are a few ways to implement it. For example Jasper4Salesforce is based on JasperServer (of course) and it includes exactly the feature you are describing. In that case it is taking advantage of Salesforce's date utilities like calendar quarter, fiscal quarter, last week, next week, etc.

In your case you could address this directly in the SQL. For example in MySQL you can use code like this to get data from the last week:
WHERE t1.start_date < date_add( now(), INTERVAL -1 WEEK )

You can use variations on this idea to get 'last Monday' or 'the first of last month' etc.

In the simplest case the report doesn't need to be parameterized: you just hard code the use of last week. A more flexible version would optionally let you enter a date. The query could use the date if it's entered, but it could fall back on a default value of last week if no date was entered. Or you could have a simple drop down param1 with "Last week", "Last month", etc. Then based on the value of param1 you can have param2 take the appropriate SQL string.

Param2 would be something like this:

Code:

<br /> param1.equals("Week"«»)<br /> ? "date_add( now(), INTERVAL -1 WEEK )"<br /> : param1.equals("Month"«»)<br /> ? "date_add( now(), INTERVAL -1 MONTH )"<br /> : "date_add( now(), INTERVAL -1 YEAR )"</td></tr></tbody></table><br /> <br /> Remember that your SQL needs the P!{} syntax in this case:<br /> WHERE t1.start_date < P!{param2}<br /> <br /> I'm generally biased towards doing these calculations in SQL... but you could do the same sort of thing in Java in the .jrxml if you prefer.<br /> <br /> That's the general idea of how I would do it today. We expect to see support for this idea built-in to JasperServer automatically as part of the sceduling functionality sometime in the future.<br /> <br /> Regards,<br /> Matt