917.848.7284Data for Finance and Accounting

Top

FINANCIAL REPORTING AND ACCOUNTING BLOG

I recently presented on SSRS at the NAV Focus Conference in St. Louis. As often happens at these events, I wasn’t exactly sure who was going to show up and what they would already know. (Especially at an event like this, which had a development track but with no specified levels.) In any case, it became clear to me that many folks in the audience could benefit from a series of posts aimed at a “SSRS 102” level. They’ve mastered “SSRS 101” material—they can create a basic report and know what data source, datasets and reports are—but now, they’re ready to move up. The Cardinal Rule of SSRS Performance Happiness To move from a SSRS 101 level of understanding to a SSRS 102 level, you have to understand what SSRS does well—and what it doesn’t. Like most software tools, SSRS can do many things. Some of those things it does really well. Other things,...

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

I’m working on my first paid project using Azure. Previously, I’d set up test databases in Azure but nothing focuses the mind like having to get something working for a real client. I am hardly an Azure expert, but most of my clients are interested in or using some sort of cloud application, of which Azure SQL database is one. So, part of my motivation for writing this post is to get my thoughts clear. Why Azure SQL Database Should Work for Me and My Clients In the SQL server universe, I spend most of my time writing queries and doing integration development on SQL Server. I’m not a production DBA. I only learned about the back end to help various clients survive SOX and other financially related orders. So things like backups and redundancy are areas where neither my team nor I have in depth experience. Most of our clients have...

As a consultant, I’ve spent many hours on the technical aspects of budgeting. I’ve designed models, developed spreadsheets, downloaded and uploaded data, and of course, written lots of financial statements. I’ve also budgeted for my own business as well as other companies where I’ve worked. So, I know something about budgets. But, recently, in my volunteer position as treasurer of my local Y, I’ve been thinking about how a nonprofit board should approve a budget. The challenge of any nonprofit is that board members mostly don’t know the ins and outs of the business. And running a community center is particularly difficult as you have at least five to six different businesses contained therein, with very different revenue and cost structures. In my day job, I never have to model a health club and nursery school in the same project—not to mention a teen musical production. The other challenge is that, unlike in my day...

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