Create a PowerPivot BI Application for AX Project Postings

PowerPivot is a great extension for Excel to realize BI applications in very short. For Excel 2010 download and install the free PowerPivot extension. PowerPivot ist built in with Excel 2013. To activate PowerPivot go to File > Options > Addins > Choose COM Addins > activate PowerPivot.

PowerPivot Tab in Excel 2010

Load Data

Open PowerPivot Window > External Data > SQL Server. Provide Credentials to access Dynamics AX database. Select PROJTABLE, PROJJOURNALTRANS, HCMWORKER and DIRPARTYTABLE. For each table open the preview and select the following fields.

ProjTable ProjId Name DataAreaId        
ProjJournalTrans ProjId CategoryId Txt Qty Transdate Worker DataAreaId
HcmWorker Person RecId          
DirPartytable Name RecId          

In the import wizard set a filter on the DataAreaId for PROJTABLE and PROJJOURNALTRANS on CEU

PowerPivot Data Import

After the tables have been imported, create relations between these tables. Back in the PowerPivot window > Relations > Manage Relations create these relations.

  • ProjJournalTrans[PROJID] to ProjTable[PROJID]
  • ProjJournaltrans[WORKER] to HcmWorker[RECID]
  • HcmWorker[PERSON] to DirPartytable[RECID]
    Data Model in PowerPivot
    In the PowerPivot window select PROJJOURNALTRANS table and on the right side after the last column select the new empty column. Write =YEAR(‘PROJJOURNALTRANS’[TRANSDATE]) in the formula text field. Rename the new calculated field to YEAR. Add a second calculated column =MONTH(‘PROJJOURNALTRANS’[TRANSDATE]) and rename to MONTH

Calculated Field in PowerPivot

Create the first pivot table

Close the PowerPivot windows and return to Excel. From the PowerPivot tab create new pivot table. Place the PROJJOURNALTRANS[QTY] field in the right lower Values box. Place PROJTABLE[NAME] and PROJJOURNALTRANS[CATEGORYID] in the rows box. Place PROJTABLE[NAME] and PROJJOURNALTRANS[CATEGORYID] in the vertical slicer box. Place PROJJOURNALTRANS[YEAR] and PROJJOURNALTRANS[MONTH] in the columns box and horizontal slicer.

Pivot Table based on Dynamics AX Data

Create charts

From the PowerPivot tab open the PowerPivot table dropdown, select 2 charts vertical and place them in a new sheet. For both charts place the PROJJOURNALTRANS[QTY] in the lower right values box. For the first diagram place the PROJTABLE[NAME] field in the columns box. In the second diagram place the DIRPARTYTABLE[NAME] in the rows box. Now there are two diagrams, postings per employee and postings per project. Place PROJTABLE[NAME], PROJJOURNALTRANS[CATEGORYID] and DIRPARTYTABLE[NAME] in the vertical slicer. Place PROJJOURNALTRANS[YEAR] and PROJJOURNALTRANS[MONTH] in the horizontal slicer.

Dynamics AX Project Postings per Project and per Employee

Connect Slicer to other sheets

Select the YEAR slicer, from the context menu open the pivot table connections. Connect the slicer to the other pivot table created earlier. By connecting the slicer a selection made on one sheet also effects the other sheet.

PowerPivot Slicer

Create Measures and define a KPI

From the PowerPivot tab, PowerPivot table dropdown create a new simple pivot table on a new sheet. Place the DIRPARTYTABLE[NAME] field in the rows box. Place the PROJJOURNALTRANS[YEAR] in the horizontal slicer On the fieldlist select the PROJJOURNALTRANS table and create a new measure from the PowerPivot tab. Call the measure M_TOTAL and use the formula  =sum(PROJJOURNALTRANS[QTY])

PowerPivot Measure posted Qty

Create a second measure on the PROJJOURNALTRANS table called M_ABSENCE. This will be the sum of all postings made on project HOLIDAY (PROJID 50002) and ILLNESS (PROJID 50003). Use the formula

PowerPivot Measure posted Qty on Holiday or Illness

On the fieldlist, select the measure M_ABSENCE in the PROJJOURNALTRANS table. From the PowerPivot tab, create a new Key Performance Indicator (KPI) based on the M_ABSENCE Measure. Select the M_TOTAL as target. Choose green > yellow > red color schema. Define green to be from 0 – 5 percent, yellow from 5 to 10 percent and red to all above.

PowerPivot KPI

The pivot table with KPI should look like this

PowerPivot table with KPI

About erpcoder
Azure Cloud Architect and Dynamics 365 enthusiast working in Research & Development for InsideAx

2 Responses to Create a PowerPivot BI Application for AX Project Postings

  1. Pingback: Create a PowerPivot BI Application for AX Project Postings (Update) | ErpCoder

  2. Pingback: Create a Power BI Dashboard for Dynamics AX 2012 Sales | ErpCoder

Leave a Reply

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

You are commenting using your 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

%d bloggers like this: