A client recently asked me: “When I run a report, how can I know which server/company I’m using?”
It’s a good question — and one that others may have.
Below, I’ll walk you through the answer.
And because pretty colors impress most folks, I’ve even made it match the colors that show up in NAV.
Read more…One weakness of NAV Account Schedules is its inability to specify a range of accounts (or a totaling account) and then give details on the fly.
Unfortunately, when my client went to their first Jet consultant with this issue (thinking that Jet Reports could do better), they got the same answer. If you want to see detail, the consultant said, you have to enter EVERY account you want to see. (Which is something of a maintenance nightmare.) Further, it just isn’t true. In today’s post, I’ll show you how to do it.
I’m going to divide the process into three parts:
These posts assume you have some basic familiarity with Jet Reports and the special functions it provides for Excel. You might also want to have some familiarity with GL, NL, NF and hide functions in Jet.
For my examples, I’ll use the Cronus, USA company..
Let’s start with a simple example in Jet, using the GL function to return the amounts we’d like.
As explained in an earlier post, I’m using accounting periods for my report and not dates.
In this example, I’ve entered the same accounts in three ways:
I want to point out two key things:
First, the amount columns (G,H,L and M) all use the GL function to return the information we want. We can see the formula, and here’s an example of it:
=GL(“Balance”,$B7,CYPSD,CYED)*$E7
Second, we can’t just rely on the GL formula. We need to remember that, unlike many financial report writers, Jet also creates regular database reports.
So, let’s check out the new report design. In it, we use two basic functions to retrieve non-financial data: NL and NF.
We start by listing all our revenue accounts 41000..41999. Then we place that range in column B.
Note that column B is set to hide when the report runs:
In column C, we use our NL function. As you know, NL function can do many things. In this case, we ask it to create a row in our Excel worksheet for every record returned from the specified table.
Note that the formula appears somewhat cryptic in the spreadsheet. But if we look at the formula itself, we can understand what’s going on:
=NL(“Rows”,”G/L Account”,{“No.”,”Name”},”Account Type”,”Posting”,”No.”,$B$8)
But the easiest way to see the formula is by clicking NL in the ribbon:
Here, we see that we’re asking for rows to be returned from the table “G/L Account” where the chart of accounts is stored. We ask for Fields “No.” and “Name” to be brought in. And we’re filtering by “Posting” accounts. Most importantly, we reference the range of accounts that we setup in our spreadsheet, which Jet helpfully show us to be 41000..49999.
So, the NL function looks good. But we need to do one more thing. We need to put numbers and names into our report. And we’ve done that with the NF function, which we see in columns D and E.
In column D, the formula refers back to the cell with the NF function and asks for “No.,” (i.e. the account number):
=NF($C8,”No.”)
Similarly, column E asks for the name:
=NF($C8,”Name”)
We can click on NF to see the formula structure clearly:
“Key” is where we’re getting the data and “Field” tells us what we want to see in this cell.
We then update our GL formulas to reference this cell:
=GL(“Balance”,$D8,CYPSD,CYED)*$F8
Now, let’s run the report and see what happens:
And we have a list of all the accounts we want with the appropriate numbers.
Yay!
When Jet ran, it inserted one row per account.
Before we started we had totaling formulas under our data:
For example, take a look at the current MTD column. Ideally, we’d like to see this automatically add up all our inserted rows. (In practice, I use SUBTOTAL instead of SUM in most of my reports, but I don’t want to confuse things here.)
Let’s see what happens:
Unfortunately, the formula didn’t change. It’s still only includes row 8.. How can we work around this?
Let’s try inserting a row to “anchor” our formula:
The formula now spans H8:H9.
Will this work? Let’s try it:
And it does!
We can, if we want, place a hide command in that row so we won’t see it when the report runs:
And when we rerun it, it still works:
The hidden row doesn’t appear, but the formula still works.
You can repeat the above process for as many ranges as you’d like.
So, we’ve seen how we can get financial detail for a range of accounts. Now, we want to go a step further and create a summary or detail version of our report.
We start by having a cell where we can enter “S” (for summary) or “D” (for detail).
So, we create a named cell on our parameter page where the user can select “Summary” or “Detail.”
Now, when we return to our report page, we need to make two changes:
First, we add a formula for each row so that if we want summary, the row will be hidden. We do this by displaying the word “Hide.” (This works because at the top of the column we’ve entered “Hide.”)
=IF(SummaryDetail=”Summary”,”Hide”,””)
Second, on the actual summary row, we note that we only want to see a name if we’re summarizing:
=IF(SummaryDetail =”Summary”,”Revenue”,””)
So, if we run this as detail, we see what we received before:
And if we choose summary:
Perfect!
Feel free to comment below if you have any questions.
For the past few months, I’ve been spending a lot of time creating reports with both Jet Reports and SSRS, mostly with Dynamics NAV and a little with Dynamics GP. If you’re a NAV customer, you have some version of Jet included with your purchase. Like all folks with SQL server databases, Dynamics folks use SQL Server and therefore have access to SSRS.
So which is better? Or rather, what works best in any given situation?
In considering this question, let’s focus neither on regular reporting (sales order, inventory, etc.) nor financial reporting. Jet is clearly better suited for financial reports than SSRS. And if Jet runs out of steam, you’re often better off finding another financially oriented tool rather than going to SSRS.
Instead, let’s focus on a few other factors that might nudge your decision one way or the other:
As I’ve said before, the biggest ERP salesperson lie is “your users can build their own reports.”
Yet getting data without having to call IT remains the Holy Grail for end users. And on that topic I’ll say this:
Based on the end users I know, and the folks we’ve trained, Jet is definitely easier for end users to learn. It’s really just a few additional Excel formulas and some key terms. So, your Excel jockeys will dive right in. (Now let’s just hope that their Excel knowledge doesn’t become a gateway drug.)
What’s more, Jet Reports lends itself really well to learning in chunks. You can get started quickly without having to know everything there is to know.
SSRS, isn’t nearly as easy. SSRS is designed by folks who like SQL and Visual Studio—intuitive it’s not. We’ve had some luck with SSRS when the end users are dedicated reporting folks within their departments, but Jet wins the easy-to-learn contest.
That said, even with Jet, a little IT support can go a long way. For example, we built a lot of views to combine multiple data for a Dynamics GP client. When the users realized they could use the views in Jet (because they had the right licenses), they were very, very happy.
Here, Jet Reports win again. Not surprisingly, as Jet is an Excel add-in. You can build reports in Jet and keep all your regular formatting. In contrast, while SSRS dumps things to Excel pretty well, your formulas won’t come over.
Hands down, SSRS is faster than Jet. It’s the nature of the beast. Jet, because it’s Excel based, issues commands to the database and then does its work on the PC.
Meanwhile, SSRS does its work on the server. When properly run, it’s faster. And this is especially true when reports get complex. SSRS allows you to use stored procedures to save time. But with Jet you’re relying on basic tables and views.
To be clear, Jet is plenty fast for most uses. It’s just that when you have lots and lots of users and lots of reports, SSRS can be faster.
Jet is an Excel tool. And it does a lot of work in Excel itself. This means it runs best when you have a solid PC working on the data and when the server is on premise. When your apps are hosted, you need to consider running Excel in the cloud as well. And in my experience, this doesn’t make end users happy. (Note: the newest version of Jet does work with Office 365. However, I’m still not sure how well everything will work together if things are hosted in different places.)
SSRS, as a server-only application, is great for the cloud. All you need on your desktop is a browser. (And with SQL server 2016, you can even use multiple browsers without much aggravation.)
Excel is both the strength and the weakness of Jet. With SSRS, if someone asks, “It’s 10:00 p.m. Do you know where your reports are?” You absolutely know. You can easily control who has access to what and where.
With Jet, you’re chasing spreadsheets. Yes, you can make this work with folder controls, proper assignment of licenses and good procedures. But years in this business have taught me that Excel spreadsheets tend to get out of control. That’s not Jet’s fault. That’s just how people use Excel.
I’m not going to create a scorecard. Any scoring will depend too much on context. But I will make a couple recommendations:
I’d welcome anyone else’s feedback and experience.
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.
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:
=GL(“Balance”,$B9,CYPSD,CYED)*$E9
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, 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.
If you’d like to discuss jet reports or receive a copy of the sample spreadsheets used for these examples, please let me know.
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:
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:
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 ).
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.
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:
=IF(MOD(ROW(D4)-3,12)=0,12,MOD(ROW(D4)-3,12))
=TRUNC((ROW(D4)-4)/12,0)+StartFY
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.
