Reporting on Missing Data

[Deleco]

Hi All,

I have a bit of a dilema. I need to create a report which shows a persons downtime. There is a diary table but this only holds the dates that appointments are booked for.

Has anyone got any ideas

Any help would be appreciated

Deleco

 

[Madawc]

Which Crystal? It makes a difference

In Crystal 8.5, you can do running totals for various possible dates. Weekends can be excluded using tests like DayOfWeek({orders.ORDER DATE}), with Saturday being 7 and Sunday 1.

If you are allowed to change the database, or can get someone else to do this, you could have a table of dates that would include non-working days, bank holidays as well as weekends.

Madawc Williams
East Anglia, Great Britain

 

[Deleco]

I am using crystal 9 and i do not have write access to the database.

Thanks

Deleco

 

[lbass]

Do you have two tables--one containing the "person" field and the other containing the appointment dates?

-LB

 

[Deleco]

Yes the database consists of lots of different tables. But there is 1 table for staff and another for appointments.

Sorry but i can't see the significance of the question?

Deleco

 

[lbass]

You need to provide more information about what your report should do. For example, are you trying to identify people who have no appointments for a specified period of time, where the time period might be set up as a parameter? Or are you looking to measure elapsed time between appointments? If the latter, are you only looking at the how long ago the most recent appointment occurred? The design of the report depends on answers to questions like these...

-LB

 

[Deleco]

The report needs to show any Days that a person has no appointments. The report is a Downtime report to show me what days a person is not working.

The reason i think it is difficult is because i am trying to report on data that isn't actually there.

Deleco

 

[Madawc]

Not difficult - impossible, except by the methods you've already been told about.

Madawc Williams (East Anglia)

 

[lbass]

One of the following solutions might get you close:

Use a left join FROM the staff table TO the appointments table. Do not use any record selection criteria on fields from the appointments table. Insert a group on {staff.ID}. Then create a date range parameter {?daterange} where you will enter the period of time that you want to review for non-work. Next create a formula {@inperiod}:

if isnull({appointments.date}) or
not({appointments.date} in {?daterange}) then 0 else 1

Then go to report->edit selection formula->Group and enter:

sum({@inperiod},{staff.ID}) = 0

This will give you a list of people who have not worked ANY day during the date range selected.

Another option would be to try a crosstab in the report header or footer. Again, use the left join between tables. If each person has at least one working date and if each date of interest has at least one person working, you should be able to get a crosstab that shows the non-working dates for all staff. You would use the appointment date as the column, staff ID as the row, and a maximum of {appointment.date} as the summary.

-LB

 

[Deleco]

Thanks the cross tab sounds promising for what i need to do!! Ill give it a try and let you all know.

Thanks

Deleco