Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How to get the count o...
Microsoft Dynamics AX (Archived)

How to get the count of terminated Employees from Employee master in the HR module

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee 
Hi All ,
I am facing one issue. I am doing the attrition Report.  and calculating Attrition Rate. 
Attrition rate = (Number Of Attrition or Number Of Employee Left * 100)/ (Actual Employees + New Joined)/100
In that report i am giving the Valid From date filter for a particular month and i am getting the count of the employees who are all employed. Means the employees whose employee status is "Employed". But similarly the employees who left (The employees whose status is "Terminated") for these employees i am not getting the count of these employees. . Only '0' is coming for any month filteration.
Any help is appreciated. Thanks in advance. 
And this is the job code i have written 
static void attritionReport(Args args)
{
HcmWorker hcmworker, hcmworker1;
HcmEmployment hcmemployment, hcmemployment1;
ITTI_Attrition_Temp attritiontemp, attritiontemp2;
HcmWorkerStatus workerStatus = HcmWorkerStatus::Terminated;

int counter1=0, counter2=0 ;
utcdatetime now = DateTimeUtil::utcNow();
utcdatetime maxDateTime = DateTimeUtil::maxValue();
utcDateTime date1 = DateTimeUtil::newDateTime(1\7\2015, 0);
utcDateTime date2 = DateTimeUtil::newDateTime(31\7\2015, 0);
while select hcmemployment join hcmworker where hcmemployment.Worker == hcmworker.RecId {
if( hcmemployment.ValidFrom >= date1
&& hcmemployment.ValidFrom <= date2 && hcmworker.workerStatus()== HcmWorkerStatus::Employed)
{
counter1++;
attritiontemp2.ITTI_NoOfEmployees = counter1;
}
else if(hcmemployment.ValidTo <= now && hcmworker.workerStatus() == HcmWorkerStatus::Terminated && hcmemployment.RecId != 0)
{
counter2++;
attritiontemp2.NumberOfLeftEmployees = counter2;
}
}
info(strFmt("%1, %2" , counter1, counter2));
attritiontemp2.insert();
}
In the above code, and in the above date filter range total 6 employees are there. 5 are employed and 1 is Terminated. I am getting 5. But not getting Terminated employee count  Instead of 1 , zero is coming in the info.

*This post is locked for comments

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: How to get the count of terminated Employees from Employee master in the HR module

    good.

    you can, further, make your query faster by removing while select and putting select count.

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to get the count of terminated Employees from Employee master in the HR module

    Hi, Sohaib Thank you so much for your ready response. .

        In the mean time i got the correct output for my requirement.  As you said i used the simple ValidTimeState function with select statement. .Now i am getting the count of Active employees and also the count of Terminated employees for that particual Joining and Termination date (ValidFrom and ValidTo fields from HcmEmployment Table)

    static void communityattrition( Args   args)

    {

        HcmWorker                      hcmworker, hcmworker1;

        HcmWorkerAction                hcmWorkerAction;

        HcmEmployment                  hcmemployment, hcmemployment1;

        ITTI_Attrition_Temp            attritiontemp, attritiontemp2;

        HcmWorkerStatus workerStatus = HcmWorkerStatus::Terminated;

        int counter1=0, counter2=0 ;

        utcdatetime   now = DateTimeUtil::utcNow();

        utcdatetime   minDateTime = DateTimeUtil::minValue();

        utcdatetime   maxDateTime = DateTimeUtil::maxValue();

        utcDateTime date1 = DateTimeUtil::newDateTime(1\7\2015, 0);

        utcDateTime date2 = DateTimeUtil::newDateTime(31\7\2015, 0);

        while select ValidTimeState(date1, date2)  hcmEmployment where hcmemployment.ValidFrom >= date1 && hcmemployment.ValidTo >= now

          {

            counter1++;

          }

        while select  ValidTimeState(date1, date2)  hcmEmployment where hcmemployment.ValidFrom >= date1 && hcmemployment.ValidTo <= now

          {

             counter2++;

          }

        info(strFmt("%1, %2" , counter1, counter2));

    }

    This is that logic. . Happy Daxing. . :)

  • Suggested answer
    Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: How to get the count of terminated Employees from Employee master in the HR module

    I will not go into your exact scenario, due to shortage of time and other reasons such as

    1. What is your definition of Terminated employee?

    2. Might be you are checking with reference of HCMEmployment or some other factors might be into your consideration

      I will speak about basics with respect to development.

      So, as example have a look at below code.

    DirPartyPostalAddressView    postalAddressView;
    DirPartyRecId                _party;
    utcdatetime _validFrom = DateTimeUtil::minValue();
    utcdatetime _validTo = DateTimeUtil::utcNow();


   _party   = HcmWorker::findByPersonnelNumber("000670").DirPerson().RecId;

       select  validTimeState(_validFrom, _validTo) count(Recid) from postalAddressView
        where postalAddressView.Party == _party &&
              postalAddressView.ValidFrom >= _validFrom &&
              postalAddressView.ValidTo <= _validTo;
    
    info(strFmt("%1",postalAddressView.RecId));


    So, as you can see above  how I am getting count(RecId). My definition of Past records is From Now till start of minTime. Similarly you need to  work with your scenario.  in my example I am getting count of expired address' for an employee.

    if you want me to work on your exact scenario, you need to wait , unless I get some free time. Additionally I will need "Your definition of terminated employee with respect to business process"

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to get the count of terminated Employees from Employee master in the HR module

    Can you please send me the piece of logic to get the count for only Terminated employees. . Thanks in advance

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to get the count of terminated Employees from Employee master in the HR module

    Hi, Thanks for your reply.

    Now i have used that ValidTimestate function and written the code.  Still the count of terminated employees are showing zero only.

    static void communityattrition( Args   args)

    {

        HcmWorker                      hcmworker, hcmworker1;

        HcmWorkerAction                hcmWorkerAction;

        HcmEmployment                  hcmemployment, hcmemployment1;

        ITTI_Attrition_Temp            attritiontemp, attritiontemp2;

        HcmWorkerStatus workerStatus = HcmWorkerStatus::Terminated;

        int counter1=0, counter2=0 ;

        utcdatetime   now = DateTimeUtil::utcNow();

        utcdatetime   minDateTime = DateTimeUtil::minValue();

        utcdatetime   maxDateTime = DateTimeUtil::maxValue();

        utcDateTime date1 = DateTimeUtil::newDateTime(1\7\2015, 0);

        utcDateTime date2 = DateTimeUtil::newDateTime(31\7\2015, 0);

      while select  ValidTimeState(date1, date2) * from hcmEmployment

       {

         select hcmworker where hcmworker.recid ==hcmemployment.worker;

          if( hcmemployment.ValidFrom  >= date1 && hcmemployment.ValidFrom  <= date2

              && hcmworker.workerStatus()== HcmWorkerStatus::Employed)

             counter1++;

          else if(hcmemployment.ValidTo <= date2 && hcmworker.workerStatus() == HcmWorkerStatus::Terminated)

               counter2++;

       }

       info(strFmt("%1, %2" , counter1, counter2));

    }

    In the above code, if i put the debugger and checked that Terminated employee RecId's are not selecting at all .

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: How to get the count of terminated Employees from Employee master in the HR module

    So, you have posted two question, which are almost same in nature. I will answer to 1 only considering reasons for both issues seems same.

    First of all kindly use validTimeState in you select statement, which is needed for tables using time stamp. There is no need of if statement inside while. Even you don't need a while. You can count record in simple select statement.

    Kindly fix your query and get back with new results.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,622 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,354 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans