Crystal 10 Field Help

[Djbell]

Hi All

I am using a table with the following fields to populate my report.

Table Name Employees.

Employee Date Hours
James 01/06/05 8
James 02/06/05 8
Craig 31/06/05 8
Craig 03/06/05 8

After editing and using a group on the name I get the following:-


Date Hours
James 01/06/05 8
02/06/05 8
Craig 31/06/05 8
03/06/05 8

What I would like is the following:-

Date Hours
James Sun
Mon
Tue
Wed 01/06/05 8
Thu 02/06/05 8
Fri
Sat
Date Hours
Craig Sun
Mon
Tue 31/06/05 8
Wed
Thu
Fri 03/06/06 8
Sat


So I would like a way to have the days of the week displayed then the correct info from the table displayed on the correct day. This table will only ever hold one weeks worth of data for each employee.

Any help is appreciated

Regards

Djbell

 

[ShawnPDuffy]

If you are using an Oracle database, you could break the date down into the datepart of day of week and pass that in as a separate field to group on.

select
name,
to_char(date, 'DAY') as Day_Of_Week,
date,
hours
from
TABLE


Then in the .rpt file group based upon Day_Of_Week.

 

[Djbell]

Hi

I am using a sql database, and that function would only result in the day being displayed for that apprpriate date, I want it to list Sun to Sat then put the information from the table on the appropriate day.

Regards

Djbell

 

[mdwyer]

This is not really a Crystal Enterprise question, so you may get better answers in the Crystal Reports forum. Maybe something with a stored procedure would work, or there might be some technique available in the report designer.

You could do something with a UNION of 7 selects, but that would be pretty ugly. I think a natural JOIN will give you what you want, however.

Create a dummy table WEEK with seven records: Sun, Mon,...,Sat. Then
SELECT EMPLOYEES.EMPLOYEE,
WEEK.DAY,
CASE
WHEN WEEK.DAY = DAY(EMPLOYEES.DATE)
THEN EMPLOYEES.HOURS
END AS HOURS
FROM EMPLOYEES, WEEK

I'll leave you the task of cleaning up the sql syntax. I've modified a working Oracle SQL statement with my limited knowledge of ms sql. The concept here is that the natural join will multiply the employee-hours rows will be multiplied by 7 - which is the number you want reported - but you only want hours from the rows where the days match, hence the CASE statement.

Let me know how it works for you.