Crystal 8.5 Report Challenge

[KRobinson]

I have an interesting challenge in a report I am doing. Wondering if I can get some feedback on how to approach it? I am creating a daily schedule of booked appointments with field such as name, reason for appt, etc. I have a table were the user can define the start and end time of their appointment day as well as the interval (start 8:00 am with 15 min. intervals would give me appt times of 8:00, 8:15, 8:30, 8:45, etc). The users want their interval times to display regardless if the time slot is booked or not, another caveat is that a counsellor can double and triple book (ie. 8:15 could have three appointments). the end result should look something like this (over simplified):

8:00 Bill Smith
8:15 Tom Jones
8:15 Bob Jackson
8:30 <blank>
8:45 Wilma Stimpson
9:00 Ralph Kramden

Just looking for efficient ideas to do this? Should I do a group by on the time/intervals? Appreciate any help.

 

[synapsevampire]

I suggest that this sort of thing be accomplished by creating a Period table which houses all times, then do a Left outer from that table to your time data and you'll always get all times.

Also do this for all dates (separate table), to allow for the same sort of reporting based on dates.

This are standard Dimensions in Data Warehouses.

-k

 

[chelseatech]

It is quite easy to write a report to display the values that are in the DB.

Then use a formula to display the missing time slots using Previous and Next Functions. We have examples of doing that for missing days in some old issues of Crystal Clear. Check out Novemeber 2000 and the few months before that. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm