I have a report that shows clock hours for each day for each employee.  Also it shows hours worked for all jobs for that day and employee.  This is my first attempt at a crystal report, but I believe the best way to do this is by using a subreport for showing hours worked on jobs.  From reading the book, I found that I will need to use a formula to link to this subreport, since the date is not a key field in the db.  But I don't understand how the formula is set up to accomplish this. 

If you follow John's recommendation to use groups, that is great. If you need to create a formula subreport link in the future, here is more info:

In the CR.NET book, see page 221 for the steps for linking on a formula field.

For your formula, all you have to do is return the field's value. In Crystal syntax, you would create a new Formula (call it whatever you want) and in the Formula worshop enter the field name:

{table.field]

That's all you do. Then use the steps on pages 221 to build the link.


Edited by BrianBischof - 24 Aug 2007 at 1:39pm

Given my feelings towards subreports here is what I would offer as alternative. You can fine tune this as needed.

Create two command ojects in the database expert

1. Employees (Command Object) - here might be the SQL statement for that object.

SELECT employee.employeename, employee.employeeid, clockhours.dateworked,clockhours.hoursworked
FROM employee
LEFT INNERJOIN clockhours ON employee.employeeid = clockhours.employeeid

2. HoursWorked (Command Object) - Here might be the SQL Statement for that object

SELECT Hoursworked.employeeid, Hoursworked.timeworked,HoursWorked.DateWorked
FROM HOursworked
GROUP BY employeeid,dataworked

Then use the linking tab in the database expert to link the two command objects, Left Join the Employees object to the HoursWorked object. Make sure you Left join all the records the records from the Employee side to the those in the hours worked. Join by the Employee Id and dateworked

Then layout your report this way

GroupHeader 1a {employee.Name}
    GroupHeader1b {ClockHours.DateWorked} suppress
 ` Details {ClockHours.HoursWorked} {HoursWorked.TimeWorked}

That should do it, all without a subreport, and their inherent difficulties.

I cannot test this for you, so there could errors, but hopefully its close enough to sort it out. Also you could build a second copy of the report and test this against it without losing the work you have done this far. I think you will know in about 10 minutes if this a viable solution.

Others on the forum can certainly chime with correction to the logic I am offering.

Hope this helps,

Regards,

John W.
www.CustomReportWriters.net

If you use the Field Explorer you will see an item called Parameters. You  right click on that and select New.  While there are many ways to do this, the manner I will discribe will be to create a Start Date and End Date. If you follow the wizard you will use the names above as the name of each of the parameters, The will be of the Date type and point to the field DateWorked.