Jump to content

Scheduled Report - Back-Dated Timestamp Pattern


cbentley

Recommended Posts

Good morning, everyone --

I've been using JasperServer and JasperReports for awhile and love it. I run a number of intricate weekly and monthly scheduled reports, with the reports output as PDF and e-mailed to a script, which then posts them to my company intranet for review. My one gripe is with the output filename timestamp pattern...

The weekly reports are generated each Monday and are based on the previous week's data -- the week ends on Friday. As such, logically speaking, the filename timestamp should be dated for that Friday. Unfortunately I have not found a way to back-date the filename timestamp function, which is currently set to yyyy-MM-dd. So, the filename always has Monday's date, since that's when the report is generated.

Is there any way, from within JasperServer, to change the timestamp pattern to do this? Or possibly to use some other source for the date? Could I pass that as a variable from within the report, perhaps? Or can the output script be modified somehow to calculate last Friday's date and use that date instead?

Any suggestions or tips would be appreciated. As it is now, I'm tired of manually renaming those files every week! :-) If this isn't possible from within JasperServer, I'm considering writing a bash script that does this for me, but that comes with it's own issues, so I thought I'd start here, first.

Thanks in advance!

Chris

Link to comment
Share on other sites

  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

I had the same scenario and ended up implementing a custom solution using Java and SQL to change the contents of the Jasperserver database. The code executes on a cron schedule. The data is always for a prior period of time just like in your scenario. There were 2 goals:

 

1. dynamically change the input parameter date in preparation for the next run

2. Rename the report output with an appropriate timestamp.

 

//First I selected the reports I was interested in into a collection

select id, base_output_name

from jireportjob

where base_output_name like #baseOutputName#

 

//Depending on the type of report (weekly, monthly, daily) I programatically

//determined which date I wanted and appended it to the existing report name

update jireportjob

set base_output_name = #baseOutputName#

where id = #id#

 

//I then used the ids from the collection above to get the parameters for the report:

select job_id, parameter_name, parameter_value

from jireportjobparameter

where job_id = #jobId# (this is from the above collection)

and parameter_name = #parameterName#

 

//Then I updated the parameter to it's next date value

update jireportjobparameter

set parameter_value = #parameterValue#

where job_id = #jobId#

and parameter_name = #parameterName#

Link to comment
Share on other sites

THANK YOU!

While your solution didn't work perfectly for me as-is, it did point me in the right direction for effectively accomplishing this goal. My JIReportJobParameters table was empty, but for my requirements, I only needed to change one value anyway.

Once I retrieved the id's of the reports that I would be working with, I created a simple bash script that I'll run as a cron job to automatically update the base_output_name of the report every week before the report is generated.

Since my report date will always be the date of the previous Friday, the script was relatively simple -- it figured out the date of last Friday, appended that variable onto the base filename, then ran a MySQL query to update the filename.

Just tested it and it works perfectly - Thanks so much for your help!

 

Code:
#!/bin/shdbUSER="username"dbPASS="password"DATE="$(date -d 'last Friday' '+%Y-%m-%d')"FILENAME="WeeklyReport-$DATE"QUERY="USE jasperserver; UPDATE JIReportJob SET base_output_name = '$FILENAME' WHERE id=1;"mysql -u $dbUSER -p$dbPASS -e "$QUERY"
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...