Creating a Calender report in iReport/Jasper

[chrisasi-nv.com]

I've searched the forum and see several threads about creating a calendar report using jasper/iReport but no samples or hints or best way to accomplish it.  

Does anyone have a sample or an idea for the best approach or point me in the right direction?

Thanks

Chris

[slow]

I read your question as a little challenge ...

and so I created a small example of calendar ... (Includes also the highlight of Sundays!   :)    )
Since I work with Oracle, I used it to generate the data set for the report ... but there aren't limits, you may create it by using equally JRDataSourceProvider or I think, other DB.

See it as an example of the unrestricted imagination and how IReport (JasperReport ) is a great tool.

NOTE
that the calendar is parametric... so you can choose the start date and the end date in the YYYYMMDD format...
so you can create calendars spanning over multiple years, starting from the middle of a month, or of one month only

the attached PFD is in italian language... but if you run the report with an active oracle connection, it creates a report with the language set in your environment.
 

_________________________________________

if you enjoy it ... give me KARMA points please!    : ) 
_________________________________________

listening:   Afterhours - Quello che non c'è (Live)
("rivuoi la scelta, rivuoi il controllo, RIVOGLIO le mie ali nere, il mio mantello..." (aft.))

 

 

Post Edited by slow at 06/09/2010 09:06

[slow]

a very small improvement...

now the calendar language is parametric... so you can choose the calendar language simply setting the parameter LANGUAGE to one of the Oracle  NLS_LANGUAGE:

AMERICAN, BULGARIAN, CATALAN, CROATIAN, CZECH, DANISH, DUTCH, ENGLISH, ESTONIAN, FRENCH, GERMAN, GREEK, HEBREW  , HUNGARIAN, ICELANDIC, INDONESIAN, ITALIAN, JAPANESE, KOREAN, LATVIAN, LITHUANIAN, MALAY  , NORWEGIAN, POLISH, PORTUGUESE, ROMANIAN  , RUSSIAN, SLOVAK, SLOVENIAN  , SPANISH, SWEDISH, THAI  , TURKISH, UKRAINIAN, VIETNAMESE, etc etc...

 

_________________________________________

if it works... give me KARMA points please!    : ) 
_________________________________________

listening:  Pink Floyd - Comfortably Numb Pulse 1994 (Live)

 "When I was a child
I caught a fleeting glimpse
Out of the corner of my eye.
I turned to look but it was gone
I cannot put my finger on it now
The child is grown,
The dream is gone..." (pf.)

Post Edited by slow at 06/09/2010 09:24

[slow]

listening:  Pink Floyd - Comfortably Numb Pulse 1994 (Live) all the day give me some reason to improve the calendar

 (working to improve it in the work breaks...in case some fussy boss read these words...)

so here to you a ladscape version... multilanguage, with formatted day ...
in this case the parameters are only year and language...

 

 _________________________________________

if iyou like it... give me KARMA points please!    : )  
_________________________________________

listening: Pink Floyd - Comfortably Numb Pulse 1994 (Live)

"There is no pain you are receding
A distant ship's smoke on the horizon.
You are only coming through in waves.
Your lips move but I can't hear what you're saying.
...
I can't explain, you would not understand
This is not how I am." (pf.)

 

 

Post Edited by slow at 06/09/2010 14:09

[slow]

another version... :)

single page, multilanguage, two parameters (year and language)... very basic graphic...
breaks at work are very useful ...

 

_________________________________________

if it works... give me KARMA points please!    : ) 
_________________________________________

listening: Death in Vegas - Dirge

no words, just chills.

 

 

 

 

 

Post Edited by slow at 06/18/2010 15:04

[rw-network]

 I really like what you have done with your calendar examples.  Can you please upload the jrxml files for the last two versions.  

 

THANKS!!

 

Post Edited by scottj314 at 06/18/2010 16:08

[chrisasi-nv.com]

 

Thanks, I'm glad you took this as a challenge

/tools/fckeditor/editor/images/smiley/msn/regular_smile.gif

This will more than get me started on accomplisinh what I need to do.  Thanks for your help on this!!

[chrisasi-nv.com]

 

Would it be possible to get the jrxml from the landscape reports? 

Thanks

Chris

 

[rw-network]

 For the MS SQL people out there. Give this a shot.

Create the function found on this website:

http://snipplr.com/view/16196/ms-sql-generate-all-date-and-times-between-2-dates/

 

Then use this query in your jrxml:

 

Code:

SELECTstart_date As DATES,DateName(day,start_date) As NUMERIC_DAY,DateName(mm,start_date) As NUMERIC_MONTH,DateName(yyyy,start_date) As NUMERIC_YEAR,DateName(month,start_date) As STRING_MONTH,DateName(weekday,start_date) As STRING_DAY,Case When DateName(weekday,start_date) = 'Sunday' Then 1     Else 0 End as SUNDAYFROM    dbo.generateDateTable($P{startdate}, $P{enddate}, 'day', 1)

