Troubleshooting the Analysis by Dimensions Matrix in Dynamics NAV

A few months ago, I worked on a case where a client was having a problem with the analysis by dimensions matrix in Microsoft Dynamics NAV. It was a tricky problem (and quite hard to state in a descriptive manner), so I’ll demonstrate it using pictures.

If you are expecting an output that looks like this (the analysis by dimensions matrix shows the G/L accounts per month):

But you ended up with an analysis that looks like this (same date filters, but no details were displayed on a monthly basis):

Then this is a sign that you have to check the settings that you have applied to the analysis by dimensions matrix. There are two  methods that you can employ to resolve this concern.

Method One – Check the Date On The Analysis Card

The first checkpoint is to go back to the Analysis View Card (Administration>Application Setup>Financial Management>Dimensions>Analysis Views) and check the ‘Starting date’ data field. Compare the ‘starting date’ on the analysis view card with the date filters used in the analysis by dimensions matrix.

If the date filters used are before the starting date on the card, then Dynamics NAV would lump up all transactions that were prior to the starting date together. To illustrate this, try pressing the amount on the ‘Total Amount’ column:

There are several changes that you can make to the ‘starting date’:

• Option 1: Remove the starting date, then ‘Update’ (by pressing update button) the analysis card
• Option 2: Assign a starting date that is lower than the one defined in the date filters, then ‘Update’ the Analysis card (same process as option 1).
• Option 3: Assign a starting date that is equal to the lowest date used in the date filters, then ‘Update’ the analysis card.

Tip: Every time there are changes done on the Analysis View card (example: dimension, starting date), always remember to ‘update’ the card by pressing on the ‘Update’ button. This ensures the data is refreshed and the changes will take effect.

Method Two – Check the Matrix Options

The second checkpoint is to check the ‘Matrix Options’ fast tab of the analysis by dimensions matrix; specifically the ‘View as’ data field.  If you see that the option is ‘Balance at Date’.  You may want to change it to ‘Net change’:

You might ask: “what is the difference between the two?”

Balance at Date – Shows the balance at date to display the G/L account balance as of the last day in the period

As you can see, the last column is exactly the same as the ‘Total Amount’ column. Further investigation shows that the system is displaying the sum of the net change for each month.

Net Change – Shows the net change in the account balance for the period

If you sum up the values from columns 01/01/2015 until 05/01/2015, it yields the total displayed in ‘Total Amount’ column. Further checking would show that the values displayed are exclusively for the month.

Summary

As a summary to what has been discussed, there are two methods that you can perform if you find that the analysis by dimension matrix is not showing your desired results. The first one is to check the starting date (defined on the analysis card), and the second is to check the ‘View as’ option in the matrix options fast tab.