Fiscal year in crystal reports 1

[jodilyn]

hi!
i am trying to create a report that shows the number of fiscal years that employees have worked full-time for FULL fiscal years. if fiscal year starts on july 1 and ends june 30 how do i do this. if the employee works just 1 day during the fiscal year as a parttime employee..that year is not counted.
jodilyn [sig][/sig]

 

[kenhamady]

depends on the tables and fields that you have to work with.

Version of CR?
[sig]<p>Ken Hamady-

http://www.kenhamady.com<br><a

href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]

 

[jodilyn]

Version 6 of crystal reports

i am using i table which has the fields:
Status --fulltime, partime ect.
begin date of job
end date of job [sig][/sig]

 

[kenhamady]

Not an easy one.

Are there other status options other than Full-time and part time? Are they ignored?

First, do they get a new record EVERY time they switch from part time to full time and vice versa? For instance, if I am full-time, and I switch to part time for three months and then switch back, does that mean I have 3 records?

[sig]<p>Ken Hamady-

http://www.kenhamady.com<br><a

href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]

 

[jodilyn]

Ken,
The status options are fulltime, parttime and temporary.
i only want records from employees who work fulltime for a FULL fiscal year. July 1 to June 30

if the employee works just 1 day in that fiscal year as partime or temporary then that fiscal year is not counted.

or if the employee begins the job on july 2 and works to June 30 that fiscal year is not counted because he/she missed it by 1 day.

--They do get a new record everytime their job or status changes.

When i run this report i need a count of how many full fiscal years the employee has worked with status as full time.

ANY help would be appreciated!!!!!!
thanks again
jodilyn [sig][/sig]

 

[kenhamady]

I think I understand the question.

But the answer may require an understanding of Crystal variables to accomplish. That is because one record can span more than one year. Also a person may have 5 different full-time jobs that make up one year. You have to treat the person's records as a group and go through them making sure that you only increment when the current job and the next job are consecutive. Have you worked with Crystal Variables?

Lets see if I understand the data:

1) Every time you make a change you end one record with an end date, and begin a new record with a begin date the following day (or is it the same day).

2) A person can change from one full time job to another. If the begin date is one day after the prior end date this is considered consecutive service?

3) When a person is terminated, you fill in a term date for the current job?

Answer these questions and I will try to give you a rough approach to the formulas you will need. [sig]<p>Ken Hamady-

http://www.kenhamady.com<br><a

href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]

 

[jodilyn]

1) When a person changes positions within the company the enddate is filled in. A new record is created with the new job position's information and the begin date for this record is generally the day after the end date of the last job position.
2)Once a person has terminated employement from the company the term date is filled in. i check to see if the term field isnull in crystal reports..if it is or the status is not fulltime i assign a value of zero. otherwise i am trying to work through a series of if statements to get the correct value.

example:
if(isnull({.termdate}) then
(
if{.status) = &quot;fulltime&quot; then
(..........
Thanks soooo much!
jodilyn [sig][/sig]

 

[kenhamady]

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
[sig]<p>Ken Hamady-

http://www.kenhamady.com<br><a

href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]

 

[jodilyn]

Thanks soo much ken!
i'll let you know how it works out!
jodilyn [sig][/sig]

 

[jodilyn]

ken
i am a little confused on the next() & previous
this question pertains to #2 formula

start job end job
record1 1/5/1993 1/3/1995
record2 1/4/1995 7/2/1995
record3 8/6/1995 currently working at this job

do i check for a break like this:

if next({startjob}) - previous{(endjob}) > 1 then
--there has been a break
else
-- no break

do i need to automatically skip to the second record? if so how????

thanks!
jodilyn
[sig][/sig]

 

[kenhamady]

Sorry, You don't use both next and previous in either number 2 or 3. One uses next, the other uses previous. So 2 would be:

if next({startjob}) - (endjob} > 1
and
next ({employee id}) = {employee id}

then increment the variables based on the break

Crystal evaluates all formulas on each record in turn, so you don't skip anything. You just let it increment the variables on each record based on what it finds in that record. [sig]<p>Ken Hamady-

http://www.kenhamady.com<br><a

href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]