Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: years of service formula in crystal reports 2008 Post Reply Post New Topic
Page  of 3 Next >>
Author Message
bayhr
Newbie
Newbie
Avatar

Joined: 02 Mar 2011
Online Status: Offline
Posts: 20
Quote bayhr Replybullet Topic: years of service formula in crystal reports 2008
    Posted: 02 Mar 2011 at 9:56am
I want to create a formula to have years of service (i.e. 5, 10, 15, 20 years etc...) for employees. Below is a formula that was used. I am not an expert with crystal reports. However, I want to create a formula and to also set a parameter so that the parameter prompt will be set in a way that the report will only pull employees with say 5, 10, 15, etc years of service. 
 
 
Service Year Formula used:
 
//When running this report, you must input a Service Recognition Year parameter.  For example, if the
//current date is November 17, 2005 and you want to recognize employees with five years of completed
//service at a luncheon in Feb. 2006 , then you would input a Year parameter of 2000 (ie. 2000 = Year 0, 2001 = Year 1,
//2002 = Year 2, 2003 = Year 3, 2004 = Year 4, 2005 = Year 5).  The rationale is that employees must complete at least
//five full years of service or more.  For example, if someone is hired on Dec. 12, 2000, then they haven't worked a full year
//until Dec. 12, 2001.
//Dec. 12, 2000 - Dec. 12, 2001 - one year
//Dec. 12, 2001 - Dec. 12, 2002 - two years
//Dec. 12, 2002 - Dec. 12, 2003 - three years
//Dec. 12, 2003 - Dec. 12, 2004 - four years
//Dec. 12, 2004 - Dec. 12, 2005 - five years
//Record Selection criteria says only show people either if the year of their Hire Date is equal to the
//year of Year parameter (ie. Year parameter = 2000 Hire date = October 18, 2000) or if the year of their
//rehire date is equal to the year of the Year parameter (ie. Year parameter = 2000 Hire date = July 2,
//1980 Rehire Date = April 28, 2000).

//Calculates the Service Recognition year.  For example, if you run the report on November 17, 2005, then
//the current year (@Current Year) is 2005.  And, when you run the report and choose the Parameter year
//to be 2000, then the Service Recognition Year is 2005 - 2000 = 5 Year Service Award.

//{@Current Year} - {@Parameter Year to Number}

2008 - {@Parameter Year to Number}

 
Year Parameter Setting Used:
 
Please enter the Recognition year (YYYY).  This year will be counted as Year 0 (ie. If running the report in Nov. 2005 to celebrate 5 Years of Recognition in Feb. 2006, then enter 2000; where 2000 = Year 0, 2001 = Year 1, 2002 = Year 2, 2003 = Year 3, 2004 = Year 4, 2005 = Year 5).
 
Any help will be very much appreciated as I'm not an expert with crystal reports. Thanks!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 02 Mar 2011 at 10:06am
why not just have then enter a total number of years '#ofyears' as the parameter and use a dateadd as your select statement
table.hiredate<dateadd('yyyy', - {?#ofyears},currentdate)
IP IP Logged
bayhr
Newbie
Newbie
Avatar

Joined: 02 Mar 2011
Online Status: Offline
Posts: 20
Quote bayhr Replybullet Posted: 02 Mar 2011 at 10:14am
can you please further explain using "a dateadd as your select statement."
 
are you saying the table.hiredate<dateadd('yyyy',-{?#ofyears},currentdate) should be the formula that I'll create for years of service? how do I get the dateadd? sorry for my ignorance but I'm not an expert in crystal reports.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 02 Mar 2011 at 10:20am
dateadd is a function in crystal to add an interval of time to a specific date, in this case a negative number of years to today.
so if you place the formula of
{table.hiredate} <= dateadd('yyyy', - {?#ofyears},currentdate)
in the select expert, your report will only return data rows where the employee was hired on or before (today's date - a paramaters value of years).
It does not per se return a value for total years but rather returns only records that would meet your criteria.
likely you still need to add another condition of
and isnull({table.termdate})
to show only active employees.
Does that help?


Edited by DBlank - 02 Mar 2011 at 10:21am
IP IP Logged
bayhr
Newbie
Newbie
Avatar

Joined: 02 Mar 2011
Online Status: Offline
Posts: 20
Quote bayhr Replybullet Posted: 02 Mar 2011 at 10:29am
It helps but they want the report to actually show employees with service years broken down into 5, 10, 15 years etc... as well as to have a parameter that will prompt users to type in the year.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 02 Mar 2011 at 10:31am
so they need a parma for a min year and then show all employees that meet that condition ranked by their current tenure/years?
IP IP Logged
bayhr
Newbie
Newbie
Avatar

Joined: 02 Mar 2011
Online Status: Offline
Posts: 20
Quote bayhr Replybullet Posted: 02 Mar 2011 at 10:34am
exactly
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 02 Mar 2011 at 10:38am

here is a formula to get the tenure. It is not exactly 100% correct because of leap years but it should be within a day.

datediff('yyyy',{table.hiredate},currentdate) - if datepart('y',currentdate)<datepart('y',{table.hiredate}) then 1
IP IP Logged
bayhr
Newbie
Newbie
Avatar

Joined: 02 Mar 2011
Online Status: Offline
Posts: 20
Quote bayhr Replybullet Posted: 03 Mar 2011 at 2:45am
Thnx for the tenure formula. It is greatly appreciated but how do I get the formula to show not only tenure but say 5, 10, 15, 20 yrs etc...when users are prompted by the parameter. How can I set the parameter so that users can type in say 5yrs and it will only show employees with 5 yrs of service etc...Your help will be appreciated.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Mar 2011 at 3:48am

For clarification,

you want it to always only round to the nearsest completed 5 year mark and allow a user to input any of those 5 year marks and then return all records that are =>than that mark?

Edited by DBlank - 03 Mar 2011 at 3:49am
IP IP Logged
Page  of 3 Next >>
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.031 seconds.