Understanding the NAV Database

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

Read more…

Building an SSRS Trial Balance for Dynamics GP

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.

Why Views Instead of Stored Procedures?

While Victoria’s solution is good, I want to add to it by allowing users to return trial balances for any period, not just the last 12 months. Also, I wanted to use a view instead of a stored procedure for a couple reasons:

  1. From a users perspective, views work just like tables. So, for example, a user can use views in the SSRS Report Builder Report Wizard where they can’t use a stored procedure.
  2. Stored procedures tend to multiply and/or require lots of maintenance. If you add output fields to a stored procedure, you have to either create a new procedure or fix all your existing reports. But, if you add a few new fields to a view, your existing reports don’t change. (But this is really a topic for another blog post.)

So let’s start where Victoria started—with the balance table. I’m going to use the Account Summary view as it contains both open and historical data. This query takes the period balances that are stored in GP and totals them for each period. So, we get the ending balance for each period:

And here’s the output:

It’s not quite what we’re looking for. It’s great that each period “rolls forward” to include previous balances. But GP only stores data in the table when there was some activity in the period.

List all Account/Fiscal Period Combinations

A Dynamics GP Consultant can fix this by starting with a list of all account/fiscal period combinations that exist, instead of starting with the amount data. Here’s the query:

And here’s the sample output:

This is much better.

By the way, in financial reporting we often want to see all accounts, whether or not they had activity. Or if they had budgeted activity but not actual activity. Or actual activity but not budget. The solution is to start with a list of what we want to see, rather than start with the amount information.

Now that I have a list of possible accounts and periods, I use a left outer join to check for actual activity:

When I run the above query, I get the output I want—with a value for every period:

Next, I’m going to run the above query into a view. Note that when doing this, I remove the “order by” clause as well as the check for fiscal year, which I used for testing.

With the above view, I can create an SSRS report which groups fiscal year and periods as columns and account descriptions as rows:

Filtering Time Periods

Now, if I want to choose only a few periods, I can do so by changing the parameters. But remember, I must filter the time periods in SSRS, not in the SQL query. Why? Because to calculate balances correctly, I need to select periods 1-6 in order to get period 7.

For example, let’s say I want to select the last 12 months. I tell the query to select two year’s worth of data:

I’ve purposely broken the filter process into multiple steps. I like to segment the process because multi-part formulas are harder to debug when you return to your reports months (or years) later.

In each case, I’m combining fiscal year and period into a six-digit number. For example:

  • 2015 period 10 becomes 201510
  • 2015 period 6 becomes 201506.

I do this three times: (1) for the fiscal year and period from the data returned from the query, (2) for the fiscal year and period from the query result and, (3) for the starting fiscal year and period, which requires a little twist in the logic.

All these fields can be created from Dataset->Properties->Fields:

Which I define as:

Now, I’m going to establish the ending period of the report, which I get directly from my parameters:

The logic for the starting period is a little more complex. Basically, I have two situations: period 12 and all other periods. For anything but 12, the logic is simple:

  • Subtract one from the year
  • Add one to the period.

So, 2015 period 10 becomes 2014 11.

For period 12, I want to start at period 1 for the same year.

So, the year remains the same. The period becomes 1.

Here’s how the logic looks in SSRS:

Now that I have the three date fields, I can easily create the filter.

Choose Dataset->Properties->Filters:

I state that “fiscal year and period” must be between “start fiscal year and period” and “end fiscal year and period.” I also exclude period 0, which represents the balance brought forward.

Here’s the output:

A Word of Warning

I acknowledge that using filters in SSRS degrades performance. I’m only using filters here because I’m crossing years. If I simply wanted all months for the current year, I wouldn’t need a filter because I could incorporate the record selection into the query.

If you’re looking for a Dynamics GP Consultant or would like a copy of the sample report, let us know.


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

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

A Workaround for Dynamic SQL and BatchMaster

Our client was running both Dynamics GP and BatchMaster. Both systems share the same database instance, with different database names. So, we started with six different databases. (This wasn’t the exact structure, but it’s close.)







(Dynamics GP folks know that Dynamics Shared database makes things even complex. But I don’t want to get into that here or we’ll lose sight of our main topic.)

To make things more interesting, we set up our own databases for development. In general, we’re strong believers in having software packages control their own databases. It makes for easier upgrades and eliminates “Your peanut butter is in my chocolate!” kind of arguments.

So, we have three more databases:




Now, we have a problem because all nine databases are on a single instance. And much of our Red Three (RED3) code needs to reference both GP and BatchMaster.

So how do we do it? Well, say we start with a stored procedure in Dev. We write an interface between BM and GP, and we want to reference the item master in both systems for our stored procedure. So, we have to fully qualify the object names. A simple select statement would look like this:


BMDEV.dbo.ITEM bmi


ON bmi.ITEM = gpi.ITEM

And that’s fine. But if we want to move this code to production, we have to search and replace as we move, so it looks like this:




ON bmi.ITEM = gpi.ITEM

This is bad for several reasons:

  • It leaves room for human error. Every code promotion requires making a change.
  • If you want to compare your code in production and development, whatever tool you use (we use Red Gate SQL Compare) is going to find differences that aren’t really differences.
  • If database names change, you have to go back and change your code.

