web
You’re offline. This is a read only version of the page.
close
Skip to main content
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 596
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
    Worker / employee positions and hierarchy
    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,911 Moderator on at
    Worker / employee positions and hierarchy
    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,191 Super User 2025 Season 2 on at
    Worker / employee positions and hierarchy
    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,911 Moderator on at
    Worker / employee positions and hierarchy
    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,911 Moderator on at
    Worker / employee positions and hierarchy
    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

Quick Links

Responsible AI policies

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

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

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

#1
CA Neeraj Kumar Profile Picture

CA Neeraj Kumar 783

#2
André Arnaud de Calavon Profile Picture

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

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 519 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans