Export Position Hierarchy from Dynamics to Visio & Power BI


A position hierarchy is a visual representation of how positions relate to each other within your organization

In this article, We will see how to export Position hierarchy.

This is application for below product.

  • Dynamics AX 2012
  • Dynamics 365 F&O
  • Dynamics 365 HR

We can export Position hierarchy with three different approach

  1. Microsoft Visio
  2. Power BI
  3. Microsoft Excel(Not included in the demo)

1.    Microsoft Visio

Lets see how we can export Position hierarchy with Microsoft Visio.

Reference link : https://docs.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/export-a-position-hierarchy-to-microsoft-visio

Prerequisite

  • We need an access to MS Visio

1. Go to Active Position form

Click on Office icon at Right top corner. Export  Positions to excel and download.

  • Open MS Visio, Click on New and search for Organization chart template.

Click on create

Select option heighted in above screen and click on Next

Select Excel file and Click on Next

Select Name and Report to dropdown.

Select Display fields

Select Shape Data fields

Select the appropriate option. Here I don’t have pictures so I have selected Don’t include picture option.

Click Next to continue

Click Finish to Wizard.

Based on your data, It will take some time to load data in to Visio.

2.    Power BI

To export Position hierarchy to Power BI, We need below entities

  1. cdm_jobposition
  2. cdm_positionworkerassignmentmap
  3. cdm_workers
  4. cdm_departments

Here we need to add relationship between above entities

We need data like this.

Position IdPosition descriptionDepartmentsTitleWorker nameReport to Position IdReport to Position workerReport to Position worker name

To create hierarchy, We need to create calculated columns as below.

Calculate Path

Path = PATH(PositionHierarchy[Name],PositionHierarchy[Report to Position worker name])

Calculate Depth

Hierarchy Depth = PATHLENGTH(PositionHierarchy[Path])

Calculate Levels

Level 1 = PATHITEM(PositionHierarchy[Path],1)

Level 2 = PATHITEM(PositionHierarchy[Path],2)

Level 3 = PATHITEM(PositionHierarchy[Path],3)

Reference link : https://simplebiinsights.com/parent-child-hierarchies-in-dax/

After creating above calculated columns, Download the hierarchy slicer from Visualizations.

Drag and Drop Level 2 and Level 3 to Level 1. It will create a hierarchy.

Select this hierarchy in Fields of Hierarchy Slicer visual.

Save the report and You will see below structure.

I hope you like this article. If you have any questions then please let me know.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: