Daily Schedule in CrystalReports

Question

I'm trying to create a daily schedule in Crystal Reports to show appointments for the doctors in the clinic. Down the side will be the hours of the day starting at 7am until 5pm in half hour increments. Across the top would be the doctor's name. Each column would have the doctor's appointment lined up correctly with the time. How can I do this?

Answer 1

You should give some information more about your context;

Solution

anyway follow this steps:

1. Write a query that returns Doctor Name, Day and Time, Appointment
2. Filter the query by the interested date
3. Create a cross-tab, putting Doctor Name in Columns, Day and Time in Rows, Appointment in Field
4. Summaryze Appointment on MAX (or MIN, other summarization report a number not real value)
5. Edit style to omit totals for rows and columns (you don't need I suppose)
6. Edit format for Day and Time (right click on the field), to show only hours (and minutes)
7. Edit options for Day and Time (on Cross Tab Expert) to Display row every minute (default 'every day' will show just 1 line..)
8. Enjoy your report

Be careful

• Cross tab allows just 1 appointment per row
• If there's a time gap in the time coming from the query there will be a gap in the cross-tab (look at 16.00 row); query must be written in a way to report empty appointments.

Sample report

I created a sample report.

Sample query

SELECT  'Doctor 1' as Doctor,  CAST('2012-02-01 07:00:00' AS DATETIME) as DateTime , 'mr. Red' as Appointment
union
SELECT 'Doctor 1' , CAST('2012-02-01 07:30:00' AS DATETIME)  , 'mr. Pink' 
union
SELECT 'Doctor 1' , CAST('2012-02-01 08:00:00' AS DATETIME)  , 'mr. White' 
union
SELECT 'Doctor 1' , CAST('2012-02-01 08:30:00' AS DATETIME)  , 'mr. Yellow' 
union
SELECT 'Doctor 1' , CAST('2012-02-01 09:00:00' AS DATETIME)  , 'mr. Green' 
union
SELECT 'Doctor 1' , CAST('2012-02-01 09:30:00' AS DATETIME)  , 'mr. Gray' 
union
SELECT 'Doctor 1' , CAST('2012-02-01 16:00:00' AS DATETIME)  , 'mr. Purple'
union
SELECT 'Doctor 2' , CAST('2012-02-01 07:30:00' AS DATETIME)  , 'mr. Red' 
union
SELECT 'Doctor 2' , CAST('2012-02-01 08:00:00' AS DATETIME)  , 'mr. Orange' 
union
SELECT 'Doctor 3' , CAST('2012-02-01 09:30:00' AS DATETIME)  , 'mr. Blue'