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

Joined: 02 Mar 2011
Online Status: Offline
Posts: 20
Quote bayhr Replybullet Posted: 03 Mar 2011 at 3:53am
well, I want the user to input any of these years: 5, 10, 15, 20 years etc... marks and then return all records with employees having 5, 10, 15 etc...years of service
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Mar 2011 at 4:04am
sorry nut for clarity here... if they enter 5 years do you want them to see only people with 5 or see people with 5, 10, 15 , 20 etc.?

Edited by DBlank - 03 Mar 2011 at 4:07am
IP IP Logged
bayhr
Newbie
Newbie
Avatar

Joined: 02 Mar 2011
Online Status: Offline
Posts: 20
Quote bayhr Replybullet Posted: 03 Mar 2011 at 4:09am

that's ok and thanks for your valuable 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.
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Mar 2011 at 4:11am
right, but when they do enter 5 should they only see 5 or do they see 5 and above?
IP IP Logged
bayhr
Newbie
Newbie
Avatar

Joined: 02 Mar 2011
Online Status: Offline
Posts: 20
Quote bayhr Replybullet Posted: 03 Mar 2011 at 4:14am
they should only see 5.  this should be the same for the other years (i.e. 10, 15, 20, 25, 30 etc...)
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Mar 2011 at 4:21am
ok.
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:
(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)
IP IP Logged
bayhr
Newbie
Newbie
Avatar

Joined: 02 Mar 2011
Online Status: Offline
Posts: 20
Quote bayhr Replybullet Posted: 03 Mar 2011 at 5:02am
Thanks very much :) but I'm having an issue deleting old formulas that was placed in the report. will get back to you shortly but in the meantime thanks for all your valuable assistance :)
 
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 Smile


Edited by bayhr - 03 Mar 2011 at 9:01am
IP IP Logged
bayhr
Newbie
Newbie
Avatar

Joined: 02 Mar 2011
Online Status: Offline
Posts: 20
Quote bayhr Replybullet Posted: 03 Mar 2011 at 9:26am
Hi there,
 
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
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Mar 2011 at 9:34am
not sure I understand what you want exactly.
datediff('yyyy',{table.hiredate},currentdate) - if datepart('y',currentdate)<datepart('y',{table.hiredate}) then 1
will give you there actual tenure in completed years, although your example if hired on 8/8/2005 will wtill be 5 years as of today because we are using today as our cut off. You can use a param to see whom will hit  a tenure on a future date by replacing currentdate with a param date.
IP IP Logged
bayhr
Newbie
Newbie
Avatar

Joined: 02 Mar 2011
Online Status: Offline
Posts: 20
Quote bayhr Replybullet Posted: 03 Mar 2011 at 9:49am

Thnx. Actually, I just thought of it and that should be fine because if an employee has 5.5yrs of service, they aren't necessary at 6yrs of service yet so they'll show up as 5yrs of service...so this is fine. sorry about the confusion.

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...)
IP IP Logged
<< Prev 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.