Report Design
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Report Design
Message Icon Topic: Linking to a Subreport with a formula Post Reply Post New Topic
Author Message
ctbny
Newbie
Newbie


Joined: 24 Aug 2007
Location: United States
Online Status: Offline
Posts: 4
Quote ctbny Replybullet Topic: Linking to a Subreport with a formula
    Posted: 24 Aug 2007 at 1:19pm

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. 

IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 24 Aug 2007 at 1:34pm
If I have read your post correctly you probably could do what you want using groups instead of a subreport. I use subreports but do try to avoid them if I can.
 
It might look something like this
 
PageHdr
GrpHeader1a {emplyeeaname}
GrpHdr1b {jobname} - suppress
Details     {clockhours}  {hoursworked} - suppressed
grpFtr1a   {jobname} { sum -clockhours} (sum-hoursworked}
---------------------------
Its hard to give a better layout but it might give you a starting point. Of course because of the data you may need to use a subreport.
 
Since you said you were just getting started with CR let me encourage to understand grouping it can be an extremely powerful tool.
 
Hope this helps.
 
Regards,
 
John W.
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 24 Aug 2007 at 1:37pm
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
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
ctbny
Newbie
Newbie


Joined: 24 Aug 2007
Location: United States
Online Status: Offline
Posts: 4
Quote ctbny Replybullet Posted: 24 Aug 2007 at 6:38pm

Thanx for your suggestions, but I am still stumbling with this so I will try to be more detailed in my problem.

I have three tables Employees, ClockHours, and HoursWorked. I have my report layed out like this...

GroupHeader1 {Employees.Name}

       GroupHeader2 {ClockHours.DateWorked}

                Details {ClockHours.HoursWorked}
 
                SubReport {HoursWorked.TimeWorked}
 
Everything is fine except pulling the hours worked for the subReport.
It needs to only pull rows for the current Employee and DateWorked but this is what I cannot get it to do. 
 
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 25 Aug 2007 at 10:16am

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

IP IP Logged
ctbny
Newbie
Newbie


Joined: 24 Aug 2007
Location: United States
Online Status: Offline
Posts: 4
Quote ctbny Replybullet Posted: 27 Aug 2007 at 12:40pm
Thank you very much John for your help.  It seems to work perfectly as you laid it out.  I just have one small problem with the report.  I want to only show data for one week at at time so I added a parameter that allows the user to select a date range.  But I cannot figure out how to integrate this parameter to narrow the dates to be within this selected range. 
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 27 Aug 2007 at 6:23pm

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.

If you look under CR Help - ParameterFields(Field Explorer Dialog) you will find specific guidance.
When everything is working correctly the report will open showing the prompts for the Start Date and End Date. This will give you the date range aspect you want. You could improve this further but will get you going.
 
Regards
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.