Post Edited by scottj314 at 06/18/2010 16:13

[slow]

I'm sorry for the delay, but the weekend I hadn't the files with me ...

I'm back in the office now and so here to you the jrxml:
(I need some other extra Karma "power" points to do this!! :) :) :) )

 

I really appreciate the work of scottj314 translating the Oracle sql in mySql sql...
I haven't here a mySql installation otherwise I'll give you help doing it.

I hope this little "calendar-report" work continues with other and other examples... it's a real way to learn using of iReport/JasperReports constructs...

 

_________________________________________
if you like it... give me KARMA points please!    : ) 
_________________________________________

listening:    CSI - Io Sto Bene

 

 

 

 

Post Edited by slow at 06/21/2010 12:52

[chrisasi-nv.com]

Thanks for posting the jrxml. I’ve given you some extra karma points for the work it’s been extremely helpful!!!  

I’ve got some modifications and need to add some drill down functionality to the report using hyperlinks and I’ll post those examples when I get them done. 

 

Thanks again!!

[slow]

another simple and minimal version, multilanguage, with formatted week day: a year in  A4 page :)

 

_________________________________________

if you like it... give me KARMA points please!    : ) 
_________________________________________

listening: Underworld - dark and long (dark train) 
 

[slow]

An.other   "elegant"    charming  version... vertical, multilanguage, single year in single A4...

 

_________________________________________

if you like it... give me KARMA points please!    : ) 
_________________________________________

listening: Skunk Anasie - Secretly

for you, who are not there now ...

Post Edited by slow at 06/25/2010 15:20

[bukkwheat]

 Could you post some tips for making this happen in HQL?  For Karma?

[rw-network]

bukkwheat
Wrote:

Could you post some tips for making this happen in HQL?

 

This is where Google is your friend.  I don't know Oracle syntax, but to get it figured out for MS SQL, I just googled for the "terms" in slow's sql statement that did not make sense to me.  Then thru trial and error and more googling, I came up with a MS SQL solution.  You will most likely have to do the same for HQL.

 

 

[slow]

@bukkwheat:
      I'm sorry but in this period I'm working only with Oracle... so the time to translate all in HSQL it's too little...

@scottj314:
      thanks again for the translation/'googling' work...
 

[izafarid]

 Hi,

need your advise.

My calendar is always start with first column. Eg: 1st Jan 2010 is on Friday, suppose the date start on the 5th column but currently it's printed on the first colum.

 

For MySQL user, this is the query.

For testing purpose.

1. Language parameter has been remove
2. The date range is hardcoded

 

Code:

SELECT DISTINCT	date_format(cdate,"%Y") AS numeric_year,	date_format(cdate,"%m") AS NUMERIC_MONTH,	MONTHNAME(cdate) AS STRING_MONTH,	CONCAT(DATE_FORMAT(cdate,"%Y-%m-"),"01") AS FIRST_DAY,	DATE_FORMAT(LAST_DAY(cdate),"%Y-%m-%d") AS LAST_DAYFROM (SELECT ADDDATE('2010-01-01', numlist.id) AS `cdate` FROM(SELECT n1.i + n10.i*10 + n100.i*100 AS id   FROM (SELECT 0 AS i   UNION SELECT 1   UNION SELECT 2   UNION SELECT 3   UNION SELECT 4   UNION SELECT 5   UNION SELECT 6   UNION SELECT 7   UNION SELECT 8   UNION SELECT 9  ) AS n1 CROSS JOIN (SELECT 0 AS i   UNION SELECT 1   UNION SELECT 2   UNION SELECT 3   UNION SELECT 4   UNION SELECT 5   UNION SELECT 6   UNION SELECT 7   UNION SELECT 8   UNION SELECT 9  ) AS n10 CROSS JOIN (SELECT 0 AS i   UNION SELECT 1   UNION SELECT 2   UNION SELECT 3   UNION SELECT 4   UNION SELECT 5   UNION SELECT 6   UNION SELECT 7   UNION SELECT 8   UNION SELECT 9  ) AS n100) AS numlistWHERE ADDDATE('2010-01-01', numlist.id) <= '2010-12-31') AS dateorder by numeric_year, numeric_month

[slow]

Hi Izafarid... welcome...

I haven't a way to test your code, but reading it I suppose the problems are those:

the code you post generates the start and the end date of a single month of the year passed as argument...

only the subreport creates the single dates in the months.

in the Oracle code I posted, I have used this construct:

           TRUNC(TO_DATE('20100101','yyyymmdd'),'DAY')

that gives as result all the day in a week, but also the days of different months... so, it returns all the days in the 01 month including the days in the weeks that starts and ends the month (from 2009-12-28 to 2010-01-31)... so I used them to create a gap from the first column to the column that prints the real first day in the month.

Try to emulate this with MySql to solve your problem... and post here your feedback.

read this, maybe it can help you...

http://www.terminally-incoherent.com/blog/2006/09/22/mysql-find-week-startend-based-on-week-number/
 

