Create a PowerPivot BI Application for AX Project Postings (Update)

This is an update on the first posting how to create a PowerPivot Application for Dynamics AX. These are the changes and updates:

  • Excel 2013
  • XML Data Feeds instead of SQL
  • Timeline Filter instead of calculated fields

    Create and Publish Data Access Services

    You can publish Dynamics AX queries as open data feed. In contrast to direct SQL access it gives you more control on what is published because the builtin Dynamics AX security layer is used. Furthermore the data is delivered as XML and can easily be processed by other applications  Create three queries using these datasources and fields:

    Datasource            
    ProjTable ProjId Name        
    ProjJournalTrans ProjId CategoryId Txt Qty Transdate Worker
    HCMWoker join
    DirPerson
    Person RecId from HCMWorker Name      

HcmWorker join DirPerson

Go to Organization Administration > Setup > Document Management > Document data sources. For each of the queries create a new entry using Module “Project” and Type “Query Reference”. Choose your query from the dropdown and activate them.

Document Data Sources

You can check your services by opening the ODataService URL in your Browser. Depending on the name of the Server and Ports it might look like this http://localhost:8101/DynamicsAx/Services/ODataQueryService. You should see three available data services.

ODataFeed collections

You can load the data by adding /COLLECTION_NAME to the URL. For example http://localhost:8101/DynamicsAx/Services/ODataQueryService/ERPProjTable to view the data from the ERPProjTable collection based on the ERPProjTable query.

Projtable ODataFeed

Build the Data Model

PowerPivot and PowerView reports are builtin with Excel 2013. However, these addins need to be activated manually. Go to File > Options > Addins > Dropdown COM Addins. Activate PowerPivot and PowerView.

Active PowerPivot and PowerView

Go to PowerPivot Tab > Manage > Get External Data From Data Service. In the wizard copy the data service URL (without collection name) and click next. Select the data feed collections and finish the import

Import Dynamics AX ODataFeed in Excel

Switch to the Diagram View using the small button on the right bottom. Create the relations between the tables by drag and dropping the related fields. ProjTable.ProjId on ProjJournalTrans.ProjId and ProjJournalTrans.Worker on HcmWorker.RecId

PowerPivot 2013 Relations

Switch back to Excel. From the PowerPivot tab, select Calculated Fields and create 2 new calculated fields. One for the Qty sum and another sum of postings on project Holiday and Illness.

  • sum(ERPProjJournalTrans[ProjJournalTrans_Qty])
  • CALCULATE(sum(ERPProjJournalTrans[ProjJournalTrans_Qty]);
    ERPProjTable[ProjTable_ProjId] = "50002" || ERPProjTable[ProjTable_ProjId] = "50003")

 image

Create a KPI to visualize Absence vs. Productive

In the PowerPivot Tab, create a new KPI. Use the C_Absence as base value and the C_Total as Target value. Change the color scheme to Gree-Yellow-Red and set the sliders to 5% and 10%.

image

Go to Insert Tab, from the Tables Group select PowerPivot Table. In the Dialog select “External Datasource”. Switch to Tables and use Workbook Data Model.

image

Select the ERPWorker and drag the DirPers_Name field in the rows. Select the ERPProjJournalTrans and drag the C_Total calculated field in the Values box. Expand the C_Absence KPI. Drag the Value(C_Absence) and the Status in the values box.

image

Go to the Analyze Tab (only visible if the pivot table is selected), from the group Filter, create a new Timeline Filter and select the ProjJournalTrans_TransDate field. Place the Filter above the table (or wherever your want)

image

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
=CALCULATE(sum([QTY]);PROJJOURNALTRANS[PROJID] = "50002" || PROJJOURNALTRANS[PROJID] = "50003") .

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

How to create a custom ribbon Addin for Excel 2010

Creating a custom ribbon addin for Excel can be done in a few steps

  1. Develop your VBA code in a new Excel sheet, save it as Excel addin .xlam
  2. Open the addin with Custom UI Editor to create Buttons, Groups, etc. and link it with your VBA code
  3. Add the addin directory to the secure folders in excel
  4. Configure Excel to load your addin at startup

      Here are the required steps in detail

    Start Excel and develop your VBA code as you did before. If you do not see a development tab in excel, activate it via the options menu. File > Options > Ribbon > On the right side check “Development Tools”

    Activate the VBA Development Tab in Excel 2010
    You need to create an event handler to make your functions callable from the UI. Here is an example for a function and an event handler. The event handler foo_eventhandler calls the function foo which displays a message box with “Hello World from Excel”.

Sub foo_eventhandler(control As IRibbonControl)
    foo
End Sub

Sub foo()
    MsgBox ("Hello World from Excel")
End Sub

Save your Excel as Excel Addin (.xlam) to your C:\Users\YOURNAME\AppData\Roaming\Microsoft\Addins directory. You can save it wherever you want, but the predefined addins directory might be good idea.

Yet the addin does not have any UI elements. You have to define these elements by yourself. Download and install the Custom UI Editor from http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/07/7293.aspx . Start the editor and open your previous saved .xlam file. The editor shows a blank text field. From the Menu > Insert > Sample XML choose Excel Custom Tab. Use the onAction property at the button to call an eventhandler. Here is an example code to call the foo_eventhandler

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
        <tabs>
            <tab id="customTab" label="MyTab" insertAfterMso="TabHome">
                <group id="customGroup" label="MyGroup">
                    <button id="customButton1" label="Say Hello" size="large"
                     onAction="foo_eventhandler" imageMso="HappyFace" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

Start Excel from the program menu. Go to File > Options > Security Center > Settings > Secure Locations > Add Location, and add your addins directory e.g. C:\Users\YOURNAME\AppData\Roaming\Microsoft\Addins as secure directory to load stuff from. Close Excel.

Add the Addins directory to the secure locations in Excel 2010

When you open the .xlam file using Excel, you can already use your addin. However, it is not available when you start Excel from the programs menu. To load the addin when excel starts, go to the development tab  > Add-Ins and check your addin. (The form displays addins from the Appdata\Roaming\Microsoft\Addins directory)

Load your Addin when Excel starts

Now the Addin is loaded when Excel starts. Whenever you change something and the Addin does not appear anymore, go and check your xml UI code. Make sure that all IDs are unique and do not have blanks
(e.g. id=”My Button2” is a bad idea)

Custom Hello World Addin for Excel 2010

More Icons

There are many icons available you can use for your addins. For example download the icon gallery addin from http://www.accessribbon.de/index.php?Downloads:24 . To change the icon on button modify the imageMso property in your xml file.

<button id="customButton1" label="Say Hello" size="large"
 onAction="foo_eventhandler" imageMso="HappyFace" />

Have fun!

Many icons in Excel 2010