A More Ideal Solution: Separate Database Instances

A more ideal solution (and not just for coding reasons) is to have separate database instances for Dev, Test, and Prod. Then our database names could stay consistent:

Production Server


Test Server


Development Server

But unfortunately, that wasn’t an option.

An Alternate Solution: Build Simple Views in Our Red3 Database

We decided we would never, ever reference the tables outside of GP and BM directly with our custom code. So instead, we decided to build simple views in our Red3 database for each table we need to access.

To demonstrate, let’s use the above example where I’m accessing two different item masters. I would create two simple views:

First, a simple view like this:



CREATE VIEW dbo.vBM_ITEMMASTER as select * from BMTEST.dbo.Item

Second, a simple view so my red3 code doesn’t have to access any databases:




ON bmi.ITEM = gpi.ITEM

Now that’s not bad. I do have to setup underlying simple views, but I can move the code between environments with no changes.

Automating the Procedure to Reduce Odds of User Error

But I want to go one better. As you can predict, when your database grows, you’ll need more and more of these views. And keeping database names straight is prone to user error. So, I automated a procedure to automatically create a view for a list of tables. (Theoretically, I could create a view for EVERY table in the packaged systems. But that creates thousands of objects I’d never use and make SSMS hard to navigate.)

As a first step, I create a simple table that contains the list of tables I want to turn into views:

And then I populate it. (I will also save all these inserts as a stored procedure so I can easily update the procedure and the table when I need to create more views.)

(I repeat this for about 50 tables.)

Now, comes Dynamic SQL. Basically, I read through the table, create a view name using a prefix, and then create the tables:

Once this runs, I have the views I need without a lot of cutting and pasting. (Note: This procedure only updates views that don’t exist already. I have other versions that drop and create the view. And I also use synonyms when stored procedures are involved.)

Hopefully, most of you have multiple environments and don’t need to resort to this kind of workaround. However, the idea of using Dynamic SQL to build other objects can be useful in other environments as well, for example:

  1. Dynamics GP: Even with multiple environments, you’ll often have multiple databases. We use this technique to build code to “union” all databases in a single view and update that view each time we add a new one.
  2. Infor SunSystems: While GP creates a database for each company, SunSystems creates a different set of tables within a database each time a company is added. We use this technique to join all tables together in one view (and then one data mart).

If you’d like to play around with these solutions, you can download the sample code files.

Do you have these kinds of issues? How do you deal with them?

* * *

For more of these tips, sign up for our newsletter on the top right of this page.


Finding the Relevant View in Dynamics GP

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.

The challenge: While there are many places to find out about tables, including the application itself and the many blogs on the topic (such as those by Victoria Yudin and Mark Polino), I haven’t been able to find information about the views.

But as I was writing an earlier blog post about database queries, I realized that these same queries might help folks get a head start on the correct views.

To illustrate the point, I walk through the process below. Because I don’t want to scare end users (who’re the primary audience for this post), I demonstrate using sample data and leave the actual queries for the end of this post. (You’ll also find a link to a spreadsheet with both the queries and complete sets of data at the end of the post.)

Start with a Role and Find the Views that Role can Access

Dynamics GP sets up many database roles to control data access for reporting. The role names are fairly obvious. Here’s a list of “rpt” roles delivered standard with the TWO database:

(See query 1, below.)

Looking through the list, you can probably guess which role has access to the data you’d like to see.

Once you have the role, you can find what views that role has access to. For example, I checked which views “accounting manager” has access to and received this list:

(See query 2, below.)

Again, the names are pretty straightforward.

One more query. After working with the system, you’ve started to learn a few table names. Or perhaps you found a name through an online tool or a website that covers GP. But still, you don’t want to create all your own table joins. So, I created another extract where you can start with a table name and see which views display its data.

Here’s an example:

(See query 3, below.)

One point on this last query: I didn’t look at every single view in the database, just the views report writers have access to. Also, views are built from other views, not just directly from tables—but I haven’t looped recursively to find every table that supplies information to any given view.

Queries Used in this Post

Download our Excel spreadsheet with the queries and complete sets of data from TWO.

Sign up for our newsletter by entering your email in the box on the top right of the page.


Data Access for Reporting in Dynamics GP

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.

Step 1: Find out which roles have access to which objects

The purpose of the first query is to find all objects that the given role has access to. In this example, I’m selecting by role using the “rpt_accounting manager” role. You might also select by permission type (e.g. UPDATE, INSERT OR DELETE permissions)—and thereby tell auditors that only one role has rights to perform those actions.

Here’s a sample of the output:

Step 2: Show all users assigned to a role

The second query takes a role and finds all the users assigned to it. Please note: Theoretically, roles can be assigned to other roles and those roles assigned to yet other roles. So, this one-level query won’t work in all circumstances. But for most folks, this will work just fine.

The Microsoft website has a lot of documentation on system views. Search for any of the views used in my queries above with T-SQL, and you’ll find what you need. (Note: For a “sys.” search, Google “sys objects,” otherwise you end up on the wrong page).

If you’d like to play around with this sample data and queries, you can download our speadsheet.

Sign up for our newsletter by entering your email in the box on the top right of the page.


Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284
Email: inquiries@redthree.com

Request a Consult