Jet Reports for Dynamics NAV—Using Accounting Periods Instead of Dates
It’s pretty easy to get basic financial statements out of Jet Reports. And to help even further, Jet has many videos available that explain its terminology and functions to new users. But sometimes, you just want to solve a particular reporting challenge without digging into all the details.
Therefore, I’m putting together a series of posts with a few simple tips that make Jet Reports easier to work with.
So far, I’m planning to write these posts:
- How to deal with 4-4-5 calendars
- Sign reversal and totaling
- Creating one report to show summary and detail.
- Totaling accounts and Jet (or how to compensate for a poorly organized chart of accounts)
This isn’t an Excel blog, so I’m not going to explain all the Excel formulas. Therefore, to understand these tips, you need to understand the following Excel and Jet concepts:
- Named Cells
Using Accounting Periods Instead of Dates With a Regular Fiscal Year Calendar
The fundamental financial report function in Jet is GL. And like all the analysis pages within NAV, it’s driven by dates, not accounting periods. This gives you a lot of flexibility. But it’s also different from pretty much every other ERP financial reporting system I’ve used.
And it can be especially challenging when you have a 4-4-5 or other non-monthly calendar. It’s easy enough to know that January ended last year on the 31st, as it did this year. But did the period end on the 22nd, 23rd, or 24th?
To figure this out, let’s start with a basic financial statement I built over fictional Cronus data. (Cronus doesn’t have a 4-4-5 calendar, but it provides a starting point. The second example will only work if you have a 4-4-5 calendar of your own or you set one up.)
Here’s a snapshot of the report output:
Let’s look at the formula in cell G7:
As you can see, we’ve asked Jet to return the balance of account 41300 from 1/1/2018 to 1/31/2018. I’ve used named cells for the starting and ending dates (which makes writing the report easier).
Now, if you’re operating on a calendar year, figuring out the start and end dates is fairly obvious. For example, once users enter a year and period, you can calculate the appropriate starting and ending dates for current year and last year.
In the screenshot below, I’ve put in some simple formulas that give you most of the dates you need for basic financial reports. (I’ve included the names I typically use.)
But what if you use a 4-4-5 calendar? Simple Excel formulas won’t do the trick. And trying to remember when each period closed last year (or previous years) isn’t something you want to do.
Fortunately, there’s a way to fix that problem with a solution that works entirely in Excel.
(since I think in SQL, I originally created a solution using SQL which is at the bottom of the post ).
Using Accounting Periods Instead of Dates With a 4-4-5 Calendar
To find the NAV period starting dates by year and period, we first need to use Jet NL and NF functions to get our calendar information.
On a new sheet in the workbook, we create an NF query for all calendar information:
Now we create the appropriate NL functions to return the data.
We create one NF function for the period name:
And one for the starting date:
And here’s what the page looks like:
When I run the report over my database, I get the following information back:
Note that this report only returns helpful data if you have a 4-4-5 calendar. While I’m happy to give you the Excel spreadsheet with these formulas, don’t bother if you have a regular fiscal year calendar setup.
Create Periods and Years Based on Date
So, we have the dates. But we need periods and years to make this useful.
Eventually, we want each period-ending date to map to a year-period combination (e.g. 200601 for the first period in fiscal 2006). We could accomplish this by changing the names in NAV. But let’s assume we don’t have that authority.
So, first, we ask users for the starting fiscal year that matches the first period returned in the report. We name it StartFY:
Second, we figure out the year and period based upon the row returned.
Here are the two formulas:
- For the period:
- For the year:
Next, we combine the two to get a year-period combination.
And here’s what the output looks like:
(This isn’t an Excel blog, so I won’t get into the details of why this works.)
So now we have periods and starting dates. With a little more Excel magic, we can calculate the key dates we need:
To make the output easier to read, we’ve split the formula into two columns.
In column D, we determine the year/period combo appropriate for each value we need to look up.
In column F, we use a VLOOKUP to get the value from the query returned by the NF function. The cells are named in the same way as the simple example and can be used in the exact same way.
If you’d like the Excel examples, please contact us.