917.848.7284 Data for Finance and Accounting

Financial Reporting and Accounting Blog

What to Criticize (and Not Criticize) as an SSRS Consultant

Adam Jacobson March 9,2017

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.

Read More

Tags: SSRS

Understanding the Dynamics NAV Change Log

Adam Jacobson March 7,2017

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.

Read More

Tags: Dynamics NAV

SSRS Consultant Tip—Round Your Numbers, Don’t Just Format

Adam Jacobson March 2,2017

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.

Read More

Tags: SSRS

SSRS Consultant Tip: Defaulting Your Parameters

Adam Jacobson February 28,2017

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.

Read More

Tags: SSRS

SSRS Tip: Put Parameters in Your Query, Not Your Filter

Adam Jacobson February 14,2017

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.

Read More

Tags: SSRS

Creating a Summary/Detail Report for Jet Reports and Dynamics NAV

Adam Jacobson February 1,2017

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.

Read More

Tags: Dynamics NAV

Converting 1099 Balances in Dynamics NAV

Adam Jacobson January 19,2017

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.

Read More

Tags: Dynamics NAV

See All Dimensions for NAV General Ledger Entries

Adam Jacobson January 17,2017

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.

Read More

Tags: Dynamics NAV

Free SSRS Reports for Dynamics NAV

Adam Jacobson December 29,2016

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:

Read More

Tags: Dynamics NAV

How to Deal With NSF Checks in NAV (Without Using Reverse Transaction)

Adam Jacobson December 22,2016

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.

Read More

Tags: Dynamics NAV