Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Fiscal year in crystal reports

Fiscal year in crystal reports

Fiscal year in crystal reports

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.

RE: Fiscal year in crystal reports

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

RE: Fiscal year in crystal reports

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?  

Ken Hamady-www.kenhamady.com
Crystal Reports Training by Ken Hamady

RE: Fiscal year in crystal reports

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

RE: Fiscal year in crystal reports

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.

Ken Hamady-www.kenhamady.com
Crystal Reports Training by Ken Hamady

RE: Fiscal year in crystal reports

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.

if(isnull({.termdate}) then
  if{.status) = "fulltime" then
Thanks soooo much!

RE: Fiscal year in crystal reports

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 Hamady-www.kenhamady.com
Crystal Reports Training by Ken Hamady

RE: Fiscal year in crystal reports

Thanks soo much ken!
i'll let you know how it works out!

RE: Fiscal year in crystal reports

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
   -- no break

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


RE: Fiscal year in crystal reports

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
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.

Ken Hamady-www.kenhamady.com
Crystal Reports Training by Ken Hamady

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close