Here is how I would approach it. You will need to work out the details and test it. You can't use just one formula, because you have to step through several records to get the information for one person.

The idea is that you step through each person's jobs starting with the first one. You calculate which fiscal year starts after this job. Then you see if there are other continuous job records for this person. If so you wait until you get to the end of the person's records (or a break their employment) and then you calculate the end of the fiscal year based on the ending of the last job in the series. You calculate the difference between the FYstart date of the first job, and the FYend date of the last job.

The tricky part is if there is a break. then you have to store the result of the first series of jobs, and add that to the result of other series of jobs for the same persons. This requires a sophisticated use of Crystal Variables.

First, group the records by Employee.

Sort them so that they are in ascending order by start date.

Now create 4 formula fields that will process 2 date variables and a numeric variable.

1) The first formula will go on the group header. It will reset the numeric variable to zero and it will also calculate the beginning of the fiscal year after the first job's start date. The following formula will do the calculation for the date assignment and come up with the date for 7/1 in the year following the start date:

StartFY := Date (Year ({job.StartDate} + 184) + 1 , 7 , 1 )

2) Second formula goes on the detail band. It uses the next() function to check the next record and see if it is the same person but with a break between jobs (compare start and end dates of the two records.

If it is a break, you need to:

Calculate the end of the last fiscal year to end during the job. Use the following formula to find the last fiscal year to end during this job record:

EndFY := Date (Year ( if Isnull({job.EndDate}) then currentdate else {job.EndDate} + 184) - 1 , 6 , 30 )

Then calculate the number of years between the two date variables, and add this value to current contents of the numeric variable.

This second formula shouldn't do anything if the next record isn't the same person with a break between jobs.

3) Third formula also goes on details. It uses the Previous function to check the record before and see if it was the same person and if the jobs were contiguous.

If same person, but not contiguous, reassign startFY using the same formula as in the header.

If same person, and contiguous reaasign the endFY using the formula mentioned in the second formula.

4) The fourth formula goes on the Group footer. It calculates and assigns endFY (see formula above), calculates the net between StartFY and EndFY and adds it to the numeric accumulator. This field gets displayed in the Group Footer (and is reset in the next employee's group header.

Good Luck,

Ken

Ken Hamady-www.kenhamady.com

Crystal Reports Training by Ken Hamady