Using Pivot Tables to Verify Posting Group Setup in Dynamics NAV
In the early stages of a Dynamics NAV implementation, it is essential to verify your Posting Group Setup through testing.
Excel Pivot Tables are ideal for this analysis.
We will use a Dynamics NAV Finished Production Order to illustrate this concept.
What we want to find out is: Did postings go to the correct General Ledger Accounts?
Figure 1 – View of the Dynamics NAV Finished Production Order
We find the Item Ledger Entries associated to this Finished Production Order:
Figure 2 – View the Item Ledger Entries associated to this Finished Production Order
We then use the Dynamics NAV Navigation tool to find all of the General Ledger Entries associated with this Production Order. To make sure we have all of the entries, always remove the date filter and select “Find”. The reason for this is that the transactions for a Production Order may have taken place over a period of many days:
Figure 3 – Use the Dynamics NAV Navigation tool to find the General Ledger Entries associated with this Production Order. Be sure to remove the date filter to find all the entries.
Then “Show” the General Ledger Entries:
Figure 4 – “Show” the General Ledger Entries
This gives us the list of General Ledger Entries associated with this Production Order, which we copy to Excel:
Figure 5 – Copy the list of General Ledger Entries associated with this Production Order to Excel
In Excel, we highlight all of the Columns and Rows that we want to convert to a Pivot Table:
Figure 6 – In Excel, highlight the Columns and Rows to convert to a pivot table
We then select “Insert/Pivot Table” which brings up this Options Dialog Window:
Figure 7 – Select “Insert/Pivot Table” which opens the Pivot Table Options Dialog Window
We select “OK,” which brings me to this window, where we drag down G/L Account No. and G/L Account Name into the “Row Labels,” and we drag down Amount into the “Sum of the Values”:
Figure 8 –Populate the fields to report: G/L Account No. and G/L Account Name into the “Row Labels,” and Amount into the “Sum of the Values”
This then populates the Pivot Table with a summary of the General Ledger Entries for this Finished Production Order:
Figure 9 – The Pivot Table is populated with a summary of the General Ledger Entries for this Finished Production Order
From this analysis, it is clear that the Posting Setups are correct.
For more information on this or other Microsoft Dynamics NAV topics, please contact ArcherPoint.
Read more “How To” blogs from ArcherPoint for practical advice on using Microsoft Dynamics NAV.