Input controls issue scheduling a daily email job

[heragamennoni]

 Report Scheduling daily Job  (on jasper server v3.7.0 community edition). 

I was able to set up, and then successfully received a daily report by email by scheduling it. This particular report has as input controls: a "from" and "to" date time value. For example: - from date: "2010-02-28", - to date: "2010-03-07" (a week period).

But here is the interesting issue: these two input control values "always" have the same period of time, and the purpose is to have, a day increment, on the input of the period of time, everyday I receive the email with the report attached.

So, following the above example, the next input period of time values should be: "2010-03-01 to 2010-03-08", next one "2010-03-02 to 2010-03-09",..., etc. but it didn't work. The period of time always has the same value, and it doesn't change, as it was previously desired :(

In iReport tool, I was able to set up the input parameter values, by filling them with a java coding (like entering new Date(), and then the other date before a week) when previously entering no data on the schedule job. The purpose is to have a report received by email with the input date parameters varying according to the current date. This approach only worked when I directly click on the report. But after I scheduled this job, it didn't, because (it seems the input parameters weren't populated for some reason) I successfully received the email, but with an empty attachment report generated. 

Could someone help  me in order to guide me to solve this issue? :)

Please let me know any suggestions you may have. Thanks in advance! 

                                                                                                                        Hernán.

[Gaby38]

 Hello,

For me, it's not a good idea to have your dates passed as input controls, because they will always have the same static value.

I have also some reports with the same kind of problematic : what I do is to have a main report just to have a query "calculating" the good dates and to have a sub-report inside which is the report doing the work and to which I pass as parameters the fields got from the main report query.

For instance , in your case, supposing that you want a time period starting from to-day to to-day+8, you could have a query like :

"Select to_char(Sysdate,'YYYY-MM-DD') as StartDate, to_char(Sysdate+8,'YYYY-MM-DD') as EndDate from dual"

and you pass the fields StartDate and EndDate as parameters to your sub-report.

PS: My query works against an Oracle Database

[mdahlman]

Hernán,

This is a really common request. I know it's planned to add this to JasperServer someday... but it's not there today.

The simplest solution is to code the dates into the SQL query. This is commonly a very good solution. But there are lots of times when this is not sufficient.

I wrote an article last week about how you can create reports that use relative dates like you are looking for. It explains how you can define parameters like "This week" or "Last month" and get them to work as expected. The article is called JasperReports Second Dates – Meet the Relatives.

 

Gaby38,

Your idea is an example of putting the date calculation into the SQL query. It's great for any purposes. As a detail I recommend against using TO_CHAR when you are populating a date field. Holding dates as Strings have lots of drawbacks.

Regards,
Matt

[heragamennoni]

Thks you Matt and Gaby for your reply and your explanation for giving me a solution to this! :)

[lpeel]

Hi,

I have a report run unit that enables the user to select an optional begin datetime and an end datetime via input controls.    This enables them to do ad hoc queries for any date range they wish.   A new requirement is to ALSO schedule this report and provide a report of yesterday's data for example,  ('2011-02-10 00:00:00' thru '2011-02-10 23:59:59') .

Given the above thread, any ideas on how I could do this WITHOUT duplicating the original report and making 2 versions of it?

Thanks 

[mdahlman]

Well, you clearly need parameters like these:

startdate_entered
enddate_entered

You likely want some variation on these:

startdate_calculated
enddate_calculated

There are lots of possible variations. But if you use something like that, then you could also introduce a parameters like this:

yesterday (checkbox)

If yesterday is checked, then you can ignore any entered values for the _entered parameters and easily calculate the _calculated parameters. Select this for the scheduled reports.

You could leave yesterday exposed for folks running the report on demand, and it probably wouldn't bother them. It might be more elegant to customize the parameter display window to hide yesterday since it isn't really needed for those users.

Regards,
Matt