This week I had to make a change to report 38 (Trial Balance By Period). I had to add a column with the balance on the end of the displayed periods. This made me realize that there is a flaw in the report if you enter a start date which will make the report not use all the columns.
For instance in a default Dynamics NAV Demo database, if you select start date 01/01/2012, you will see the report print 2 columns: 01/01/2012..31/01/2012 and 01/02/2012..31/12/9999. However, for the last column in this case, the actual net change is not calculated.
In a default demo database, you will not even notice this, because there will be no data in there for February 2012 or later.
So a simple post in the General Journal on Posting Date 01/03/2012 should give us an example. See screenshots below.
Chart of Accounts in Standard NAV:
GL Post I made:
Chart of Accounts after my post:
The actual output of Trial Balance By Period with starting date 01/01/2012:
After a bit of looking through the code, I found an easy fix for this problem.
On DataItem “G/L Account” you will find this code in the OnPreDataItem Trigger:
1 2 3 4 5 6 7 8 |
... MaxCount := AccountingPeriod2.COUNT; IF MaxCount > 13 THEN MaxCount := 13; FOR i := 2 TO MaxCount DO BEGIN AccountingPeriod.NEXT; ... |
Simple changing these lines to the following will fix the problem:
1 2 3 4 5 6 7 8 9 10 |
... MaxCount := AccountingPeriod2.COUNT; IF MaxCount >= 13 THEN //Changed Line MaxCount := 13 else MaxCount += 1; //Changed Line FOR i := 2 TO MaxCount - 1 DO BEGIN // Changed Line AccountingPeriod.NEXT; ... |
And behold the output of our report now:
The reason is easy enough.
They use an array to store the start/end date of each column, for the length of the MaxCount, However, they start at index 1.
The values are run through exact the same way, starting at index 2 (for i := 2 to MaxCount, meaning if 2 accounting periods to print, they only go through the loop 1 time…), and using start/end date of index-1
So if not using all columns, it is necessary to actually calculate 1 extra column for the value, but the start/end date on the last column is useless, since it is never used…
I just posted the issue on Microsoft Connect as well.
Here is the link:
http://connect.microsoft.com/dynamicssuggestions/feedback/details/747586/trial-balance-by-period-bug
Nice one !
Well, MS Connect was not the right place to log the problem as it appears…
Pingback: Trial Balance By Period Bug | Pardaan.com
Hi,
It is nice finding and solution.
On seeing images in your blog, I think you have executed Forms and that means, it\’s not a NAV 7 Beta Release.
I think, it is working fine in Beta Release.
Thanks for sharing.
Hi,
I did indeed test it in forms. However, the values are stored inside arrays. The code for creating those arrays is still the same in NAV 2013. So the problem would also still exist there and on RDLC reports.
Regards,
Magno