Finance | Project Operations, Human Resources, ...

Unanswered

Worker / employee positions and hierarchy

(1) Share

Report

Posted on by CU29041349-0

604

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)

All responses (5)

Answers (0)

Hana Xue Microsoft Employee on at

Like (1)

Report

Hi,
You can check if this link is useful.
Organization hierarchies with standard reports (dynamics.com)
Best Regards,
Hana

Was this reply helpful? Yes No
Guy Terry 28,924 Moderator on at

Like (0)

Report
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

Was this reply helpful? Yes No

Navneeth Nagrajan

2,438 Super User 2025 Season 2 on at

Like (0)

Report

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.

Was this reply helpful? Yes No

Guy Terry 28,924 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Guy Terry 28,924 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No

Join the conversation

Community site session details

Worker / employee positions and hierarchy

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

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

Featured topics

Product updates

Community site session details

Worker / employee positions and hierarchy

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

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

Featured topics

Product updates