Technical Articles
Calendar Report Samples, Crystal Reports
If you have ever wanted to create a calendar style report and didn’t know where to start then this blog post will hopefully help you out. There is a collection of different calendar style reports attached to this post.
a) a calendar created off of a date lookup table (a date lookup table is the recommended method for performance…you can also use an xls or csv if you can’t get a lookup table created in your database of choice)
b) a calendar which automatically populates the dates
c) a yearly view calendar which uses small RTF calendars to display the individual months
d) a sample finished report (nicer looking than the above 2) using the date lookup method…this report also contains a couple of small RTF calendars which can be used for linking from month to month
e) a calendar which populates all dates in a range but also has an on-demand subreport for the months (note that this won’t have great performance as a subreport is run for each day)
f) a yearly calendar created off of a date lookup table
g) a yearly calendar created off of a date lookup table with charts
There are instructions on the calendars a, b, f & g to help you get this style of report off of your data system…the calendars created off of a date lookup table are by far the easiest to implement and are also the fastest performing calendars.
The yearly calendars f & g are new for 2014 and both use a main report and subreport combo, both formatted with multiple columns, to create the overall view. Although there are no objects in the individual days in these particular samples, you can add objects such as (very small) charts, summaries, etc. to those days as each day is still a report group section.
Calendar Yearly View Populated With a Date Lookup Table
Calendar Yearly View Populated With a Date Lookup Table With Charts Etc.
| Attachment | Size |
|---|---|
| Calendar Which is Populated With a Date Lookup Table | 95 KB |
| Calendar Which Populates All Dates in a Range | 237.5 KB |
| CalendarYearlyView | 1.22 MB |
| CalendarMonthlyView | 468 KB |
Calendar Which Populates All Dates in a Range With On Demand Subreport
actually i was looking for a solution to a problem that i have for quite some time. what i am trying to do is to get a report based on the range of dates thru date time picker control the satement of code that i am using is as follows:
crystalReportViewer1.SelectionFormula = "{Employees.DateOfJoin}>= '" + dtpFrom.Value + "' And {Employees.DateOfJoin}<= '" + dtpTo.Value + "'";
every thing is ok until i press the OK button it says "a date time is required here".
i am using oledb which means ms access database.
any help would be great.
Great information and post. It's not current, but the links to attachments doesn't work. Possibly a new "feature" or "improvement" from SAP. any suggestions on how to get the files?
I had the same problem but google was very helpfull. 🙂
http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/80322bed-4baf-2f10-6b9d-a5f497b316f6
Calendar Which Populates All Dates in a Range
http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/c08f5b06-4caf-2f10-2db7-f1234f280626
CalendarYearlyView
http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/80004e2e-4caf-2f10-35a3-af270e085bcb
CalendarMonthlyView
http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/f0a10821-4caf-2f10-c8b9-ce05ee229ec2
Great calendar - but I am having an issue when I have more data than what will fit in the sub report(day). It won't expand and show all my appointments. How can I have the calendar expand to additional pages? I would be fine with it having 2 weeks on 1 page and the other 2 weeks on another page if it had to expand. I have attached a copy of the form that I have adjusted.
https://www.hightail.com/download/OGhlTG0zcHZwcFZ2TzhUQw
thanks Christine,
what i would recommend is:
1) right click on your existing subreport and do a Save Subreport As
2) now click on the Insert menu and choose Subreport and then select Choose An Existing Report, selecting the one that you saved in step 1
3) place this report over top of the subreport you have now
4) right click on the new subreport and choose Format Subreport and then go to the Subreport tab
5) check the On Demand Subreport option
6) go into the On Demand Subreport Caption option and use '' as the value
now you'll be able to click on your report and open up full size.
just a suggestion, but if you look at the top report (populated with a date table) it will run many times faster as there's no subreports slowing things down. you can put your new subreport on it as an on demand subreport and it will be much faster. this however requires that you have a date lookup table in your database, which everyone really should have if they're doing reporting.
i hope that this helps,
jamie
Thanks Jamie. I am not sure I did step 6 correctly because my calendar still looks the same and did not expand as needed.
I could really use the help getting this report up and running. Do you/SAP offer paid support for creating or helping with reports.
thanks again!
Christine
hi Christine,
please ensure that the On Demand Subreport option (Format Subreport > Subreport tab) and the Can Grow option (Format Subreport > Common tab) are checked. otherwise your subreport will be small.
have a look at the report "Calendar Which Populates All Dates in a Range With On Demand Subreport" which has a new "Daily" subreport...this report is now below the report list above. after you extract the contents, change the .txt extension to .rpt.
the new on demand subreport is full size in both design mode and in preview mode...please click on the Daily.rpt Preview tab that is already opened or you'll be prompted for the database specifics.
cheers,
jamie
Jamie,
Thank you for your help on this. I had to go a different route because I am not looking for a "On Demand" report option. My users will not have access to use this feature. I needed all to show on a calendar view from the start. I have been able to expand your original report to show 1 week per 8 1/2 x 11 page. I can't get it to grow the way I anticipated, so I hard coded the "Multiple Columns" height. Still would love for this to be a grow field.
Can you also direct me where there are other templates available through SAP?
Thanks again,
Christine
hi Christine,
unfortunately there's no central place to find all sample crystal reports in scn. that would be a good thing to have. my advice would be to search for "crystal reports samples" and also to look at the blog posts to see if there's a sample posted there as well.
cheers,
jamie
Love, love, LOVE the Calendar Monthly View report! It has given me some good ideas to take back to the office with me after the holiday season.
thanks for the comments Zac. i've just added 2 new yearly view reports as well.
Very good report!! I like the idea.
I'm trying to replicate you're very first report(Calendar with date look up table) with exactly what you did in formulas and groupings but not sure why it is not capturing the dates horizontally.
I know, that I can just do "set database location" but curious to know the steps you did to achieve this.
Thanks,
Abhi
hi Abhi,
there are 2 ways that you can set the report to your own database...using the set database location as you mentioned...to do this there are steps in the Report Header if you go to the Design tab.
method 2 is to first add your database to the report and then replace the datefield in the existing formulae and then delete the old database. this method is described in more detail here. see the (A) section, "if you have an existing report" and this outlines the steps. personally i prefer this method for designing reports if i know that the report is going to be distributed or used as a template as you never have the issue of objects like cross-tabs, charts, etc. dropping off the report or any other errors.
cheers,
jamie
Hi Jamie,
I'm good with set database location. but curious to know how did you achieved this below highlighted format.
Abhi
hi Abhi, this is a Multi-Column layout which is a feature of crystal reports. in a report you can format the Details sections & the Group sections to use a multi-column format.
to see the settings for this format, right click on the Details section and choose Section Expert. right away in the Common tab you'll see a Format with Multiple Columns option. when this is chosen, you get the extra tab for Layout where you put in your multi-column options. the calendar format takes advantage of this feature.
in my opinion, this is one of the best features in crystal as it allows you to create a whole new design type of reports. you can have vertical sections that can contain objects like charts, subreports, etc. and these columns can be dynamically sorted using the sort feature as well. if you want to download another multi-column report to see this type of functionality, please see my blog post here. the blog post is talking about creating your own charts, but there's a sample report attached to the post and there's also a couple of pics of multi-column designs.
cheers,
jamie
Hi Jamie,
Monthly calender report is too good .
Thanks for sharing those reports
Rajesh N
thanks Rajesh N for the comments. i've just added 2 new yearly view reports that are also based off of data lookup tables and are based on the multicolumn methods as well.
I cannot download the examples..can anyone help?
thx
thanks Taniarto for letting us know. I've asked the fine folks at SCN to see if they can fix the issue.
-jamie
The Links are Broken
thanks Julio for letting us know. I've asked the SCN team to have a look at the broken links.
-jamie
Are there more current links for the reports? I cannot get to these. Please help
hello Yelena,
the 4 links above in the Attachments section do work in chrome...however there seems to be several more links that are missing. i'll alert the scn team to see if they can fix those.
-jamie
The links aren't working. Could you have them refreshed please?
hi Melanie, it looks like one of the Moderators has fixed the links in the "Attachments" section. I noticed that 3 of the reports mentioned were not there, so I put links to them at the bottom of the blog post.