[izafarid]

 Hi Slow,

thanks for your reply and yes you right, the oracle plsql trunc function, need to find the equivalent command in MySQL version to get date for a week month.

Here's code for master report and subreport query with "Oracle trunc" function

Thanks

Code:

************************** Master Query **************************SELECT DISTINCT	date_format(cdate,"%Y") AS numeric_year,	date_format(cdate,"%m") AS NUMERIC_MONTH,	MONTHNAME(cdate) AS STRING_MONTH,	CONCAT(DATE_FORMAT(cdate,"%Y-%m-"),"01") AS FIRST_DAY,	DATE_FORMAT(LAST_DAY(cdate),"%Y-%m-%d") AS LAST_DAYFROM (SELECT ADDDATE(MAKEDATE($P{year},1), numlist.id) AS `cdate` FROM(SELECT n1.i + n10.i*10 + n100.i*100 AS id   FROM (SELECT 0 AS i   UNION SELECT 1   UNION SELECT 2   UNION SELECT 3   UNION SELECT 4   UNION SELECT 5   UNION SELECT 6   UNION SELECT 7   UNION SELECT 8   UNION SELECT 9  ) AS n1 CROSS JOIN (SELECT 0 AS i   UNION SELECT 1   UNION SELECT 2   UNION SELECT 3   UNION SELECT 4   UNION SELECT 5   UNION SELECT 6   UNION SELECT 7   UNION SELECT 8   UNION SELECT 9  ) AS n10 CROSS JOIN (SELECT 0 AS i   UNION SELECT 1   UNION SELECT 2   UNION SELECT 3   UNION SELECT 4   UNION SELECT 5   UNION SELECT 6   UNION SELECT 7   UNION SELECT 8   UNION SELECT 9  ) AS n100) AS numlistWHERE ADDDATE(MAKEDATE($P{year},1), numlist.id) <= MAKEDATE($P{year},356)) AS dateorder by numeric_year, numeric_month******************************* Sub Report Query ********************SELECT distinct DATE_FORMAT(cdate,"%Y%m%d") string_date,       DATE_FORMAT(cdate,"%d") NUMERIC_DAY,       DATE_FORMAT(cdate,"%m") NUMERIC_MONTH,       DATE_FORMAT(cdate,"%Y") NUMERIC_YEAR,       DAYNAME(cdate) STRING_DAY,       case           when  DAYNAME(cdate)  like 'Sunday' then 1           else 0       end as SUNDAYFROM(SELECT ADDDATE(SUBDATE($P{startdate}, INTERVAL WEEKDAY($P{startdate}) DAY), numlist.id) AS `cdate` FROM(SELECT n1.i + n10.i*10 + n100.i*100 AS id   FROM (SELECT 0 AS i   UNION SELECT 1   UNION SELECT 2   UNION SELECT 3   UNION SELECT 4   UNION SELECT 5   UNION SELECT 6   UNION SELECT 7   UNION SELECT 8   UNION SELECT 9  ) AS n1 CROSS JOIN (SELECT 0 AS i   UNION SELECT 1   UNION SELECT 2   UNION SELECT 3   UNION SELECT 4   UNION SELECT 5   UNION SELECT 6   UNION SELECT 7   UNION SELECT 8   UNION SELECT 9  ) AS n10 CROSS JOIN (SELECT 0 AS i   UNION SELECT 1   UNION SELECT 2   UNION SELECT 3   UNION SELECT 4   UNION SELECT 5   UNION SELECT 6   UNION SELECT 7   UNION SELECT 8   UNION SELECT 9  ) AS n100) AS numlistWHERE ADDDATE($P{startdate}, numlist.id) <= $P{enddate}) as mydateorder by  string_date

[Portlight]

Can you create the calender strictly using the java.date util

I need to group according to a day of the week

[jpohlmann]

Thanks for putting this together, slow! 

I've ported this over to pgsql, with a couple of caveats:

 

1.) I don't have the language support, I'm using the java calendar functions rather than pulling out the month string from the database, as I couldn't find a way in pg to support this.

2.) Because of how I am using the report, I have a date parameter that i send in that creates a calendar for that month, rather than for every month in a year.  I am putting together a schedule that I print out monthly rather than annually, which is why I went that way.

I'm going to try and put together a MySQL port for this as well if I get time.

Please let me know if you have any questions.

EDIT: Fixed issue with days lining up.

Post Edited by jpohlmann at 09/11/2012 16:29

[joao.matos]

Hi there, I can't see your improvements on this Calendar.

Could you please attach your jrxml?

 

Regards.

[jorge_MX_DF]

I was.. reading.. all the answers about this topic.. but where do you post the jrxml file ?

I'm in the same problem about a report style calendar...

I hope you can help me..!!

 

thanks

[edwardwalker]

Me too; I can't see the file either.

[bini]

Hi Slow

Great work!

can you please post the jrxml file for the calendar report i have to create a similar report but having a hard time doing it.