dynamic date parameters for scheduling a report

[koushal]

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 monthly report that I would like to run that looks at the first day of the month. If I set the scheduling up today (being the 1st June) I would have to enter the date parameter of the previous day (being the 15th). So the report will run fine today.
However, next month (the July) the report will run again but not for the 1st July, but for the 1st June. This is because I had to enter the date parameter of the 1st June. Every time the report runs it will always use the 1st June

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? In the below post it's updated that JasperServer3.0 does not have that feature. Can you please let me know if any of the updated Jasperserver versions have this feature.

http://jasperforge.org/plugins/espforum/view.php?group_id=112&forumid=102&topicid=40834

Thank You.

 

Post Edited by katherine2010 at 06/18/2010 19:31

[mileidysg]

I have the same situation over here. My solution was to use the time directly in the sql query according to the time of the server and is fine.

[Gaby38]

 What I do in this case is to put the report as a sub-report in another "main" report, and in this main report, the query "calculates" in sql the right dates which are passed as parameters for the sub-report.

For instance, you can schedule this main report to be run the 1st of each month and have for the query (Oracle database):

select sysdate as DayDate from dual

You will get in the field DayDate "01/06/2010" the 1st of June, "01/07/2010"  , the 1st of July ((French dates) and so on..

You just have to map your $P{DayDate} parameter in sub-report to the field $F{DayDate} of the main report

[brooneyx1]

The way I do this is to create a parameter that contains a string representation of the time slice I am interested in. So I created a String parameter called DatePick and I allow the following  set of values: "ThisWeek", "LastWeek", "Yesterday", ThisYear", "LastYear", "ThisMonth", "LastMonth" and  "Input".

The Input opton is to allow for using an explicit date. I then created a sql function that takes these strings and returns an actaul date.

So my query  would look like: (WIth DatePick = 'ThisWeek')

SELECT
 <Deleted for brevity>
WHERE
    theDate Between calcStartDate($P{DatePick}) and calcEndDate($P{DatePick});

This works great for  scheduling reports since it calculate relative to the current date !

I attached my calcStartDate sql function for MySql as an example.This could be done in java as well, but I prefer using sql so I can run the same queries in mysql.

[pwpwpw]

What if I wanted to have a prompt asking the user for the year or date when the report is ran manually, but when scheduling it should just fetch the current year? Is that possible?

 

I was thinking in the SQL, i would do this:

Check if the date parameter is null. If null then get the current year, else use the date parameter passed in.

 

 

[sjongenelen]

You can make a condition in the report parameter, afaik:

 

(if date_param = null ? get CURDATE() : date_param)

 

so, if its not filled, adjust the CURDATE() part to get the current date. (through SQL or perhaps just Java)

[mileidysg]

Great idea!!!

[paris11]

Thanks for this method

It works very good on Jasper Server for the reports built on Ireport, but  I am afraid  it is impossible to apply this method for the ad hoc reports on jasper server because we cannot change the sql code.

 

 

[julesstp]

We have the same issue over here. It is something fairly standard in my mind to have a beginning date and end date param for users to select but also have a default value so that it can run upon scheduling. I was hoping we could use new.java.util.date() - 7  and new.java.util.date() as the default value and have that flow but I don't think it is possible.

By reading this forum I guess the best fit for us is to add an aditional param with "Last Week" "Last Month" etc. as options in param and have that in the sql code - don't like it but I suppose it will do... Unless there are any more ideas - why can't a java function like the one above be the default value and dates be left blank when scheduling report and it works?

thanks

[brooneyx1]

You could create a specific report for each time slice and set the defaults for the parameter in the jrxml, but do not add an input control to the report. I set the language to Grovy and set the parameter type to java,util.Date and use a default of Date() -1 for StartDAte and Date() - 7 forEndDate. I also unset "Use as prompt"  - This will work  fine but you will need the same report duplicated for each timeslice. I still prefer doing from a list "ThisWeek', etc so that I have a single report to deal with.

I did a quick write of of the new way I do Dynamic dates. It is at http://reptics.com - I used to do Sql function based implementation, but doing this with Java is cleaner and easier to make changes.

 

Post Edited by brooneyx1 at 05/22/2011 08:53