All dates of booking, dates between start and end date

[idd]

Hi,

I am using Crystal 10.

I have a report which shows room bookings. it is grouped by start date of the booking.

It shows one day per page, with all rooms bookings for that day and will usually be printed of for a week at a time (Monday to Friday)

The user can enter a date range (start and End Dates) in a parameter and the report then shows all the bookings that have start dates between the date range specified.

so if the parameters
ignore weekends in the dates for this example.
Start date = 1st Feb 2008
End Date = 10th Feb 2008

then it would show all room bookings which have a start date that falls within the date range specified.

As the report is grouped by Start date, each booking is then shown only once (on the booknig start date)

However if a booking covers multiple days i.e.

Start date of Booking 2nd Feb 2007
End Date of Booking 5th Feb 2007

I need he report to show the booking on each page, i.e. on the page for

2nd Feb
3rd Feb
4th Feb
5th Feb

At the moment it will only show the bookings on the start date but no other dates which the booking covers.

Any help would be appreciated.

Idd

 

[Madawc]

Have you tried grouping by date?

Failing that, what is your record selection? And how does it show in SQL?

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[idd]

Madawc,

It is grouped by date (Start Date) of the booking.

{Activity.ACT_ACTION_DATE} >= {?Startdate}
and {Activity.ACT_ACTION_DATE} <= {?Enddate}

But the grouping will show the record on the date which matches only the start date. It won't show it for every other day that the booking covers.

Idd

 

[CR85user]

What format is the date information stoed in {Activity.ACT_ACTION_DATE}? Also is there another field or variable which distinguishes between start / end dates?

Initial thought would be that you would need to group by
date based on 'Section printed for each day'

Then suppress the group based on {table.date} < {@startdate} or {table.date} > {@enddate}

Realistically we need more info on the structure of your data. Can you give examples of content of the fields and
which fields define the type of activity etc.

'J

 

[idd]

CR85User,

The field {activity.act_action_date} is a date time field.

the example I will give below is a simplified example of the situation but for the purpose of this thread it would be suitable.

Activity table
Act_ID Act_start date Act end_date Act_Room

Sample data
1 07-Jan-2007 07-Jan-2007 Room a
2 07-Jan-2007 10-Jan-2007 Room b
3 09-Jan-2007 09-Jan-2007 Room c
4 08-Jan-2007 09-Jan-2007 Room a

The report should show something like this

07-Jan-2007
Act_Room Act_id
Room a 1
Room b 2


08-Jan-2007
Act_Room Act_id
Room a 1
Room b 2

09-Jan-2007
Act_Room Act_id
Room a 1
Room b 2
Room C 3

10-Jan-2007
Act_Room Act_id
Room b 2

So I want the report to show the booking for all dates that are booking covers. I.e not just the start date.

Once again all help/suggestions are appreciated

Idd

 

[IdoMillet]

Create a Calendar table (one row for each date).

Join the calendar date to your bookings table based on Calendar.Date >= Booking.Start_Date AND Calendar.Date < Booking.End_Date

Group on Calendar.Date.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[idd]

IDOMillet,

Thats a good suggestion,

Can I use temp tables in a Crystal report or would this have to be a standard table.

Idd

 

[IdoMillet]

If Crystal can see the table, it can report on it.
Having a calendar table is good for a variety of reasons. You may want to create it as a standard one...

- Ido



view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com