How do I calculate years of service?

merrittr · May 4, 2009, 6:01pm

Hi

working with Dynamics NAV 4.0 [employee] and [employee assignments] tables

what I want is a report where 5 ,10,15,20 and 25 years of service will be printed out

so my challenges are:

1-calculate years of service [employee assignemnts].[enddate] - [employee assignemnts].[contract start date] and sum them up

2-if number of months falls into 5 15 15 20 or 25 years of service print them out

any ideas?

admin1 · May 4, 2009, 7:31pm

How about this:

Years := round((EndDate - ContractDate)/365);
CurrReport.SHOWOUTPUT(Years in [5,15,20,25]);

merrittr · May 4, 2009, 8:44pm

Hi Erik

thats great , I am kind of new at this where would I paste this code as well I woul;d like to output the persons name

merrittr · May 5, 2009, 4:03pm

ok here is the code I have , under Employee Assignment - OnPostDataItem()

however the error I get when I go try to run it (file>run) is

Type conversion is not possible because 1 of the operators contains an invalid type.

Boolean :=Text

Years := round((“Employee Assignment”.“End Date” - “Employee Assignment”.“Contract Start Date”)/365);

if (Years in [5,15,20,25]) then begin
CurrReport.SHOWOUTPUT(Employee.“First Name”);
CurrReport.SHOWOUTPUT(Employee.“Last Name”);
CurrReport.SHOWOUTPUT(Years);
end;

DaveT · May 5, 2009, 4:19pm

Hi Rob,

The error is coming from the CurrReport.SHOWOUTPUT(Employee.“First Name”);

This command is designed to flag is the section will print or not and the argument is a boolean. Fields to be printed should be add to the sections of the report.

I think you need to read up on the basics of designing a report and a good start is the “Application Designers Guide” (w1w1adg.pdf in the Doc directory of the install DVD). You can also look at some of the standard reports supplied with the system for more guideance.

merrittr · May 5, 2009, 6:17pm

Hey tahnks Dave,

That makes sense now, one thing though how do I get the years value on the report it doesnt show up in the fields?

merrittr · May 5, 2009, 7:28pm

here is an update, after further reading as per DaveT

Employee Assignment - OnPreDataItem()

oldYears := 0;

Years := 0;

Employee Assignment OnAfterGetRecord()

Years := ROUND((“Employee Assignment”.“End Date” - “Employee Assignment”.“Contract Start Date”)/365) + oldYears;

oldYears := Years;

Employee Assignment - OnPostDataItem()

CurrReport.SHOWOUTPUT(Years IN [5,15,20,25]);

so I have put off worring how to get

on the report so when I run this as a blank report I get

The Data is not valid not sure what it doesn’t like again any hints appreciated.

DaveT · May 5, 2009, 9:00pm

Hi Rob,

A couple of questions to get the design right:

Is there multiple entries per employee in the Employee Assignment table ?

if so the you will need to total the year

e.g. TotYears := Totyears + Years; in the OnAfterGetRecord

Do you need to show any Employee Assignment records?

To print a value you will need to go to the sections view and add the details to be printed in the appropriate section probably a Employee Assignment footer. To add a variable to print, add a textbox and set the source to the variable e.g. TotYears

The command:

CurrReport.SHOWOUTPUT(Years IN [5,15,20,25]);

is normally placed in the OnPreSection trigger of the section you want to conditionally print. The OnPostDataItem is too late. Also you might need to change to TotYears in the test.

merrittr · May 5, 2009, 9:49pm

yep you have it exactly correct.

I need to tally the years of service in all assignments, thats what I was trying to do in:

Employee Assignmnet - OnPreDataItem()

oldYears := 0;

Years := 0;

Employee Assignment - OnAfterGetRecord()
Years := ROUND((“Employee Assignment”.“End Date” - “Employee Assignment”.“Contract Start Date”)/365) + oldYears;
oldYears := Years;

all I need is to output

Years Employee No. First Name, Last Name

any help is appreciatted

Thanks again Dave

DaveT · May 6, 2009, 10:18am

Hi Rob,

You’re nearly there - create a footer section for “Employee Assignment” dataitem (F3 in the sections view) and and in 4 textboxes. For each of the textboxes assign the SourceExpr to Years, Employee.“No.”, Employee.“First Name” and Employee.“Last Name” (assuming that the dataitem for employee table is “Employee”).

If you are thinking of doing report development on an ongoing basis then you will benefit hughly from learning from someone the report design experience.

merrittr · May 6, 2009, 4:29pm

Hey thats great thanks Dave, this was a good lesson on report design. there is one thing though

In have the showoutput in the “OnPreSection” of the Employee Assignment Footer

then the rest of the bussiness logic in the Employee Assignment triggers (not the footer) as I mentioned in my last post.

so when I run it I get the “generating page 1” message down below and it seems to be in a loop (has been running for 45min so far)

any idea why it would take so long

employee table has ~ 6000 records and employee assignments has around 12000 ~ 72000000 Itterations

DaveT · May 6, 2009, 4:37pm

Hi Rob,

The common mistake is not to indent or link the dataitem. Indent the “Employee Assignment” dataitem and link it with the Employee dataitem on employee no. ( use the AssistEdit for the DataItemLink property)

merrittr · May 6, 2009, 9:21pm

Ahh Yes I am a dummy that did the trick, I still have one more issue

I calculated Years and Days like this

