Using Pivot Tables to Verify Posting Group Setup in Dynamics NAV

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?

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.

Trending Posts

Stay Informed

Choose Your Preferences
First Name
*required
Last Name
*required
Email
*required
Subscription Options
Your Privacy is Guaranteed