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. 

 

 

 

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

 

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.

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

to show only active employees.

Does that help?

Well, I want them to be able to not only enter 5 which will show employees with 5 years of service but also they should be able to do the same for 10, 15, 20 etc...which should also pull the employees with those years of service.

 

 

Make a paramater called 'years of service' (or whatever) to allow users to enter the 5/10/15/etc. values.

likley you want this as a

type=number

LOV =static

insert values as 5

then 10 then 15etc. until you get to your max amount

 

go into the select expert and add inthe formula as:

 

I actually created a new service recognition report using the formulas you provided me and it worked this time around. Thanks a lot for your support. It really helped me a great deal. You're the BEST

 

So I realized a little issue that I thought you could help me with again.

 

example: employee hired date is 8/8/2005. so the year of service should be 6 (i.e. 6 years on 8/8/2011). however, they show up in year 5 and so forth...

 

Is there a way that the prompt will calculate the exact years of service using their hire date? Your help again will be appreciated. Thanks

 

However, I want to insert a group in the report that will group the employees by their service years (i.e. 5, 10, 15, 20 etc...)

 

The grouping worked but I want to find out if it's possible to have a select criteria for the years of service to have the option of 5, 10, 15, 20 etc...so that the user can select those from the select criteria which will in turn populate them in the report as groupings. Your help will be great again. Thnx

that formula will convert all the rows to there respective grouping of 5-10-15-20-etc.

 

example: I selected 5, 10, 15 years but later wanted to exclude 5,10years and to include 15,20years...I don't see them in the select criteria. I only see the ones that I previously selected (i.e. 5,10,15years) Could you please help

 

 

you need to use the paramters on a report refresh in order for end users to select the data.

Did you create a paramater to use?

 

1) Could you please assist if it's even possible to use the parameters on a report refresh to select all years of service at the same time

 

or

 

2) Is it possible to see all the years of service in the select expert menu so end users can either include/exclude the years of service they want to see on the report?

 

Please help and again thanks for all your valuable help. It is greatly appreciated!

In the parameter options set it to allow for multiple values

in your select expert make your select statement use that param with the other formula that gets that 5 year values

{?Param} = (datediff('yyyy',{table.hiredate},currentdate) - if datepart('y',currentdate)<datepart('y',{table.hiredate}) then 1) - ((datediff('yyyy',{table.hiredate},currentdate) - if datepart('y',currentdate)<datepart('y',{table.hiredate}) then 1) MOD 5)