Years := ROUND( (FIELDNO(“End Date”) - FIELDNO(“Contract Start Date”) )/365) + oldYears;
Days := (FIELDNO(“End Date”) - FIELDNO(“Contract Start Date”)) + Days;

but I end up with some goofy output nothjing that resembles days or years ,once again any ideas

(Sorry to be a pest Dave)

Report Part EndDate StartDate Years Days Emp No FName LName

Employee Body 0 0 99999 Elizabeth Tester
Employee Assn. Body 11/30/2006 8/30/2005 -0.01 -2 99999 Elizabeth Tester

Employee Assn. Body 8/29/2005 -0.01 -4 99999 Elizabeth Tester

Employee Assn. Body 6/29/2007 12/1/2006 -0.01 -6 99999 Elizabeth Tester

Employee Assn. Body 8/27/2007 -0.01 -8 99999 Elizabeth Tester

Employee Assn. Body 10/22/2007 9/26/20

DaveT · May 6, 2009, 10:42pm

Hi Rob,

I can see the following problem:

You are using FIELDNO which return the field number of the field not the value - just use the variables.
You don’t need to print the employee or employee assignment - just delete the section (maybe you’re showing them for testing)
You will have to handle blank start and end date
Add the employee name to the footer section.

merrittr · May 15, 2009, 10:15pm

Thanks Dave and Ernst I almost have it except for one last detail after calculating the individuals “Years of service” I just print them out if they are in

1:-

5.00,10.00,15.00,20.00,25.00,30.00 (I am using a decimal here) but what I was hoping to do is either accumulate each user into a text string and I.E.

5yearsString := 5yearsString + username +" " + userno + " " +years + “\n”; not sure how to inset the newline this is psuedo code.

then accumulate line after line of 5 10 … year employees then print that big string out for each year catagory

2:-

the second and better way Id like to do it is promt the user for a years of service value (5.00,10.00,15.00…30.00)

then just print out that years employees

any ideas on how to do that?

code so far

using Employee

Employee Assignments

employee - onAfterGetRecord()
Days := 0;
Years := 0;
oldYears := 0;
ServiceYears := 0;

sections

Employee Assignment Body (1) OnPreSection()

EndDate := “Employee Assignment”.“End Date”;
StartDate := “Employee Assignment”.“Contract Start Date”;
EffDate := “Employee Assignment”.“Effective Date”;
IF FORMAT(EndDate) = ‘’ THEN EndDate := TODAY;
IF FORMAT(EffDate) = ‘’ THEN EffDate := EndDate;
IF FORMAT(StartDate) = ‘’ THEN StartDate := EffDate;

IF Earliest > StartDate THEN Earliest := StartDate;
IF Latest < EndDate THEN Latest := EndDate;
IF FORMAT(Latest) = ‘’ THEN Latest := EndDate;
IF FORMAT(Earliest) = ‘’ THEN Earliest := StartDate;
ServiceYears := ROUND(( Latest - Earliest )/365,1,’=’);
CurrAssYears := ROUND(( EndDate - StartDate )/365);
Years := ROUND(( EndDate - StartDate )/365,1,’=’) + oldYears;
Days := (EndDate - StartDate) + Days;
oldYears := Years;
testyears:= ROUND((EndDate - StartDate)/365);
CurrReport.SHOWOUTPUT(FALSE);

then in the Employee Assignment Footer (2) I output name emp # and years of service (calculated both ways)

I calculate years of service 2 ways for hr

-one by summing assignment time per each assignment

-two subtracting the earliest assignment date from the latest assignment date

turns out calculating “Years of Service” is NOT like calculating critical mass (no big reaction if you over estimate Years of service) so I give it to them both was

and they can decide which one is valid

DaveT · May 18, 2009, 10:24pm

Hi Rob,

Sorry been away for a while [:$]

Not sure what you’re trying to acheive with the 5yearsString - can you give an example

P.S. to test is a date is empty you can use

IF EndDate = 0D THEN

EndDate := TODAY;

merrittr · May 18, 2009, 11:35pm

Sorry Dave my cutting and pasting skills were a bit imparied , My goal is to segregate years of service thusly:

jim smith 10001 5 years of service

tim white 10999 5 years of service

.

Fred McGratten 30009 5 years of service

Rob Merritt 18999 10 years of service

.

etc

so I was thinking I could loop through the program find all the 5yr of service people concatenate them amm (seperated by a CRLF not surwe how to do that)

then spit out that whole big concatednated string and do the same for 10years 15years 20years…30years all being a string off all people with that many yrars of service . Is that feasible

DaveT · May 18, 2009, 11:49pm

Hi Rob,

Not the way I would solve this. I think you’re earlier suggestion to do a filtered run for each level would work best.

So

Create a variable - NoOfYears
In the request form add a textbox and set the sourcw expression to NoOfYears
In the OnPreSection of the footer (where you want to print) and the code

a. rounding to five years:

OldYears := round( Oldyears, 5 );

CurrReport.SHOWOUTPUT( OldYears = NoOfYears);

b. Range ( e.g. 5-9.9 years):

if ( Oldyears < NoofYears ) and ( Oldyears > NoofYears +4.99 ) THEN

Currreport.SHOWOUTPUT( FALSE );

merrittr · May 19, 2009, 12:14am

Perfect that did the trick thanks Dave this sure was a learning expeirence!

DaveT · May 19, 2009, 12:23am

Glad to help [:D] - that’s why I wrote point 5 below