Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How to view department...
Microsoft Dynamics AX (Archived)

How to view department table in multiple inner join ?

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi everyone,

I have used the below query to display/fetch EMPLOYEE CODE, WORKER NAME AND EMAIL ADDRESS in report builder :

SELECT HCMWORKER.PERSONNELNUMBER AS CODE,DIRPARTYTABLE.NAMEALIAS ,LOGISTICSELECTRONICADDRESS.LOCATOR FROM DIRPARTYTABLE
INNER JOIN LOGISTICSELECTRONICADDRESS ON LOGISTICSELECTRONICADDRESS.RECID=DIRPARTYTABLE.PRIMARYCONTACTEMAIL
INNER JOIN HCMWORKER ON HCMWORKER.PERSON=DIRPARTYTABLE.RECID
LEFT JOIN ASSETTABLE ON ASSETTABLE.PERSONNELNUMBER=HCMWORKER.PERSONNELNUMBER

ORDER BY DIRPARTYTABLE.NAMEALIAS


Now i want to add worker department column in the below query to display the department of the worker how do i achieve ?

thank you

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to view department table in multiple inner join ?

    Thank you so much for your time in writing query : )

    After some editing your suggested query worked !

    saves my plenty of time

    thank you again

  • Verified answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: How to view department table in multiple inner join ?

    Hi,

    Please find the sql query below. As I dont have reportbuilder and propersetups for departments of a worker, couldn't test it properly.

    SELECT worker.PERSONNELNUMBER as personnellnumber,

      party.NAMEALIAS as name,

      elecAddr.LOCATOR as email,

      DirDepart.NAME as name

    from HCMWORKER as worker

    inner join DirpartyTable as party on worker.PERSON = party.RECID

    inner join LOGISTICSELECTRONICADDRESS as elecAddr

    ON elecAddr.RECID = party.PRIMARYCONTACTEMAIL

    inner join HcmPositionWorkerAssignment Assign

    on Assign.Worker = worker.RECID

    inner join HcmPositionDetail pstDetail

           on Assign.Position = pstDetail.Position

    inner join HcmPositionDuration pst

    on pst.Position = pstDetail.Position

    inner join DIRPARTYTABLE as DirDepart

           on DirDepart.RecId = pstDetail.Department

    where Assign.VALIDFROM >= getdate()

    and Assign.VALIDTO <= getdate()

    If you have access to Ax, departments names are retrieved from the following method

    \Data Dictionary\Tables\HcmWorker\Methods\getDepartments. You can check it for reference.

    Hope this helps you.

    Thanks,

    Chaitanya Golla

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to view department table in multiple inner join ?

    sorry you have not read my question carefully,please review my question i want to create a report in Report builder to show PERSONNEL NUMBER, NAME , EMAIL, AND DEPARTMENT OF A WORKER

    regards

  • Suggested answer
    Khaled Alloud Profile Picture
    Khaled Alloud 3,658 on at
    RE: How to view department table in multiple inner join ?

    Hello,

    Do you know how to reach the department from AX forms?

    From Worker window go to 'Add position assignment' and then go to the Position assigned. From there you will see the department.

    From personalize you can see the table name and you make the joins as you did above.

    Best,

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans