Creating a Calender report in iReport/Jasper

0

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

ccbohne's picture
10
Joined: Jun 26 2008 - 5:17am
Last seen: 1 year 4 months ago

21 Answers:

7

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's picture
911
Joined: Feb 17 2010 - 6:57am
Last seen: 10 years 1 week ago
1

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's picture
911
Joined: Feb 17 2010 - 6:57am
Last seen: 10 years 1 week ago
1

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's picture
911
Joined: Feb 17 2010 - 6:57am
Last seen: 10 years 1 week ago
2

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
slow's picture
911
Joined: Feb 17 2010 - 6:57am
Last seen: 10 years 1 week ago

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.

bini - 5 years 1 week ago
0

 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
rw-network's picture
Joined: Mar 1 2007 - 3:44am
Last seen: 3 years 1 month ago
0

 

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

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

ccbohne's picture
10
Joined: Jun 26 2008 - 5:17am
Last seen: 1 year 4 months ago
0

 

Would it be possible to get the jrxml from the landscape reports? 
Thanks
Chris
 
ccbohne's picture
10
Joined: Jun 26 2008 - 5:17am
Last seen: 1 year 4 months ago
0

 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:
SELECT
start_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 SUNDAY
FROM
    dbo.generateDateTable($P{startdate}, $P{enddate}, 'day', 1)</td></tr></tbody></table><br><br>Post Edited by scottj314 at 06/18/2010 16:13
rw-network's picture
Joined: Mar 1 2007 - 3:44am
Last seen: 3 years 1 month ago
2

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
slow's picture
911
Joined: Feb 17 2010 - 6:57am
Last seen: 10 years 1 week ago

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

jorge_MX_DF - 5 years 11 months ago

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

edwardwalker - 5 years 11 months ago

I'm in the same problem ...
I hope you can help me..!!
I can't see the jrxml file.

bini - 5 years 1 week ago
0

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!!
ccbohne's picture
10
Joined: Jun 26 2008 - 5:17am
Last seen: 1 year 4 months ago
0

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's picture
911
Joined: Feb 17 2010 - 6:57am
Last seen: 10 years 1 week ago
2

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
slow's picture
911
Joined: Feb 17 2010 - 6:57am
Last seen: 10 years 1 week ago
1

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

bukkwheat's picture
Joined: Jun 28 2010 - 8:54pm
Last seen: 5 years 7 months ago
0

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.

 

 

rw-network's picture
Joined: Mar 1 2007 - 3:44am
Last seen: 3 years 1 month ago
0

@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...
 

slow's picture
911
Joined: Feb 17 2010 - 6:57am
Last seen: 10 years 1 week ago
0

 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_DAY
FROM (
 
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 numlist
WHERE ADDDATE('2010-01-01', numlist.id) <= '2010-12-31'
) AS date
order by numeric_year, numeric_month</td></tr></tbody></table>
izafarid's picture
Joined: Jul 5 2010 - 7:12pm
Last seen: 9 years 7 months ago
0

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/
 

slow's picture
911
Joined: Feb 17 2010 - 6:57am
Last seen: 10 years 1 week ago
0

 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_DAY
FROM (
 
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 numlist
WHERE ADDDATE(MAKEDATE($P{year},1), numlist.id) <= MAKEDATE($P{year},356)
) AS date
order 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 SUNDAY
FROM
(
 
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 numlist
WHERE ADDDATE($P{startdate}, numlist.id) <= $P{enddate}
 
) as mydate
 
order by  string_date</td></tr></tbody></table>
izafarid's picture
Joined: Jul 5 2010 - 7:12pm
Last seen: 9 years 7 months ago
0
Can you create the calender strictly using the java.date util
I need to group according to a day of the week
Portlight's picture
160
Joined: Jan 6 2011 - 9:11am
Last seen: 5 years 2 months ago
1

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
jpohlmann's picture
Joined: Apr 21 2008 - 5:24am
Last seen: 11 years 10 months ago

Hi there, I can't see your improvements on this Calendar.
Could you please attach your jrxml?

Regards.

joao.matos - 6 years 3 months ago

Hi!,

I can't find the supposedly published JRXML files.

Can someone help me find them?

Thanks a lot.

dpascual_1 - 3 years 1 month ago
1
Hi!,
 
I can't find the supposedly published JRXML files.
 
Can someone help me find them?
 
Thanks a lot.
dpascual_1's picture
Joined: Jan 2 2017 - 12:26am
Last seen: 1 year 2 months ago
Feedback
randomness