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!"

AX 2012 Personal BI - By Example - Customer Aging I

I hope everyone is doing well, and having a great start to a brand new week. After taking a week off from blogging, I wanted to start back with a focus around a topic I've been spending a lot of time focusing on. This topic, is around getting the most value of of Microsoft Dynamics AX 2012. To be specific, this is around Business Intellegnce and fitting AX 2012 today, and tomorrow, into the BI Semantic Model.



As the name implies, I wanted to show off the power and flexibility of AX 2012 with use of PowerPivot. You have seen me post about this in the past, with how to correctly access AX data via PowerPivot and OData Feeds.



Now if you don't have PowerPivot installed, that is the first thing we need to get you connected with. When talking in terms of PowerPivot for this purpose of this series of post, we are speaking about 'PowerPivot for Excel 2010'. You can get this as a free download from the following location.: Microsoft BI: PowerPivot Download Page. Make sure to download and install the add-in, so that we can proceed.



Building from that knowledge, and keeping in-line with the BISM focus around Personal BI, lets dive into a great example, specifically for "Customer Aging". In this first part of this series, which I will finish out by the end of this week, lets focus on getting a Customer Aging Query that we can make use of. Therefore lets have a look at the "CustomerAging Query". For your reference, you can download this query, located here.: CustomerQging Query. Please keep in mind use at your own risk, and no warranty is implied. This came as part of the demo instance's for AX 2012.



You can at this point download and import this query, and add it to your document data sources, which is located under Organization Administration > Setup > Document Management > Document Data Sources.:



In doing this, we should be able to then launch Excel, open PowerPivot window, enter your OData Service, similar to the following.: "http://localhost:8101/DynamicsAx/Services/ODataQueryService/" In doing that, we should now see the CustomerAging Query to make use of. In clicking next you should see a screen similar to the following, after importing data.



Doing this, means we have added a new query to the AOT, added as a document data source, and consumed that document data source as an OData feed. Doing so means we correctly consume AX 2012 data, from the Application Layer. Moving forward from here, we will work with the Data, create computed columns, work with vertical & horizontal slicers and finally some conditional formatting. The last thing we will cover is publishing to SharePoint and displaying on a Role Center page.

That's all for this post, but check back tomorrow as we continue this focus on Personal BI for AX 2012. Till Then!
Follow Me @:
    

"Visit the Dynamics AX Community Page today!"