Jet Reports for Dynamics NAV—Sign Reversing and Totaling
In my previous post, I covered the topic of using accounting periods instead of dates in Jet Reports.
In this post, I’m going to tackle another problem that often comes up with Jet, especially when regular report writers try to write financial reports for the first time.
The problem relates to sign reversing and totaling.
The Problem of Sign Reversing
To understand this problem, let’s start with a basic financial report written over the fictional Cronus company sample database.
To keep the example simple, I’m going to calculate gross profit. Here’s the report:
Simple, right? But take a closer look at two of the formulas.
Let’s start with the current MTD amount for account 42500.
Here’s the formula:
I’m using the NAV GL function to retrieve the balance for the account found in $B9, which is 42500. For starting and ending dates, I’m using names fields that I’ve setup in another tab of my worksheet (as per my previous post on accounting periods vs. dates in Jet Reports).
CYPSD stand for Current Year Period Starting Date. CYED stands for Current Year Ending Date.
If we open the function, it looks like this:
As you can see, the start date and end dates convert to real dates.
But what is cell $E9? Let’s take a look:
$E9 holds a value for reverse sign. In NAV (as in most accounting systems), credit balances are held as negative numbers. However, accountants (and others who read financial statements) don’t like seeing them as negative. So, we have to reverse sign. (Typically, we do this for revenue, liability and equity accounts.)
Note that I’ve created a column for this. It’s easier to scan a column for mistakes than look through lots of formulas.
Of course, once we reverse signs, we can’t just add everything together and get a profit. So, if we look to the gross profit line, we see that we’ve simply subtracted cost from revenue:
That looks easy. And it is for a report as simple as this. However, I’ve seen other reports with lots of total lines to add. So, you end up for a formula something like this:
=g12-g19 – g27 – g35 + g44 -g60 – c72
Indeed, I recently saw a report with about 20 of these. Fixing this kind of formula can be a nightmare. So what to do?
The trick is to total everything first and reverse the sign in the last step.
First Total, Then Reverse
First, we add another column to hold the unreversed values and change our formulas appropriately:
In the above screenshot, you see column Current MTD No Rev as well as the formula that works with the raw data. We’ll hide the column when the report is run, based on the value in G1.
The formula in the Current MTD column generates the numbers:
While column H does the reversal:
Next, we add a total to the new column. But we change it from “sum” to “subtotal.” We then replace the previous sum with a simple reversal calculation.
So, G12 now looks like this:
And G19 looks like this:
Why do we subtotal instead of sum? Because when we get to Gross Profit line, we can subtotal again. Unlike sum, subtotal ignores all previous subtotals.
Thus, as long as we know where we want to start, we don’t have to worry about intermediate steps:
As the report grows longer, we can get the total simply by starting at the top and adding all the way through. But we do have to figure out whether the total should be reversed.