Daily schedule of appointments by time w/open time slots

[vbMonk]

I've been asked to write a report against a scheduling system. They want the report to show "open" time slots. The SQL tables against which I'll run the report have no records for open time slots, of course. I'm looking for a way to perhaps write a function or something that will output each/every hour of the day when I run the report. Then, I need to match the records of the scheduled events to the hour they belong to. Thus, I'd like the report to look something like:

8:00 am
8:30 am
9:00 am scheduled event (db record)
9:30 am scheduled event (db record)
10:00 am
10:30 am

etc.

Anyone ever done anything like this, or have any suggestions?

 

[synapsevampire]

This is common.

It's also Crystal version and database dependent, consider that technical information is important when requesting technical assitance.

The way that a data warehouse would handle this is by creating a periods tables on the database, one that has all dates, and one that has all time increments of interest that can be joined to the dates table.

In this case you want hours? (your text says hours, your example shows half hours).

This might also be done uing Crystal, please post some specifics.

-k

 

[vbMonk]

I really am sorry. I'm using Crystal 8.5 and writing against an MS SQL 7.0 database.

You are right, I said hours and showed half hours. The specifics at that level aren't really important to me at this point. In fact, it's highly likely that I'll have different groups of users who will want different increments. I'm hoping that the solution to one set of increments will suffice for all increments.

I'm hoping to avoid having to create a table with the time increments stored in it. Or at least, that's going to be my last resourt. I'm really hoping to be able to handle this within Crystal. That may not be possible.

 

[IdoMillet]

You could probably do this in Crystal alone but it would take significant tweaking (and a tedious explanation).

Is there a good reason for why you see creation of a helper table as a last resort?

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[vbMonk]

Well, I suppose it's because if I can solve this problem strictly in Crystal, then I don't have to involve anyone else, either in the creation of the solution or in it's ongoing support. This system is a scheduling system for a large hospital network. I'm frankly not even sure that I could add a user defined table to the database if I wanted to. But, even if the vendor of the system didn't have a problem with it, to solve the problem that way raises some interpersonal and management/support issues that, while admittedly not insurmountable, are at least not personally insignificant.

Really, I just put this question out here to try and gauge whether a) doing this in Crystal is possible, and b) if it is, how difficult it is. I'm hoping that maybe someone, somewhere, has done something similar. I really don't want to put anyone out, though. If it's too much of a hassle to explain, then that's cool...and I mean that sincerely. I've already told my users I can't do it. At this point, I'm just wondering for my own knowledge as much as anything.

 

[IdoMillet]

1. Group the report by Date

2. Within the Group, have 8 or 16 (depending on hours or semi-hours) GH1 or GF1 sections (right-click the section and select Insert Section Below).

3. In each of these sections, insert a subreport that accepts as a link from the main report the date but restricts itself to the appropriate time slot. If it can't find info, it is easy to show nothing or show a static text saying "No Appointments".

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[vbMonk]

Thank you. I'm going to noodle around with this and see how it goes. I appreciate your help.