Dynamics 365 Business Central AS 101 Lesson 22: Dealing with Zeros
Every numbers person needs to deal with zeros, and the most frequent thing we all do with zeros is to get them out of the way. Dynamics 365 Business Central gives us very few ways to deal with this, but frequently the best way to get rid of zeros in account schedules is to do that task in Excel. Here are a few tips and tricks to help.
If you print account schedules directly out of the software, you’ve got some great options. (But who prints anymore??)
You can check the option found under Show in the row setup for If any column not zero. If this option is checked, the row will be suppressed if all columns have a zero value. Just like the other options on the show menu, this only affects printed account schedules and makes no change directly on-screen in NAV, either in the design view or in the overview.
There is an option in the column layout under Show where you can choose Never, but this isn’t directly applicable to the behavior of zeros. It is mighty handy if you want to hide a column whose only function is to perform a calculation that you don’t want shown.
If you are viewing account schedules on-screen, there is no way to hide zero values.
If you’re looking in the design view or the overview, you will see rows that contain zero or no values and you can’t hide them.
If you are exporting account schedules to Excel, you’ve got some other options.
These take a little setup, but will get the job done. If you want to consistently filter out zero values from your account schedules using Excel, here’s one way to do it: use filtering! I’ve got a report that I produce that has 50 rows on it. I regularly filter this report by dimensions, and any tab that is generated with a dimension can have the majority of lines with zero values, but it is not the same for each dimension. Each month, here is what I do:
Copy the prior month’s report and save as the current month’s report.
Remove all Excel filtering from each tab of the new report.
Export all the tabs I need from Account Schedules in order to update the numbers.
Go back into the new report that now has new numbers and apply an Excel filter to a column using the option to Select All and then uncheck the blanks or zero value option.
Pro Tip: You’ll need to think through how you design your report in order to build in a column that would correctly indicate that a row with all zeros exists. One way is to insert a column that calculates the sum of all the other values and then format the numbers to show up as white text in Excel. That way you can then filter on those values when a zero or a blank occurs for all columns in a row in order to show only the rows that have values in them.
This posting is part of the Dynamics 365 Business Central Account Schedules 101 series. Find the entire list of lessons here.
Don’t forget to visit the Account Schedule Examples pages if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Dynamics 365 Business Central.