NAV AS 101 Lesson 21: Account Schedule BugsPosted: June 24, 2014
Just like any piece of software, there are some bugs in NAV account schedules, and the best way to deal with them is to know they exist. I’ll let the Microsoft folks debate whether these are a bug or not. To me, as an end-user, anything that removes a necessary feature or keeps a necessary feature from operating reliably without replacing it with something better is a bug.
Refresh Error in NAV Classic Account Schedules This bug causes the account schedule to come up blank when changing to a different account schedule. It can sometimes be partially remedied by scrolling up and down or left and right, but this sometimes only brings back some of the expected numbers. This is a refresh error that has existed for many of the Classic NAV versions. I’m really not sure how long it’s been there – I started using NAV with version 3.5 and I’ve seen this bug in that version as well as in 4.0, 5.0, and 2009 Classic. The best way to get past this one is to exit out of your account schedule and open it back up again. With the introduction of the matrix to account schedules in 2009 RTC versions, this bug (finally) went away.
Copy and Paste in 2009 RTC While the introduction of the matrix brought us some very nice things, it brought us one nasty disappointing bug – in NAV 2009 RTC versions, copy and paste cannot be used in account schedules. This has by far been the worst bug I’ve seen in NAV account schedules, and really is a huge loss of efficiency to users who have that version of NAV. I rarely get worked up about this kind of thing, but I did put a post out there explaining why this was such a big loss. There is no way to get past this bug other than to upgrade to the next version of NAV, NAV2013, where thankfully, copy and paste are returned to us.
Buttons that don’t work and filters that work but are super clunky in 2009 RTC There were a few other bugs in 2009 RTC that made this version of NAV the worst version ever for account schedule users. On the account schedules overview page, there were some mysterious buttons labeled as Next Set and Previous Set that don’t do anything. In addition, the filtering is terrible. Date filters always pick up the work date, don’t retain the date you were just using if you leave the screen and go back, and if you want to change the dates, you’ve got to type the entire date range, every time. There are no quick navigational buttons to move from one period to another. In addition, dimension filtering is difficult to get to and difficult to populate with the names of the dimensions you are using, in particular, if you use multiple analysis views. Luckily, every single of these bugs was fixed with the release of NAV2013.
To date, NAV2013 is by far the best version of NAV Account Schedules. It seems like Microsoft really got it right with a large number of capabilities related to the improved matrix, filtering, and navigation with account schedules, making it streamlined, intuitive and easy. Alas, NAV2013 is not perfect, and there are two bugs I’ve found. Unlike other versions, hopefully we won’t have to wait for a next release to see these ones fixed.
Refresh error in NAV2013 This one is a brand new and different type of refresh error than the one we saw in NAV Classic versions. When switching from an account schedule with many columns (let’s say a 12 month trended view) to an account schedule with fewer columns (let’s say an actual versus budget with variance), you’ll end up with a bunch of left over data from the 12 month view in the columns that are no longer a part of the actual versus budget view. There’s a quick and easy fix, which is to hit F5 or the brand spanking new Refresh button, and those pesky numbers will disappear. However, you’ve got to be on the watch for them and know what you’re looking at – a risk that’s not so great. As a NAV2013 user you’ll get used to using the F5/Refresh button frequently, so not a big deal, but I still don’t like any financial report showing up with numbers it shouldn’t.
Update workbook function no longer retains formatting in Excel when exporting account schedules with NAV2013 This one is a bigger deal and again, is a pretty big loss of efficiency for NAV account schedule users. Anyone who uses account schedules a lot should be using the export to Excel function all the time in order to product account schedules quickly and with consistent formatting. NAV2013 users can no longer do this and retain existing formatting. The update to Excel function still works in this version, but instead of just updating numbers in a formatted Excel worksheet, it dumps unformatted updated numbers into that worksheet, overriding the formatting you might have there. The recommended best practice is to continue to use the update worksheet function, but change one aspect of design of your Excel workbook. What you now need to do when you design your Excel workbook the first time are the following steps:
1) Use create workbook for any schedule to get an unformatted starting copy (Copy 1) of your schedule in Excel.
2) Copy this worksheet to another worksheet (Copy 2) and format it exactly like you’d like to see it.
3) Delete all the numbers out of Copy 2 and put in a link to Copy 1 that will pull all the numbers into Copy 2.
4) Hide the worksheet tab for Copy 1 so you only see the nicely formatted Copy 2.
5) When you update your Excel workbook the next time, only update the numbers to Copy 1. You will never update numbers to Copy 2. The links in Copy 2 will always pull forward from Copy 1.
I can’t say I’m especially looking forward to redesigning my 18 page financial statement package this way, but I’ll do it because now I have to. The one nice thing about this is I can get a little fancier with some of my formatting and won’t need to continue to tinker with some of the small things I do, like taking underlines out of column A and readjusting the width of column B, things I need to do universally in NAV2009 Classic because while the update workbook function keeps most of the formatting, it does override a couple of pesky things. So ultimately, this method will be faster on a monthly basis, but will take more design time and Excel comfort level, up front.
This might be a better solution for account schedules produced with many separate tabs for filtered dimensions. Imagine an Excel workbook with 20 tabs showing the same report, one for each dimension. Because we must use this new method, one way to make it more efficient will be to design a single report with the needed dimensions in each column. This report will go to the one hidden worksheet in the workbook and the 20 user facing tabs will be beautifully formatted reports that link only to the applicable column from a single report. So, for that example, this actually turns into a time saver since you don’t need to export/update each filtered dimension to a new tabbed worksheet.
One concern is that the linked formulas could become unlinked/deleted/moved, but this could be safeguarded by locking cells on the formatted worksheet to keep them from being accidentally changed. Another concern is that your account schedule could change and then the linked copy won’t show the error that is not being picked up. There are a few more things to watch for with this change in place, but there are some positives as well as negatives that go along with this bug.
This posting is part of the NAV Account Schedules 101 series. Find the entire list of lessons here.
Don’t forget to visit the Account Schedule Formulas and Account Schedule Examples pages if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Microsoft Dynamics NAV.