917.848.7284Data for Finance and Accounting

Top

FINANCIAL REPORTING AND ACCOUNTING BLOG

Recently, I read a blog post by Andy Leonard on his SQLblog.com titled Long Poles and Critics. In this post, Andy discusses the importance of not being too quick to criticize other’s work when he’s called in to complete or extend software reporting projects. Almost by definition, consulting is an arrogant profession. SSRS consultants are paid considerable sums to solve other people’s problems. So being quick to criticize is endemic. Still, I agree with Andy. I’m careful to give those who’ve come before me the benefit of the doubt. But it’s not because I don’t know the full story, as Andy writes. It’s because I know that, while I’m proud of most of the work I’ve done, anyone looking at some of my projects would scratch their heads. If you were to ask me, “What were you thinking?” I could tell you exactly. Here are some examples: 1. The Simple, Quick Solution That Took Over...

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

Here’s another installment in my series of posts on SSRS consultant tips for super users and advanced beginners. (See my post Put Parameters in Your Query, Not Your Filter for the first installment.) Part of what I enjoy most about working as a SSRS consultant, and performing SQL Server consulting, is the variety. Some months, I spend a lot of time writing T-SQL. Others, I’m deep into Excel pivot tables. And then others I’m writing SSRS reports. Truth be told, if I could spend all my time doing solution architecture and T-SQL coding, I’d be happy. But that’s not my current life. Every once in a while, when I go back to SSRS, I find I’ve forgotten a few helpful but not crucial things. So, I empathize with folks who don’t use SSRS full time and need a moment to get their heads back into it. In that spirit, I’m going to use this...

I’ve been doing various forms of systems consulting since the mid-90s. From my very first job, it was clear that the amount of credit you receive is rarely related to the amount of work involved. So, in that spirit, I’m going to help you become a reporting hero by showing you how to set default values in your SSRS reports. As an SSRS consultant, I do the majority of my work with finance and accounting data. That means my reports generally run by fiscal year and period. Therefore, in this example, I’ll show you how to have fiscal year and period default on your report parameters. So, even if you have, say, 15 possible fiscal years, the current year comes up. If you want to follow along at home, I’m using the AdventureWorks2012 database with the addition of three custom tables: FiscalYears, FiscalPeriods and DateToFiscalYearPeriod. (When conducting actual client work, we have...

As a SSRS consultant, we’re often asked how to speed up reports. While there are many ways to do it, I would make this the top one: Put parameters in the query, not in the dataset filter, if at all possible. Filtering is not your friend. Repeat after me: Put your parameters in your query, not in your filter. To show you why, let’s look at two reports: one called Parameter in Filter, the other Parameter in Query. Both reports are identical except for one thing—in one report I use a filter on the dataset and in the other I use a filter in the query itself. Note: I’m using a dataset here based on Adam Machanic’s bigproduct and bigTransactionHistory tables. The view I’m querying has about 700,000 rows—so it’s not a huge set. When we run either report, we get the same output: However, there is a one striking difference in the two 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...

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

I was at the NAVUG user group in NYC last week. There, I learned that NAV2017 now allows users to cancel posted sales invoices and sales credit memos. While I’m sure some users will welcome these changes (and I’ve yet to see exactly how they’ll work), my first impression is that this isn’t such a great idea. Part of my reasoning is that, honestly, I believe that people who “pay for their mistakes” are more likely to stop making them. But I also hesitate because I don’t like reversing transactions in NAV more generally. Using Reverse Transaction in Dynamics NAV NAV has at least three “reverse transaction” options: General Ledger Entries, Customer Ledger Entries, and Vendor Ledger Entries. When a customer bounces a check, you could usually reverse that in the customer ledger. To better understand my hesitation about reversing transactions, let’s look at a payment reversal as an example. Let’s start with payment 2596 (below) in...