Problem: ABC Manufacturing has implemented AX 2012 R2. They are looking to see quantities sold, quantities produced, quantity of hours worked (and any material quantity variance they wish, for that matter) to be presented with/next to the financial data in the reports. For example, ABC Manufacturing is looking to have quantities of widgets produced with a report that displays the actual cost incurred by the production department-in other words, all ABC Manufacturing is asking is to populate the General Ledger posting along with the actual transaction.
Before we get started talking about how this works in AX 2012, let’s be clear-if you’re looking for a step-by-step recipe on displaying material quantity variance with financial data, you’re in the wrong place. Understanding how AX 2012 works conceptually is half the battle in getting it to function the way you want it to. So lets go over how you can make this happen.
How Custom Reporting Works in Microsoft Dynamics AX
If you’ve been looking for a little checkbox or switch to make material quantity variance, or any quantities to show up in financial reports, then save yourself the time and stop reading now. One answer we can give away is that the ability to display quantities on financial reports is not readily available in AX 2012. These customizations have been done before but with the following:
- Custom code, X++
- Doing it through Excel reporter
- An add-on ISV solution
Dynamics AX Development with Custom Code
Depending on which Microsoft Partner implemented AX 2012 for you, the development and consulting resources on their side will be responsible for implementing a custom code solution. Getting material quantity variance or quantities to display in the financial reports takes programming expertise because the quantity that “ABC Manufacturing” is wanting to acquire is not readily available in the tables directly related to the financial reporting tool. A Dynamics AX developer will need to use X++ or C# on the back end to acquire the quantities desired through a related field.
However, it must be noted that there are still some limitations to the quantities that can be displayed, even with custom code. For example, if you are looking to display a quantity variance of “hours of labor worked in a day” with an entity that displays financial data in a monthly format, it will not be able to drill down to that specificity. You should consult your Microsoft Dynamics Gold ERP Partner before pursuing this to ensure that you’re organizing information according to this functionality.
AX 2012 Reporting and Excel Reporter
In order to get materials quantity variance or quantities information to show up in Excel with the desired financial data, you’ll first need to make sure you have the Microsoft Dynamics Excel add-on. There are several ways to organize the information you are looking for via the Excel add-on. Half the battle is figuring out what information you’re looking to compile and which tables you’re going to pull it from. There are several Excel reporting tutorials that you can sift through on various forums, such as AXUG and technet. As aforementioned, the biggest challenge is conceptually determining which information you need for the report.
Add-on Reporting Solution
For companies that do not have either development capabilities on staff or development services offered through their Microsoft Gold ERP Partner, there are always add-on solutions (different from the add-on Excel module for AX) that can satisfy your unique reporting needs.
