AX 2012 Personal BI - By Example - Customer Aging II

Yesterday, we started a series together on using PowerPivot with Microsoft Dynamics AX 2012. In the post, AX 2012 Personal BI - By Example - Customer Aging I, we went over a custom query named "CustomerAging" that we were able to connect to via OData feeds from AX 2012. In doing so we left off, with filling our PowerPivot window, with data from AX 2012.



With this we now have the ability to create a new PivotTable within our Excel 2010 workbook and use our PowerPivot data set to power the Pivot Table for reporting. We do this, by closing our PowerPivot window, and then clicking on the new PivotTable button from the PowerPivot Ribbon, as shown below.



Doing this, we should see a screen similar to the following screen shot, that has our field selections for our PivotTable to the right, as well as the working PivotTable area, within our workbook to the left.



Now that we have our PivotTable options to start working with, lets look at some data that we have, which we imported during the first part of this series. Since this is a customer aging report, we should start with the values section. This means we will want to get the Open Balance value. If I just highlight and click the 'CustAgingLine_1_OpenBalanceMst', then you will see that this automatically sums this value for me, and places it within the Pivot Table, values area as shown below.



Having this, and looking at the value, does not look to pretty really. Lets do a little face lift work on the value label, and change that. We can do this by right clicking on the Sum Value field, and the left clicking on Edit Meassure.



Doing this, you should see a form similar to the below. This is where you can set the value for the name of the Meassure as well as other options. For now, we just care about changing the name. I'm going to change it to Open Balance.



Doing this, gives us a nicely formated name for our Value that we care to report on. This could also be changed in the PowerPivot window, but for now we will work within the PivotTable for such changes.

Moving forward from here, we need some rows, and columns to report on. So first, lets get some rows to report on. Since, again, this is a Customer Aging report, let us focus on the customer account & name values. This is made up of 'CustTable_1_AccountNum' and 'DirOrganization_1_Name' from our field list. Lets place these two fields, by draging and dropping them into the row columns as seen in the below image.



Now if we look to our Excel workbook, we can see that the PivotTable itself has been updated to reflect our new Row Label fields. In looking at this, and like the change we made for Open Balance, this is just not quite what we are looking for. In order to help make this report look a little better, lets create a computed column in our PowerPivot Window, and call it Customer. We do this by going back into the PowerPivot Window, and then scrolling to the far right and click to add a column.



Now that we have our new column we need to add Dynamic Analysis eXpression (DAX) formula that will concantenate our Account Number & Customer Name, into one field for us. We can achieve doing that with the following such formula.

"=CONCATENATE(CustomerAging[CustTable_1_AccountNum],CONCATENATE("-",CustomerAging[DirOrganization_1_Name]))"

Doing this, allows us and saving we should then see our new computed column, that combined "AccountNum - Name" in our new column called customer, as seen in the following screen shot.



Now that we have our new computed column, lets close our PowerPivot window. We should then be able to remove the current row labels, scroll to the bottom of our field list, and see our new Customer field. Lets drag and drop that now into our Row Labels as seen below.



Now that we have our new computed column place, we should see this reflected in the the workbook where our PivotTable is being built out. You should see something similar to the screen shot we have listed below here.



Now we are off to a really good start, with our personal BI journey that we are taking with PowerPivot and AX 2012. It's worhty to note that, in accessing data through the application layer of AX 2012 through the use of OData feeds, representing query elements we are able to inherit all the great security and features of Dynamics AX.

Well that's all for this evenings post. I hope you all have a wonderful night, and chekc back soon as we continue our journey on personal BI tomorrow. We will continue with adding Column labels, as well as Vertical & Horitizontal Slicers! Till Then!
Follow Me @:
    

"Visit the Dynamics AX Community Page today!"