Skip to content

Using Microsoft Excel Pivot Tables to Verify Posting Group Setup and Analysis in Microsoft Dynamics NAV

ArcherPoint How-To Blog: Step-by-step instructions on how to perform specific tasks in Microsoft 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?

View of the Dynamics NAV Finished Production Order

Figure 1 – View of the Dynamics NAV Finished Production Order

We find the Item Ledger Entries associated to this Finished Production Order:

View 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:

Use the Dynamics NAV Navigation tool to find the General Ledger Entries associated with this Production Order

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:

“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:

Copy the list of General Ledger Entries associated with this Production Order 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:

In Excel, highlight the Columns and Rows to convert

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:

Select “Insert/Pivot Table” which opens the Pivot Table 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”:

Populate the fields to report

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:

The Pivot Table is populated 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.

Blog Tags: 
Read ArcherPoint's Blog Follow us on Twitter Follow us on Facebook Follow us on LinkedIn Link to our RSS feed Join us on Google+ Watch us on YouTube
Get Help Now