web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Sign Up for Ask a Community Pro Today!
Introducing our New Video Series: Community Spotlight
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Worker / employee posi...
Finance | Project Operations, Human Resources, ...
Unanswered

Worker / employee positions and hierarchy

(1) ShareShare
ReportReport
Posted on by CU29041349-0 610
Hello,
I've been tasked to provide an extract of all our employees with their associated position details. I would like to do this through a SQL script for the time being. I have a fairly simply SQL retrieving most of the information I need. The next step would be to show the report-to hierarchy somehow. Which leads me to the questions. Does an OOTB report exists? I know there's a Position hierarchy form available to drill through the hierarchy so hoping there's a companion report or form which would lay it out in a downloadable tabular format. 
I have the same question (0)
  • Hana Xue Profile Picture
    Hana Xue Microsoft Employee on at
    Hi,                          
    You can check if this link is useful.
    Organization hierarchies with standard reports (dynamics.com)
    Best Regards,
    Hana
  • Guy Terry Profile Picture
    Guy Terry 28,943 Moderator on at
    If you really want SQL to do it, try this.
     
    DECLARE @Partition bigint
SELECT @Partition  = Par.RECID FROM dbo.PARTITIONS AS Par WHERE Par.PARTITIONKEY = 'initial'

SELECT P.POSITIONID AS 'Position', PD.DESCRIPTION AS 'Description', PP.POSITIONID AS 'Reports to', PPD.DESCRIPTION AS 'Reports to Description'
FROM dbo.HCMPOSITION AS P
INNER JOIN dbo.HCMPOSITIONDETAIL AS PD ON P.PARTITION = PD.PARTITION AND P.RECID = PD.POSITION AND PD.VALIDFROM < GETDATE() AND PD.VALIDTO > GETDATE()
LEFT JOIN dbo.HCMPOSITIONHIERARCHY AS PH ON P.PARTITION = PH.PARTITION AND P.RECID = PH.POSITION AND PH.VALIDFROM < GETDATE() AND PH.VALIDTO > GETDATE()
INNER JOIN dbo.HCMPOSITION AS PP ON PH.PARTITION = PP.PARTITION AND PH.PARENTPOSITION = PP.RECID
INNER JOIN dbo.HCMPOSITIONDETAIL AS PPD ON PP.PARTITION = PPD.PARTITION AND PP.RECID = PPD.POSITION AND PPD.VALIDFROM < GETDATE() AND PPD.VALIDTO > GETDATE()
WHERE P.PARTITION = @Partition
ORDER BY P.POSITIONID ASC
     
  • Navneeth Nagrajan Profile Picture
    Navneeth Nagrajan 2,491 Super User 2025 Season 2 on at
    Hi,
     
    The SQL Script provided by GuyUK should work but to answer the second part of the question, there is a report called Positions by department report under Human Resources -> Positions section. The Positions by department report gives you the list of positions by department. 
     
    Also, there are some data entities that can be leveraged as mentioned below as an alternative to the SQL Script:
    S. No. Entity Name Target Entity Name
    1.  Position Hierarchies  HcmPositionHierarchyEntity
    2.  Position Hierarchy Type HcmPositionHierarchyTypeEntity
     
    Happy to answer questions, if any.
  • Guy Terry Profile Picture
    Guy Terry 28,943 Moderator on at
    That SQL will give you all positions, and their parent. Considering the data structure, I think you will be doing well to write sql to build the whole hierarchy for you! I suggest some knife and forking in Excel.
  • Guy Terry Profile Picture
    Guy Terry 28,943 Moderator on at
    Ah, I thought it might be a one-off exercise. I agree, this is not an excellent long term solution!
     
    I wonder if Power BI has tools to manipulate the data how you would like. I believe it has functionality to handle hierarchies; I just don't know how it works.

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

News and Announcements

Sign Up for Ask a Community Pro Today!
Introducing our New Video Series: Community Spotlight

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the December Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 627 Super User 2025 Season 2

#2
Abhilash Warrier Profile Picture

Abhilash Warrier 438 Super User 2025 Season 2

#3
Martin Dráb Profile Picture

Martin Dráb 358 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans