HELP! Scheduling Reports for Yesterday

[dnewby]

Im hoping someone here might be able to help with a conundrum I have with scheduling reports.

 

All the reports we use, use the java.util.date parameter for date entry. This works fine for running the reports as normal and being able to choose a specific date or date range.

However when scheduling the reports, it will still use the same date parameter and as part of the setting up of scheduling you have to fill in the same report parameters.

For example there is a daily report that I would like to run that looks at the previous day. If I set the scheduling up today (being the 16th) I would have to enter the date parameter of the previous day (being the 15th). So the report will run fine today.

However, tomorrow (the 17th) the report will again but not for the 16th, but for the 15th. This is because I had to enter the date parameter of the 15th. Every time the report runs it will always use the 15th!

 

Can anyone help or suggest a way to be able to run a recurring report that you can choose a specific time frame by name i.e. yesterday, last week or similar?

[mdahlman]

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:

param1.equals("Week"«»)
? "date_add( now(), INTERVAL -1 WEEK )"
: param1.equals("Month"«»)
  ? "date_add( now(), INTERVAL -1 MONTH )"
  : "date_add( now(), INTERVAL -1 YEAR )"

 

Remember that your SQL needs the P!{} syntax in this case:

WHERE t1.start_date < P!{param2}

 

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.

 

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.

 

Regards,

Matt

[DocJones]

Heyas,

 

i am fiddling around with JS 3.0 and date parameters (yesterday), too.

Currently i am trying to evaluate, why

Code:

$P{IDS_DATE} == null ? new Date(System.currentTimeMillis()-86400000) : $P{IDS_DATE}

does not work. Any hint would be appreciated. I really need to get done...

 

Even

Code:

[code] ... WHERE DATE(STYEAR||'-'||STMONTH||'-'||STDAY)=COALESCE($P{IDS_DATE},CURRENT DATE-1 Day)

ceases to work (SQL dialect is DB2)

 

thanks & regards

Marc

 

Post edited by: DocJones, at: 2008/07/07 12:38

Post edited by: DocJones, at: 2008/07/07 12:45

[swood]

In what way was this not working?

 

Sherman

Jaspersoft

[DocJones]

Heyas,

 

Code:

$P{IDS_DATE} == null ? new Date(System.currentTimeMillis()-86400000) : $P{IDS_DATE}

 

does not work. The parameter IDS_DATE is still null afterwards.

 

Seems to me as if the new Date(...) Constructor is not handled properly.

 

On a sidenote: is there a possibility to "debug" report generation? I would love to see the create SQL command thats being executed.

 

regards

Marc

[lucianc]

DocJones wrote:

On a sidenote: is there a possibility to "debug" report generation? I would love to see the create SQL command thats being executed.

 

Add the following line to $TOMCAT/webapps/jasperserver/WEB-INF/log4j.properties:

Code:

log4j.logger.net.sf.jasperreports.engine.query.JRJdbcQueryExecuter=DEBUG

 

Regards,

Lucian

[DocJones]

Thanks,

 

the logging works now. I can clearly see, that the Parameter #1 for the query ($P{IDS_DATE}) is null, which can't be, imho.

 

Any help on that?

 

regards

Marc

[lucianc]

DocJones wrote:

Code: $P{IDS_DATE} == null ? new Date(System.currentTimeMillis()-86400000) : $P{IDS_DATE}   does not work.   Where is this expression used? And how should it work?

[DocJones]

Hey,

 

it is used as "Default Value expression" for a parameter (IDS_DATE). The parameter is used to handle a input control (date selector).

 

Hope this helps :)

 

M

[lucianc]

If you have an input control for IDS_DATE and you don't set any value for it, null will be used as parameter value and the default value expression will not be used (the expression is only invoked when a parameter does not receive any value).

 

What you should do is to introduce a new parameter which will be used for the query:

Code:

<parameter name="IDS_DATE" .../>
<parameter name="QUERY_IDS_DATE" class="..." isForPrompting="false">
  <defaultValueExpression>
    $P{IDS_DATE} == null ? new Date(System.currentTimeMillis()-86400000) : $P{IDS_DATE}
  </defaultValueExpression>
</parameter>
<queryString>
  select .. from .. where date = $P{QUERY_IDS_DATE}
</queryString>

 

HTH,

Lucian