One of my customers had adapted a report template that they found online to generate a calendar report. But they weren’t happy with the reports performance. When I looked at it I found that the template ran a subreport for every day so there wasn’t much I could do to speed it up. They allowed me to try a different approach to creating the calendar. The result was the planning calendar I wrote about earlier this month. After giving it some thought I figured I could adapt that report to create a more traditional daily appointment style calendar, something like this:
I wanted to create it without using subreports. I also wanted to eliminate the need for a table containing all dates, which needs to be maintained.
I was able to eliminate the “Dates” table by using a 15-record inflation table with records numbered 1 to 15. This allows the report to project each record into the next 15 weeks. So as long as you have at last one record every 15 weeks you get a complete calendar. The number 15 is arbitrary and can be adjusted up or down depending on how large of a gap you expect to encounter in your data. If you have at least one event every week you don’t need the inflation table at all.
And I did find a way to eliminate the subreports by using variables. The sample above is from the Xtreme database. I have posted the report on line for you to download. It uses feeder formulas instead of referring directly to the database fields. This makes it relatively simple to point the report to the tables/fields in the your own database.
If you find it helpful, drop me a line. Or, if you need help deploying the report, call to schedule a short consult.(For examples of my most popular formulas, please visit the FORMULAS page on my website.)