I recently had a partner/customer that came to me with a few things they needed to accomplish in AX 2012 when it came to reporting. They were running an earlier version of AX 2012 very close to RTM and were trying to make an upgrade decision. They were going to soon be opening another plant in North America and it was very important to them to be able to use dimensions and site to report by site in AX 2012.
Requirements from the customer:
1. To be able to have full reporting capability by site
2. To make sure that the site was always included on each transaction to ensure full reporting by site.
3. Be able to analyze the reason for the same financial dimension for site not being used on each side of a transaction.
4. To be able to view a full trial balance by site.
Below is a list of the setup that must be done in AX 2012 to be able to report by site and accomplish the above requirements.
Please Note: There are other setups that would need to be done that are standard to make 2012 function correctly that are not discussed in this article.
1. Create sites
2. Create business units
3. Create a financial dimension with business unit as the backing entity
4. Link and lock the financial dimension to the site using the Dimension link form
5. Setup inter-unit accounting for the Business Unit financial dimension
6. Setup Account structures to always require Business unit be a required dimension on every transaction
7. Setup payment journals by site for A/P and A/R
8. View transactions in the trial balance
9. Build Management Reporter reports by site
Solutions for each requirement:
1. Go to IWM—>Setup—>Inventory breakdown—>Sites. From this form you can create all of the sites needed within the legal entity. Generally sites will be separate geographic areas of doing business within a single company. Such as separate production plants or something of this nature. Create new sites using the new button and fill in the information on the various tabs as required.
2. Go to General ledger—>Setup—>Organization—>Business units. The business units created here should be named the same or very similar to the names of the sites that were created in the previous step. Note that other financial dimensions could be used other than Business unit, but Business unit is the dimension that I have seen used most often for the purpose of linking to site for reporting by site. Create new Business units using the new button and fill in the information on the various tabs as required.
3. Go to General ledger—>Setup—>Financial dimensions—>Financial dimensions. From this form you can create a financial dimension for Business unit and choose Business unit as the backing entity in the “use values from” field. Create new Financial dimensions using the new button and fill in the information on the various tabs as required.
4. Reporting by site is most easily accomplished using a financial dimension that is equal to the values of the sites and linking them using the Dimension link functionality in the IWM module –>Setup —>Posting. The financial dimension I have seen used most often for this is Business unit. Business unit is then linked to site using the Dimension link form mentioned previously. When deciding whether to link and/or lock the dimension the explanation of the form functions in the form will guide you. I would suggest that during the initial implementation of Dynamics AX that this feature is set to locked if the feature is used.
Having this feature setup this way will cause the financial dimension that is linked to the site be carried into every transaction where the site is specified on a source document such as PO’s and SO’s just to name a couple.
5. Go to General ledger—>Setup—>Ledger. In the field for balancing financial dimension choose the Business unit financial dimension.
The requirement that drives this setup is that the customer needs to know when the Business unit financial dimensions on each side of the transaction are not the same. They wanted a way to tell if there was one business unit used on the account side of the transaction and another used on the offset account side of the transaction. This feature causes extra automatic postings to be made on the voucher to accounts setup in the “account for automatic transactions” form. This will allow the customer the ability to do analysis on the inter-unit DR/CR accounts and drill into the transactions posted there. Being able to drill into these transactions would allow the customer to see when a inter-site transaction occurred, and allow them to easily drill back to that transaction so it can be investigated and reclassified. It would be investigated and reclassified because for this particular customer anytime an inter-unit DR/CR entry was made it would have been because of a user error in assigning the Business unit financial dimension to the transaction.
According to the customer there should not be many transactions where the site is not the same on both sides of the transaction. Most of the time this happening would be an error, and in very few transactions the extra entries to inter-unit DR/CR would be caused by an actual inter-site transaction.
Note: Using the balancing financial dimension function, for this purpose, would not be recommended if there are going to be regular inter-site transactions. This would produce a lot of transactions that would have these additional automatic entries. The customer in this case was thinking there would only be 10-15 of these inter-site postings to investigate each month due to error.
Below is an example of the setup of the accounts for the automatic transactions form for interunit:
General ledger—>Setup—>Posting—>Accounts for automatic transactions
6. The next step in the process of setting up reporting by site is to setup the account structures to properly require the Business unit financial dimension be required on all transactions. When trying to report fully by site in AX 2012 there are two things you will need to ensure in the account structures. The screenshot below is a good example of how an account structure must be setup to report by site. Please note every account structure in the left column would have to be setup this way to ensure no GL accounts were missed for the Business unit (site) requirement.
1. Every account structure created has the site dimension, in this case Business unit, as the first dimension in the account structure.
2. In every account structure the Business unit can NOT ever be allowed to have blanks. If blanks are allowed this defeats the whole purpose of reporting every transaction by site as site may not be required and could be left blank by a user posting a transaction.
General ledger—>Setup—>Chart of accounts—>Configure account structures
7. The next step in this process is to setup the payment journal names for each site. This was a specific question that came up with this partner/customer because they found that the dimensions that were applied to the invoice at the time of posting do not get pulled into the payment journal via the payment proposal. This is by-design in the Dynamics AX product as it would cause a lot of dimensions to need to be changed on payment lines if there is a centralized payment scenario. Also, it is good to note that if you are using centralized payments you may not want to have separate payment journals as are being setup in the step. This functionality should be tested and evaluated on an individual implementation basis.
Below is a possible setup that could be used on the journal name. Please note that most of these settings will be specific to the needs of the implementation, but for the purposes of this article please note the setup of the Business unit financial dimension. Also note the descriptive naming of the journal.
General ledger—>Setup—>Journals—>Journal names
Please note that this setup would have to be repeated for each site making sure that the appropriate dimension is assigned to the Business unit financial dimension field on each journal name. This would be needed on both the AR and AP payment journals to ensure all invoice payments are capturing the correct Business unit financial dimension.
Below are examples of how the dimensions default into the payment journals once the setup is complete.
You can click on the financial dimension button on the top of the form to check the dimensions that have been applied to each line in the payment journal. Please note the Business unit that is applied and locked in this journal.
8. The next step in the process is to view your transactions by site in the Trial balance inquiry. First you will have to rebuild the balance for the Main account + Business unit financial dimension set. Navigate to General ledger—>Setup—>Financial dimensions—>Financial dimension sets. Find the dimension set that you have previously created for Main account + Business unit and press the “Rebuild balances” button at the top of the form. Press ok on the next form that pops up. Once this is completed you can close this form.
Note: There are some basic setups that I have skipped in this article to spare length such as Chart of accounts, Main accounts, complete building of Account structures, and Financial dimensions sets. Directions for the setup of these items can be found in standard Microsoft documentation on PartnerSource and CustomerSource.
Now that this step is complete you can proceed to the Trial balance inquiry located at the path General ledger—>Common—>Trial balance. Once you have the Trial balance list page open change the Financial dimension set drop down field to Main Account + Business unit and you will then be able to see the balances of each account by business unit. Notice that some of the account numbers will repeat with a different business unit this allows for easy comparison of expenses, revenue, etc. by site (business unit).
You can easily drill into the transactions behind each account by using the posted button, and also the list page can be exported to Excel easily using the Export to Excel button.
9. The last step in this process which I will not address in depth is users can use Management Reporter to create in-depth financial statements by site that leverage the Business unit financial dimension which was linked to the site. Using Management Reported to create these reports will allow users to put the full power of AX 2012 reporting to use.
I hope this helps anyone out there who had questions about how to configure Dynamics AX 2012 so that they could effectively report their financials by individual site. This solution ultimately was suggested to the customer for reporting by site, but they would have to upgrade to AX 2012 R2 to be able to utilize the inter-unit balancing financial dimension functionality. This was the answer to the original upgrade question posed to me by the partner and customer.
As always please test any solution that you are implementing thoroughly to make sure it is meeting the requirements of your customer. This is always very important as all customers will have slightly different requirements that require adaptions within Dynamics AX 2012. That being said this article should get you started on a solution for reporting by site.
- Aaron Vandiver