I recently presented “NAV SQL from Friends to Foes” at the recent NAV User Group Summit.
Here’s a copy of the presentation:
Here’s the code I reference (in GitHuB).
My presentation is a whirlwind tour of the NAV database and SQL. But still, it’s a good starting point for folks who want to get comfortable with them both.… Read More
In this article, I’m going to delve into the topics of MAXDOP and cost threshold for parallelism.
I’m writing it for two reasons:
1. There was a recent thread on SQL Server settings on the NAVUG list. Some of the information was good. But much of it was expressed in terms of “Well, I heard…,” which makes me nervous. You should have a basic understanding of what various settings do before you start taking anyone’s advice.… Read More
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
If you work at the intersection of systems and accounting, as I do, the title of this post may put you on edge—because you know how the story goes. Your system is working fine. You may even have time to get to some of the “nice to haves” as the “must haves” are going pretty well. But then the auditors show up.… Read More
In May, I’m presenting at the NAVUG Focus conference on the topic of “BFFs with SSRS.” (Not a title I chose. Please.) In preparation, I’ve been doing more work on the database, and I thought it worth writing up some of my findings. In this post, I’m going to review three fields that are in the NAV table but aren’t (or aren’t obviously) in SQL Server: Option fields Flow filter fields (FlowFilter) Sum index fields (SumIndexFields). If you’re new to NAV, you should start with my Understanding the NAV Database post from last summer. Option Fields In my previous post, I wrote about how to find the table name from a NAV page. Now, let’s look at another example, using the Vendors page and underlying table. Here, I’ve filtered to show three vendors and the value for “Blocked” (which I’ve set): If we look at the table information, we can see that the underlying table is...
Tracking changes is a good idea in any ERP application, including Dynamics NAV. And Dynamics NAV’s “Change Audit” feature is a good place to start because it can comprehensively track changes to your tables. That said, it has some limitations in reporting that are important to know. In this post, I’m going to review some of the limits and also point out a few other key things to remember when tracking changes. Setting Up Change Log Setting up the change log is easy. First, we navigate to Change Log Setup. Click the check box “Change Log Activated”: (This doesn’t actually do anything. It just enables the next step.) From this page, we choose the Actions tab and click on Tables: We come to a list of our tables. I’ve filtered the list so we can work with the vendor table. (Vendor table is the table that probably everyone should track changes to: Each table has three options: Log Insertion—when we...
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...
I’m in the process of finishing up a data conversion from Lawson to Dynamics NAV 2016. The last piece to figure out are 1099 balances from checks issued before we converted. (We only converted open AP Items, so we don’t have the history necessary to calculate 1099s at year end.) I’ve looked around the web and found a variety of incorrect and partial answers. Many solutions have a lot of discussion about invoices and credits. But I think I’ve found a much simpler way of doing this. While converting 1099 balances is a fairly rare occurrence, you may find it useful for other scenarios as well. (For example, in many systems, I use “bank accounts” for clearing things that aren’t real payments.) But before we start, there are three things you need to remember about NAV and 1099s: 1. If you have paid invoices in the system for the year, it’s easy to update...
Dimensions are a wonderful feature of NAV. However, when doing General Ledger analysis, sometimes clients just want to dump everything into Excel and “play” with it—especially if they use Analysis Views. And to do this, clients want to see all dimensions, at the detail level, for each transaction. But here’s the challenge: You can only view or filter two dimensions directly on the General Ledger entries page. Fortunately, there’s a way around this restriction. How to See All Dimensions for NAV G/L Entries Let’s start by reviewing the problem: We want to see all transactions for a specified dimension, but when we look at General Ledger Entries, we can only see and filter by the two shortcut dimensions. We start with the basic General Ledger Entries page in Cronus, USA.: Out of the box, no dimensions are shown at all. If we choose Columns, we see the two global dimensions, in this case Department Code and Project Code: If...
Since most of my clients have switched to Dynamics NAV (many of them from Lawson), I’ve been busy writing a bunch of SSRS reports that facilitate specific finance and accounting functions my clients require. Here are the reports I’ve completed thus far: Using Totaling Accounts with SQL in Dynamics NAV AR Revaluation Report – Dynamics NAV RapidStart – A Query to Check Your Errors. I have more reports in the works. If you’d like my complete package of free SSRS reports for Dynamics NAV when it’s ready, let me know. ...