917.848.7284Data for Finance and Accounting

Top

Jet Reports

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: How to enter a range of accounts and see the detail How to add a total to the range How to create one report that can show either detail or summary, depending on your parameters. These posts assume you have some basic familiarity with Jet Reports and the special functions it provides...

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: Ease of Learning for End Users As I’ve said before, the biggest ERP...

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: =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...

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) Excel Prerequisites 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: VLOOKUP SUBTOTAL DATE Named Cells NL NF GL Using Accounting Periods Instead of...