AX 2012 Personal BI - By Example - Customer Aging III

Well I hope everyone is having a great start to a brand new week! I wanted to write today to continue our focus on the PowerPivot Example for personal BI with AX 2012 - CustomerAging III. For those that are just now getting into this series, the following are links to the previous two post for this topic.

• AX 2012 Personal BI - By Example - Customer Aging I
• AX 2012 Personal BI - By Example - Customer Aging II

Last we left off, we had placed the Open Balance in the values area, as well as having created a computed column that concatenated both the AccountNum - Name, to give us our Customer column. We placed this in the row labels, and we have now what you see in the screen shot below.



Now lets continue this effort, by adding a column label, that will give us our buckets of aging, or aging intervals. In order to do that, we will need to identify from the PowerPivot query, what field best fits this need. Since I know this query well, I know that the field.: StatRepIntervalLine_1_Heading is what we are looking for.



Now that we have placed this as our Column Label, lets take a look at the report area, and see what our data looks like with the change.



As we can see from the above image, we now have our aging category, or buckets - that being made up of the StatRepIntervalLine datasource from the query within AX 2012. This further split up the Customer account information, so we have better insight into what our customer aging really looks like.

Now that we have our aging intervals, lets continue by adding both hortizonal and vertical slicers. These slicers are used to slice the displayed data in our report, so that we can get a dynamic glimpse by the values in which we choose to slice by. This helps make the report very dynamic and interactive within the scope of the workbook itself. Our targets for these slicers are "Customer Group" or CustGroup_1_Name for the vertical slicer, and "Aging Category" or StatRepIntervalLine_1_Heading for the horizontal slicer selection, as seen below.



Like the Value label before, lets change the name of these two slicer columns so they help make the report have a friendly consumption experience. Doing so, should yield an outcome, similar to what you see below.



Now that we have our slicers, and can look at our Customer Aging data in several different ways, let continue to finish out this report, by adding some conditional formatting. With this formatting, the idea is to take and place some visual cue to the data itself. This conditional formatting, as the name implies, will change based on the conditions we set forth.

This Conditional formatting can be added to any Excel workbook, and you launch the setup of such formatting from the Home ribbon within Excel 2010. Below is a screen shot of where to launch this from.



With this, the idea is to give some sort of data based formatting, that gives a nice visual cue to the report. It helps the numbers stand out, so that we can further have a truly refined Customer Aging report, from AX 2012. In adding the conditional formatting, you should end up with an output similar to that we have below here.



Now that we have a nice report, we have basically finished our first series on Personal BI for AX 2012 with PowerPivot. Notice that we actually left the scope of AX 2012 in the first part of this series. Since then we have been working 100% in the scope of PowerPivot & Excel 2010. The Query, when we imported the data, created a snapshot of the data and actually created a cube for us.

This data lives within the workbook, which can support up to 4 GB of size for imported data. The next steps in this would be to take this report and publish it to SharePoint, that would start the next phase in the BISM, which is a focus on Team Based BI, or collarbative BI.

We will pick back up on this series doing just that, and adding this as a WebPart, so we can place on AX 2012 role center pages. We will see how no development is needed, to publish this new personal BI artifact, the excel workbook, to SharePoint. Further we will see how little effort it takes to consume this workbook as a webpart and place it on an AX 2012 Role Center page.

Well I hope everyone has a great week, and check back soon as more to come. Till Next Time!
Follow Me @:
    

"Visit the Dynamics AX Community Page today!"