917.848.7284 Data for Finance and Accounting

Financial Reporting and Accounting Blog

Understanding the NAV Database

Adam Jacobson June 9,2016

I recently posted a seven-part series on SQL for super users. (You’ll find the introduction here.)

In this series, I chose to use generic Northwind database (with some enhancements) for my examples. (The posts came out of presentation I made to a mixed group of Dynamics users.)

But, as you know, each kind of Dynamics software (i.e. GP, NAV, AX, CRM) has its idiosyncrasies. Therefore, I’m in the process of adapting the generic Northwind examples to specific Dynamics types. And I’m starting with NAV, as I happen to spend quite a lot of time on it. (If you’re interested in getting a copy of the PDF when it’s ready, let me know.)

As I prep the NAV series, I realize a basic intro to NAV would be useful before launching into it.

Read More

Tags: Dynamics GP

Building an SSRS Trial Balance for Dynamics GP

Adam Jacobson December 16,2015

In this post, I'll show you how to create a view in SSRS that allows Dynamics GP users to create a trial balance for any time period (or multiple periods) and get the ending balance for that period(s).

I was motivated to write this post after seeing a question on the GPUG forum about it. The question was referred to Victoria Yudin, a GP SQL guru, and she has a nice stored procedure to get a 12-month trial balance for the same year.

Also, I’ve been developing some generic design patterns for financial reporting in SSRS and as part of this project we've been asked to create trial balances for the last 12-month period—so the topic has been on my mind.

Read More

Tags: SSRS Dynamics GP

Using Dynamic SQL to Solve the Problem of Too Few SQL Server Instances

Adam Jacobson November 13,2015

As consultants, we pride ourselves on both knowing the best way to set up systems AND finding workarounds when client infrastructure is “suboptimal” and unlikely to change. We recently performed such a workaround for a client that had too few SQL server instances—and I'm going to share the experience with you here. (Hopefully, your system is set up more optimally. But even if it is, you can still learn from our example.)

Read More

Tags: Dynamics GP

Finding the Relevant View in Dynamics GP

Adam Jacobson October 28,2015

During my recent presentation on SSRS for Dynamics GP at the recent GPUG Summit conference, an attendee asked how to begin. Could he just start with the Report Wizard in Report Builder, he asked?

I replied that would work fine if he had a test database (and preferably a test system) so he wouldn’t blow up the production work. Also, rather than try to join tables directly himself, he should work with one of the provided views—this would give him and his team a major head start with names that make sense and joins that are already in place.

Read More

Tags: Dynamics GP

Data Access for Reporting in Dynamics GP

Adam Jacobson October 26,2015

During a session at the recent GPUG Summit, an attendee asked how to find out which users had access to which database objects. (This was in the context of a conversation about setting up SSRS and using the various database roles that GP provides for reporting.)

I responded that you can find this out using the system views which are part of SQL Server. To demonstrate the point, I’ve created two queries below. You can combine them, but for illustrative purposes I've divided the process into two steps.

Read More

Tags: Dynamics GP